Page 1 of 1

importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 9:49 am
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

Re: importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 10:14 am
by Celauran
Some sample content and the code you're using to import this would be helpful.

Re: importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 10:36 am
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

Re: importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 11:28 am
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.

Re: importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 11:37 am
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?

Re: importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 11:38 am
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.

Re: importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 11:40 am
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.

Re: importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 12:05 pm
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

Re: importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 12:45 pm
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

Re: importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 2:11 pm
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]

Re: importing into MySQL giving error #1064

Posted: Mon Jul 21, 2014 3:25 pm
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).