Buying 15$ to build a mysql query

Hello all

I have data in a excel file : address / phone / cell etc... I need to import them in a new database, in a text field , that contains parameters, that looks like follows

{"userExtendedFieldsaddress":"40 rue du pont","userExtendedFieldscity":"issoire","userExtendedFieldsstateOrProvince":"","userExtendedFieldszipCode":"63500","userExtendedFieldscountry":"France","userExtendedFieldstelephone":"0123456789","userExtendedFieldsmobile":"0123456789","userExtendedFieldsfacebook":"facebook","userExtendedFieldstwitter":"twitter","userExtendedFieldsgoogle":"gplus","userExtendedFieldslinkedin":"","userExtendedFieldsyoutube":"","userExtendedFieldsvimeo":"","userExtendedFieldsblip":"","userExtendedFieldsflickr":"","userExtendedFieldspicasa":""}

a name field is in commont to the excel and the new databse and can be use as index. The first one to post the correct query here gets the money !

 

Pm me with ur excel.file then i will sort this for u

 

Hello

excel files contains personnal data, cannot be sent. But just use this as the column name in the same order as the field i ve posted above

xl_userExtendedFieldscity = userExtendedFieldscity

 

Just export your excel as a CSV then import it via phpMyAdmin.

 

the parameters you see above are values in a field,not fields in a table ...

 

I've tried it, please check with the below steps. Sorry if I am wrong.

Steps:

1. First you need to create a table using the query,

create table tb1 (address varchar (20), city varchar (10), state varchar (10), zipcode int(10), country varchar (10), telephone varchar (10), mobile varchar (10),
facebook varchar (10), twitter varchar (10), Google varchar (10), linkedin varchar (10), youtube varchar (10), vimeo varchar (10), sblip varchar (10), flickr varchar (10), picassa varchar (10));
2. Then you need to create a csv file that looks like,

3. After creating it, you need to write a query like,
load data local infile 'd:\sample1.csv' into table tb1 FIELDS TERMINATED BY ',' enclosed by '"' lines terminated by '\n';
The data will be imported to your database with warnings.
Please let me know if you need further assistance.
Thanks.

 

Hello

the table already exists, all the values are stored as text in one field !

O

 

Your table contains only one field?

 

I am not explaining this right , here you can see how the table is built

CREATE TABLE IF NOT EXISTS `mi7df_k2_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userID` int(11) NOT NULL,
`userName` varchar(255) DEFAULT NULL,
`gender` enum('m','f') NOT NULL DEFAULT 'm',
`description` text NOT NULL,
`image` varchar(255) DEFAULT NULL,
`url` varchar(255) DEFAULT NULL,
`group` int(11) NOT NULL DEFAULT '0',
`plugins` text NOT NULL,
`ip` varchar(15) NOT NULL,
`hostname` varchar(255) NOT NULL,
`notes` text NOT NULL,
PRIMARY KEY (`id`),
KEY `userID` (`userID`),
KEY `group` (`group`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=265 ;
 
INSERT INTO `mi7df_k2_users` (`id`, `userID`, `userName`, `gender`, `description`, `image`, `url`, `group`, `plugins`, `ip`, `hostname`, `notes`) VALUES
 
(10, 50, 'ttt', 'm', '', NULL, '', 1, '{"userExtendedFieldsaddress":"40 rue du pont","userExtendedFieldscity":"issoire","userExtendedFieldsstateOrProvince":"","userExtendedFieldszipCode":"63500","userExtendedFieldscountry":"France","userExtendedFieldstelephone":"0123456789","userExtendedFieldsmobile":"0123456789","userExtendedFieldsfacebook":"facebook","userExtendedFieldstwitter":"twitter","userExtendedFieldsgoogle":"gplus","userExtendedFieldslinkedin":"","userExtendedFieldsyoutube":"","userExtendedFieldsvimeo":"","userExtendedFieldsblip":"","userExtendedFieldsflickr":"","userExtendedFieldspicasa":""}', '', '', ''),
;

Code (markup):

 

Please create a csv file like, all double quotes (") and comma (,) should be escaped by \, so that MySql recognizes it has a same field not has a different field.

After modifying the notepad content, you can execute the below query.


load data local infile 'd:\sample.csv' into table mi7df_k2_users FIELDS TERMINATED BY ',' enclosed by '"' lines terminated by '\n';


Notepad contents:


"20", "30", mmm,f,,,,,{\"userExtendedFieldsaddress\":\"40 rue du pont\"\,\"userExtendedFieldscity\":\"paris\"\,\"userExtendedFieldsstateOrProvince\":\"\"\,\"userExtendedFieldszipCode\":\"63500\"\,\"userExtendedFieldscountry\":\"France\"\,\"userExtendedFieldstelephone\":\"0123456789"\,\"userExtendedFieldsmobile\":\"0123456789\"\,\"userExtendedFieldsfacebook\":\"facebook\"\,\"userExtendedFieldstwitter\":\"twitter"\,\"userExtendedFieldsgoogle\":\"gplus\"\,\"userExtendedFieldslinkedin\":\"\"\,\"userExtendedFieldsyoutube\":\"\"\,\"userExtendedFieldsvimeo\":\"\"\,\"userExtendedFieldsblip\":\"\"\,\"userExtendedFieldsflickr\":\"\"\,\"userExtendedFieldspicasa\":\"\"},\n

"10", "40", mmm,m,,,,,{\"userExtendedFieldsaddress\":\"40 rue du pont\"\,\"userExtendedFieldscity\":\"xxxx\"\,\"userExtendedFieldsstateOrProvince\":\"\"\,\"userExtendedFieldszipCode\":\"63500\"\,\"userExtendedFieldscountry\":\"France\"\,\"userExtendedFieldstelephone\":\"0123456789"\,\"userExtendedFieldsmobile\":\"0123456789\"\,\"userExtendedFieldsfacebook\":\"facebook\"\,\"userExtendedFieldstwitter\":\"twitter"\,\"userExtendedFieldsgoogle\":\"gplus\"\,\"userExtendedFieldslinkedin\":\"\"\,\"userExtendedFieldsyoutube\":\"\"\,\"userExtendedFieldsvimeo\":\"\"\,\"userExtendedFieldsblip\":\"\"\,\"userExtendedFieldsflickr\":\"\"\,\"userExtendedFieldspicasa\":\"\"},\n

Please let me know if it works. It is working fine for me.

 

Hello

Sorry, someone has found it s way with php using arrays. I ll publish the solution here as soon as it is completly sorted out

tks for yor try !

 

Okay no problem. Will be waiting for it.

 

You could just use json_decode to get the values, you'd get it either as an array or an object. That will NOT make searching for a particular field very easy to do though

 

The job is easy in PHP:
1)

$info = json_decode({"userExtendedFieldsaddress":"40 rue du pont","userExtendedFieldscity":"issoire","userExtendedFieldsstateOrProvince":"","userExtendedFieldszipCode":"63500","userExtendedFieldscountry":"France","userExtendedFieldstelephone":"0123456789","userExtendedFieldsmobile":"0123456789","userExtendedFieldsfacebook":"facebook","userExtendedFieldstwitter":"twitter","userExtendedFieldsgoogle":"gplus","userExtendedFieldslinkedin":"","userExtendedFieldsyoutube":"","userExtendedFieldsvimeo":"","userExtendedFieldsblip":"","userExtendedFieldsflickr":"","userExtendedFieldspicasa":""})

PHP:

2)

mysql_query("INSERT INTO `mi7df_k2_users` (`id`, `userID`, `userName`, `gender`, `description`, `image`, `url`, `group`, `plugins`, `ip`, `hostname`, `notes`) VALUES ((10, 50, 'ttt', 'm', '', NULL, '', 1, '".$info['userExtendedFieldscity']."' ,'','','')")

PHP:

Just do it like that and make sure to fill the data correctly. That query will NOT work as it is just an example on how you should do it.