importing into MySQL giving error #1064

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

importing into MySQL giving error #1064

Post by jonnyfortis »

I am trying to import and excel file that has been converted into Tab Delimited txt
and am getting the following error
[text]
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ProductID Product Producer Description Colors Size Price MinimumQty Discount Sto' at line 1 [/text]

the headers of the text document are

[text]ProductID Product Producer Description Colors Size Price MinimumQty Discount Stock UnitWeight Image ImageDetail catID[/text]

the table already does have content in
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: importing into MySQL giving error #1064

Post by Celauran »

Some sample content and the code you're using to import this would be helpful.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: importing into MySQL giving error #1064

Post by jonnyfortis »

Celauran wrote:Some sample content and the code you're using to import this would be helpful.
the

here are 3 lines from the text document
[text]
ProductID Product Producer Description Colors Size Price MinimumQty Discount Stock UnitWeight Image ImageDetail catID
1000 "shirt, Green, Bows&Arrows" OEM NULL NULL NULL 0.00 0 NULL 0 0 NULL NULL NULL
1001 "shirt, Grey, Crosses" OEM NULL NULL NULL 0.00 0 NULL 0 0 NULL NULL NULL
[/text]
i have left it as it is in the text document

the table structure i am trying to import to is

[text]Field | Type | Collation | Attributes | Null | Default
ProductID| int(11) ||| No | 0
Product |varchar(200) | latin1_swedish_ci||No|
Producer |varchar(20) | latin1_swedish_ci || Yes |OEM
Description |text | latin1_swedish_ci || No |
Colors | tinytext | latin1_swedish_ci | | Yes| NULL
Size | varchar(200) | latin1_swedish_ci || Yes | NULL
Price|decimal (10,2)| ||Yes |0.00
MinimumQty |tinyint(3)||| No |0
Discount |varchar(30) | latin1_swedish_ci || Yes | NULL
Stock |tinyint(3) | | | Yes | 0
UnitWeight | float | | | Yes | NULL
Image | varchar(100) | latin1_swedish_ci | | Yes | media/images/vuoto.gif
ImageDetail |varchar(100) | latin1_swedish_ci | | Yes | NULL
catID |varchar(80) | latin1_swedish_ci | | No |[/text]

and i am using the import tab in phpMyAdmin
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: importing into MySQL giving error #1064

Post by Celauran »

That's not tab delimited, that's using a variable number of spaces. Change the column delimiters to something consistent and it works fine.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: importing into MySQL giving error #1064

Post by jonnyfortis »

Celauran wrote:That's not tab delimited, that's using a variable number of spaces. Change the column delimiters to something consistent and it works fine.
i saved it as text (tab delimited)(*.txt) in exel, is this not correct?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: importing into MySQL giving error #1064

Post by Weirdan »

Celauran wrote:That's not tab delimited, that's using a variable number of spaces. Change the column delimiters to something consistent and it works fine.
Spaces come from phpbb. The post source has tabs.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: importing into MySQL giving error #1064

Post by Celauran »

Apologies, then. All the same, using a semicolon as a delimiter worked perfectly fine. If tabs aren't working, it's worth a shot.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: importing into MySQL giving error #1064

Post by jonnyfortis »

Celauran wrote:Apologies, then. All the same, using a semicolon as a delimiter worked perfectly fine. If tabs aren't working, it's worth a shot.
sorry can you show me an example of what you mean
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: importing into MySQL giving error #1064

Post by Celauran »

Just this

Code: Select all

1000;"shirt, Green, Bows&Arrows";OEM;NULL;NULL;NULL;0.00;0;NULL;0;0;NULL;NULL;NULL
1001;"shirt, Grey, Crosses";OEM;NULL;NULL;NULL;0.00;0;NULL;0;0;NULL;NULL;NULL
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: importing into MySQL giving error #1064

Post by jonnyfortis »

Celauran wrote:Just this

Code: Select all

1000;"shirt, Green, Bows&Arrows";OEM;NULL;NULL;NULL;0.00;0;NULL;0;0;NULL;NULL;NULL
1001;"shirt, Grey, Crosses";OEM;NULL;NULL;NULL;0.00;0;NULL;0;0;NULL;NULL;NULL
ive tried that and am getting the following error

[text]SQL query:

1000;


MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1000' at line 1 [/text]
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: importing into MySQL giving error #1064

Post by Christopher »

You understand that you cannot execute that CSV data as SQL you need to put it in a file and either use PHP to read it line by line and insert each row into the database, or use LOAD DATA INFILE to import the data from a file (see the MySQL manual for that).
(#10850)
Post Reply