Inserting csv

Newbie question, please.

I have a csv file of 950 records, 20 fields.

I used this converter
http://csv2sql.evandavey.com/

and copied/pasted insert code into SQL in phpMyAdmin

and got this error


SQL query:

INSERT INTO membership( `members_ID` , `updated` , `notes` ,
`preferred_mail_street` , `preferred_mail_csz` , `first_name` ,
`last_name` , `street` , `city` , `state` , `zip` , `location_code`
, `property` , `camp_street` , `camp_city` , `camp_zip` ,
`member_year` , `director` , `email` , `camp_phone` , `20` , `21`
, `22` , `23` )
VALUES (

'', '', '', '', '', 'xxx', 'xxxx', '102 Summer St',
'Dover-Foxcroft', 'xx', '04426', 'B', 'M15_L1_S20', 'Mill Brook',
'Bowerbank', '', '', '', '', '', '', '', '', ''
);

MySQL said:

#1054 - Unknown column '20' in 'field list'

Actually the first time, it was column 21, so to trouble shoot, I removed that column from csv file and field name in phpMyAdmin.


camp_phone should be the last field but

`20` , `21` , `22` , `23` )

is there too...

Any assistance most appreciated!

I checked the file and didn't see any odd entries after the camp_phone field.





--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Inserting csv

If you could show us the table structure (SHOW CREATE TABLE
membership), we could easily correctly the query.. you seems to have
some data among your field names...

If the question is about the CSV converter, you will have to ask them.

- michael dykman

On Thu, Oct 15, 2009 at 11:26 AM, Patrice Olivier-Wilson
wrote:
> Newbie question, please.
>
> I have a csv file of 950 records, 20 fields.
>
> I used this converter
> http://csv2sql.evandavey.com/
>
> and copied/pasted insert code into SQL in phpMyAdmin
>
> and got this error
>
>
> SQL query:
>
> INSERT INTO membership( =A0`members_ID` , =A0`updated` , =A0`notes` ,
> =A0`preferred_mail_street` , =A0`preferred_mail_csz` , =A0`first_name` ,
> =A0`last_name` , =A0`street` , =A0`city` , =A0`state` , =A0`zip` , =A0`lo=
cation_code` ,
> =A0`property` , =A0`camp_street` , =A0`camp_city` , =A0`camp_zip` , `memb=
er_year` ,
> =A0`director` , =A0`email` , =A0`camp_phone` , =A0`20` , =A0`21` , =A0`22=
` , =A0`23` )
> VALUES (
>
> '', =A0'', =A0'', =A0'', =A0'', =A0'xxx', =A0'xxxx', =A0'102 Summer St',
> =A0'Dover-Foxcroft', =A0'xx', =A0'04426', =A0'B', =A0'M15_L1_S20', =A0'Mi=
ll Brook',
> =A0'Bowerbank', =A0'', =A0'', =A0'', =A0'', =A0'', =A0'', =A0'', =A0'', =
=A0''
> );
>
> MySQL said:
>
> #1054 - Unknown column '20' in 'field list'
>
> Actually the first time, it was column 21, so to trouble shoot, I removed
> that column from csv file and field name in phpMyAdmin.
>
>
> camp_phone should be the last field but
> `20` , =A0`21` , =A0`22` , =A0`23` )
> is there too...
> Any assistance most appreciated!
>
> I checked the file and didn't see any odd entries after the camp_phone
> field.
>
>
>
>
>
> --
> Patrice Olivier-Wilson
> 888-385-7217
> http://biz-comm.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
- michael dykman
- mdykman@gmail.com

Don=92t worry about people stealing your ideas. If they=92re any good,
you=92ll have to ram them down their throats!

Howard Aiken

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Inserting csv

Those converters aren't all that great . I think its best to stay away from=
them. If a csv is too complex for a 'LOAD DATA INFILE' command then why no=
t use an etl tool like talend to load the data?

Its easy to see where your converter has gone wrong but to 'fix' the query =
I'd need the table info. Run 'SHOW CREATE TABLE membership;' in the mysql c=
lient and post the result here.

If you could post a copy of the csv (if it does not contain anything sensit=
ive), then myself or someone else on here could probably write you a 'LOAD =
DATA INFILE' command to put the data into your database fairly quickly.

Regards

John Daisley
Mobile +44(0)7812 451238
Email john@butterflysystems.co.uk

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer
Cognos BI Developer

-----------------------
Sent from HP IPAQ mobile device.



-----Original Message-----
From: Patrice Olivier-Wilson
Sent: 15 October 2009 16:26
To: [MySQL]
Subject: Inserting csv

Newbie question, please.

I have a csv file of 950 records, 20 fields.

I used this converter
http://csv2sql.evandavey.com/

and copied/pasted insert code into SQL in phpMyAdmin

and got this error


SQL query:

INSERT INTO membership( `members_ID` , `updated` , `notes` , =20
`preferred_mail_street` , `preferred_mail_csz` , `first_name` , =20
`last_name` , `street` , `city` , `state` , `zip` , `location_code`=20
, `property` , `camp_street` , `camp_city` , `camp_zip` ,=20
`member_year` , `director` , `email` , `camp_phone` , `20` , `21`=20
, `22` , `23` )
VALUES (

'', '', '', '', '', 'xxx', 'xxxx', '102 Summer St', =20
'Dover-Foxcroft', 'xx', '04426', 'B', 'M15_L1_S20', 'Mill Brook', =20
'Bowerbank', '', '', '', '', '', '', '', '', ''
);

MySQL said:

#1054 - Unknown column '20' in 'field list'

Actually the first time, it was column 21, so to trouble shoot, I removed t=
hat column from csv file and field name in phpMyAdmin.


camp_phone should be the last field but=20

`20` , `21` , `22` , `23` )=20

is there too...=20

Any assistance most appreciated!

I checked the file and didn't see any odd entries after the camp_phone fiel=
d.





--=20
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Djohn.daisley@butter=
flysystems.co.uk



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg