LOAD DATA INFILE Syntax error
Hallo List! I have a CVS file which i would like to import to MySQL. The file header and an example: | Page,Device,Group,ItemID,Item,Value | Overview,General,Computer,513,OS,Linux The table has: | Create Table: CREATE TABLE `table` ( | `ID` int(11) NOT NULL auto_increment, | `Page` varchar(128) default NULL, | `Device` varchar(128) default NULL, | `Group` varchar(128) default NULL, | `ItemID` varchar(128) default NULL, | `Item` varchar(128) default NULL, | `Value` varchar(128) default NULL, | PRIMARY KEY (`ID`) | ) ENGINE=MyISAM DEFAULT CHARSET=latin1 So I would like to import the first file field to the second table field, the second file field to the third table,... Just to have an index. I'm using: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, GROUP , ItemID, Item, Value); which gives me: | #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 'Group, ItemID, Item, Value)' at line 2 I cannot find the error. Please help me! MySQL version = 5.0.32-Debian_7etch8-log TIA, Ralph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LOAD DATA INFILE Syntax error
Group is a keyword in mysql: You need to put backticks around it in your statement: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, `GROUP` , ItemID, Item, Value); On Mon, Jun 29, 2009 at 7:07 AM, Ralph Kutschera news2...@ecuapac.dyndns.org wrote: Hallo List! I have a CVS file which i would like to import to MySQL. The file header and an example: | Page,Device,Group,ItemID,Item,Value | Overview,General,Computer,513,OS,Linux The table has: | Create Table: CREATE TABLE `table` ( | `ID` int(11) NOT NULL auto_increment, | `Page` varchar(128) default NULL, | `Device` varchar(128) default NULL, | `Group` varchar(128) default NULL, | `ItemID` varchar(128) default NULL, | `Item` varchar(128) default NULL, | `Value` varchar(128) default NULL, | PRIMARY KEY (`ID`) | ) ENGINE=MyISAM DEFAULT CHARSET=latin1 So I would like to import the first file field to the second table field, the second file field to the third table,... Just to have an index. I'm using: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, GROUP , ItemID, Item, Value); which gives me: | #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 'Group, ItemID, Item, Value)' at line 2 I cannot find the error. Please help me! MySQL version = 5.0.32-Debian_7etch8-log TIA, Ralph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: LOAD DATA INFILE Syntax error
Johnny Withers schrieb: Group is a keyword in mysql: You need to put backticks around it in your statement: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, `GROUP` , ItemID, Item, Value); Ooookay. Thank you very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
LOAD DATA INFILE Syntax
Hi mysql-list,
I just installed,
++
| version() |
++
| 5.0.16-log |
++
Here is what I am testing out: (simple table and data)
% echo 'a b c d' testfile
mysql CREATE TABLE `test` (
- `fe` VARCHAR( 2 ),
- `fi` VARCHAR( 2 ),
- `fo` VARCHAR( 2 ),
- `fum` VARCHAR( 2 )
- );
Query OK, 0 rows affected (0.00 sec)
mysql load data local infile './test' into table test;
ERROR 1148 (42000): The used command is not allowed with this MySQL
version
Q: What am I doing wrong(missing)?
{ I did not see it in the documentation online }
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Thanks, Elliot
P.S.
The same command works fine in previous versions of MySQL.
__
WizardsWorks Cluster
http://www.wizardsworks.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA INFILE Syntax
Hi Elliot,
It is in the docs, just a little bit further down the page 8-)
LOCAL works only if your server and your client both have been enabled
to allow it. For example, if mysqld was started with --local-infile=0,
then LOCAL does not work. See Section 5.6.4, Security Issues with LOAD
DATA LOCAL.
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-Original Message-
From: Elliot Kleiman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 13 December 2005 8:12 AM
To: mysql@lists.mysql.com
Subject: LOAD DATA INFILE Syntax
Hi mysql-list,
I just installed,
++
| version() |
++
| 5.0.16-log |
++
Here is what I am testing out: (simple table and data)
% echo 'a b c d' testfile
mysql CREATE TABLE `test` (
- `fe` VARCHAR( 2 ),
- `fi` VARCHAR( 2 ),
- `fo` VARCHAR( 2 ),
- `fum` VARCHAR( 2 )
- );
Query OK, 0 rows affected (0.00 sec)
mysql load data local infile './test' into table test;
ERROR 1148 (42000): The used command is not allowed with this MySQL
version
Q: What am I doing wrong(missing)?
{ I did not see it in the documentation online }
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Thanks, Elliot
P.S.
The same command works fine in previous versions of MySQL.
__
WizardsWorks Cluster
http://www.wizardsworks.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE Syntax Error
I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): 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 '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason
Re: LOAD DATA INFILE Syntax Error
http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html Bob Cochran Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): 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 '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE Syntax Error
Robert L Cochran wrote: http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html There's a lot to read there for one small paragraph, so from the above link: Before MySQL 5.0.3, the column list must contain only names of columns in the table being loaded, and the SET clause is not supported. As of MySQL 5.0.3, the column list can contain either column names or user variables, and the SET clause is supported. This enables you to assign input values to user variables, and then perform transformations on those values before assigning the result to columns. Bob: please don't top-post. Jasper Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): 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 '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE Syntax Error
I've been over that page before posting, with no luck. It might be an obvious error in syntax, but I can't figure it out. Jason On 9/26/05, Robert L Cochran [EMAIL PROTECTED] wrote: http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html Bob Cochran Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): 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 '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason
Re: LOAD DATA INFILE Syntax Error
Okay, now I get it. I was using the 4.1 series. Looks like an upgrade is in order. Jason On 9/26/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: Robert L Cochran wrote: http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html There's a lot to read there for one small paragraph, so from the above link: Before MySQL 5.0.3, the column list must contain only names of columns in the table being loaded, and the SET clause is not supported. As of MySQL 5.0.3, the column list can contain either column names or user variables, and the SET clause is supported. This enables you to assign input values to user variables, and then perform transformations on those values before assigning the result to columns. Bob: please don't top-post. Jasper Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): 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 '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE syntax
Hi and thanx :) I've found dbf2mysql and it should solve my prob. However, my linux server is a production server (web + mail) and none programmation environnement is installed on it (no gcc and so on) and i don't want to. So, could anyone send me the binary for dbf2mysql ? I use Red-Hat 7.3 on i386. thanx fabrice. gerald_clark a écrit : 1 will work. 2 will not work. 3 Use dbf2mysql. ( It should be in the downloads section on mysql.com ) fab wrote: Hi all, I'm quite new in mysql. Despite i've read a part of the doc, i can't fix my prob. Here is my question: I want to convert a DBASE IV file into mysql table: 1) Have i to convert the dbf into flat file then use the LOAD DATA INFILE cmd ? or 2) Can i directly use the LOAD DATA INFILE cmd with my dbf file ? I've tried the second point but it doesn't seem to work. Thanx in advance. fabrice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE syntax
I've found the rpm. fab a écrit : Hi and thanx :) I've found dbf2mysql and it should solve my prob. However, my linux server is a production server (web + mail) and none programmation environnement is installed on it (no gcc and so on) and i don't want to. So, could anyone send me the binary for dbf2mysql ? I use Red-Hat 7.3 on i386. thanx fabrice. gerald_clark a écrit : 1 will work. 2 will not work. 3 Use dbf2mysql. ( It should be in the downloads section on mysql.com ) fab wrote: Hi all, I'm quite new in mysql. Despite i've read a part of the doc, i can't fix my prob. Here is my question: I want to convert a DBASE IV file into mysql table: 1) Have i to convert the dbf into flat file then use the LOAD DATA INFILE cmd ? or 2) Can i directly use the LOAD DATA INFILE cmd with my dbf file ? I've tried the second point but it doesn't seem to work. Thanx in advance. fabrice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE syntax
Hi all, I'm quite new in mysql. Despite i've read a part of the doc, i can't fix my prob. Here is my question: I want to convert a DBASE IV file into mysql table: 1) Have i to convert the dbf into flat file then use the LOAD DATA INFILE cmd ? or 2) Can i directly use the LOAD DATA INFILE cmd with my dbf file ? I've tried the second point but it doesn't seem to work. Thanx in advance. fabrice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE syntax
1 will work. 2 will not work. 3 Use dbf2mysql. ( It should be in the downloads section on mysql.com ) fab wrote: Hi all, I'm quite new in mysql. Despite i've read a part of the doc, i can't fix my prob. Here is my question: I want to convert a DBASE IV file into mysql table: 1) Have i to convert the dbf into flat file then use the LOAD DATA INFILE cmd ? or 2) Can i directly use the LOAD DATA INFILE cmd with my dbf file ? I've tried the second point but it doesn't seem to work. Thanx in advance. fabrice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: load data infile syntax
If the data contains \, then database will automatically takes \ away and shift 1 byte left. My data will mass up. That is why I still need \ Does your data actually include backslash characters? If you want to import them as they are, you definitely don't want to ESCAPE BY them. Escaping is used to treat otherwise special characters as nonspecial. For example, quotes and commas have special meaning in some text files, but you can 'escape' the special interpretation by using a scheme like prefixing them with a character that's more special than they are. The prefix is usually a backslash, which means that if you really do want a backslash, you need to use two of them (the first one says to treat the next character in a different way than usual). So the moral of the story is this: If you're trying to import backslashes into an SQL table, and your text file doesn't have backslashes doubled, don't choose a backslash as your ESCAPE BY character. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: load data infile syntax
I did my testing. If I use ESCAPED BY '\\', I got 1064, You have an error in your SQL syntax near ''' at line 2 Using ESCAPED BY '\', then no error and result is fine. If the data contains \, then database will automatically takes \ away and shift 1 byte left. My data will mass up. That is why I still need \ I posted my syntax before, but nobody helped me to figure out. So this time I did my test again and found out only need one \ for ESCAPED BY. At 10:43 PM 11/5/2002 -0600, you wrote: At 14:38 -0500 11/5/02, Vivian Wang wrote: The syntac ESCAPED BY of load data infile is wrong in http://www.mysql.com/doc/en/LOAD_DATA.html. the [ESCAPED by '\\'] should be: [ESCAPED BY '\'] Why do you say that? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: load data infile syntax
At 10:03 -0500 11/6/02, Vivian Wang wrote: I did my testing. If I use ESCAPED BY '\\', I got 1064, You have an error in your SQL syntax near ''' at line 2 Using ESCAPED BY '\', then no error and result is fine. If the data contains \, then database will automatically takes \ away and shift 1 byte left. My data will mass up. That is why I still need \ I posted my syntax before, but nobody helped me to figure out. So this time I did my test again and found out only need one \ for ESCAPED BY. I think there's something else wrong with the statement. You do need to use two backslashes to specify an escape character of a single backslash. Can we see the entire LOAD DATA statement exactly as you specified it originally? At 10:43 PM 11/5/2002 -0600, you wrote: At 14:38 -0500 11/5/02, Vivian Wang wrote: The syntac ESCAPED BY of load data infile is wrong in http://www.mysql.com/doc/en/LOAD_DATA.html. the [ESCAPED by '\\'] should be: [ESCAPED BY '\'] Why do you say that? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: load data infile syntax
This is my statement using python, db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '\' lines terminated by '\n'); Is python problem? not sure. At 10:01 AM 11/6/2002 -0600, Paul DuBois wrote: At 10:03 -0500 11/6/02, Vivian Wang wrote: I did my testing. If I use ESCAPED BY '\\', I got 1064, You have an error in your SQL syntax near ''' at line 2 Using ESCAPED BY '\', then no error and result is fine. If the data contains \, then database will automatically takes \ away and shift 1 byte left. My data will mass up. That is why I still need \ I posted my syntax before, but nobody helped me to figure out. So this time I did my test again and found out only need one \ for ESCAPED BY. I think there's something else wrong with the statement. You do need to use two backslashes to specify an escape character of a single backslash. Can we see the entire LOAD DATA statement exactly as you specified it originally? At 10:43 PM 11/5/2002 -0600, you wrote: At 14:38 -0500 11/5/02, Vivian Wang wrote: The syntac ESCAPED BY of load data infile is wrong in http://www.mysql.com/doc/en/LOAD_DATA.html. the [ESCAPED by '\\'] should be: [ESCAPED BY '\'] Why do you say that? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: load data infile syntax
At 11:18 -0500 11/6/02, Vivian Wang wrote: This is my statement using python, db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '\' lines terminated by '\n'); Is python problem? not sure. Looks like what's happening is that Python itself is processing escapes before passing the query to MySQL, and MySQL itself will then process escapes. You have to account for the extra level of escape processing. Try this: db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '' lines terminated by '\\n'); At 10:01 AM 11/6/2002 -0600, Paul DuBois wrote: At 10:03 -0500 11/6/02, Vivian Wang wrote: I did my testing. If I use ESCAPED BY '\\', I got 1064, You have an error in your SQL syntax near ''' at line 2 Using ESCAPED BY '\', then no error and result is fine. If the data contains \, then database will automatically takes \ away and shift 1 byte left. My data will mass up. That is why I still need \ I posted my syntax before, but nobody helped me to figure out. So this time I did my test again and found out only need one \ for ESCAPED BY. I think there's something else wrong with the statement. You do need to use two backslashes to specify an escape character of a single backslash. Can we see the entire LOAD DATA statement exactly as you specified it originally? At 10:43 PM 11/5/2002 -0600, you wrote: At 14:38 -0500 11/5/02, Vivian Wang wrote: The syntac ESCAPED BY of load data infile is wrong in http://www.mysql.com/doc/en/LOAD_DATA.html. the [ESCAPED by '\\'] should be: [ESCAPED BY '\'] Why do you say that? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: load data infile syntax
I tried, db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '' lines terminated by '\\n'); db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '' lines terminated by '\n'); both no error, but my data still shift 1 byte to left. Also, I tried from mysql, mysqlload data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '\\' lines terminated by '\n'; no error, but my data still shift to left. mysqlload data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '\' lines terminated by '\n'; PAGER set to stdout ' ' - ; ERROR 1083: Field separator argument is not what is expected. Check the manual I don't want my data shift to left because the \. Can I change any settings? this python statement works ok for me now, not perfect. I really want escape \, but no shift. db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '\' lines terminated by '\n'); At 10:26 AM 11/6/2002 -0600, Paul DuBois wrote: At 11:18 -0500 11/6/02, Vivian Wang wrote: This is my statement using python, db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '\' lines terminated by '\n'); Is python problem? not sure. Looks like what's happening is that Python itself is processing escapes before passing the query to MySQL, and MySQL itself will then process escapes. You have to account for the extra level of escape processing. Try this: db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '' lines terminated by '\\n'); At 10:01 AM 11/6/2002 -0600, Paul DuBois wrote: At 10:03 -0500 11/6/02, Vivian Wang wrote: I did my testing. If I use ESCAPED BY '\\', I got 1064, You have an error in your SQL syntax near ''' at line 2 Using ESCAPED BY '\', then no error and result is fine. If the data contains \, then database will automatically takes \ away and shift 1 byte left. My data will mass up. That is why I still need \ I posted my syntax before, but nobody helped me to figure out. So this time I did my test again and found out only need one \ for ESCAPED BY. I think there's something else wrong with the statement. You do need to use two backslashes to specify an escape character of a single backslash. Can we see the entire LOAD DATA statement exactly as you specified it originally? At 10:43 PM 11/5/2002 -0600, you wrote: At 14:38 -0500 11/5/02, Vivian Wang wrote: The syntac ESCAPED BY of load data infile is wrong in http://www.mysql.com/doc/en/LOAD_DATA.html. the [ESCAPED by '\\'] should be: [ESCAPED BY '\'] Why do you say that? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: load data infile syntax
At 12:38 -0500 11/6/02, Vivian Wang wrote: I tried, db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '' lines terminated by '\\n'); db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '' lines terminated by '\n'); both no error, but my data still shift 1 byte to left. What do you mean by shift 1 byte to left. What's an example of your input data and how it actually ends up in MySQL? Also, I tried from mysql, mysqlload data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '\\' lines terminated by '\n'; no error, but my data still shift to left. mysqlload data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '\' lines terminated by '\n'; PAGER set to stdout ' ' - ; ERROR 1083: Field separator argument is not what is expected. Check the manual That last statement is illegal, because '\' needs to be '\\'. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
load data infile syntax
The syntac ESCAPED BY of load data infile is wrong in http://www.mysql.com/doc/en/LOAD_DATA.html. the [ESCAPED by '\\'] should be: [ESCAPED BY '\'] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: load data infile syntax
At 14:38 -0500 11/5/02, Vivian Wang wrote: The syntac ESCAPED BY of load data infile is wrong in http://www.mysql.com/doc/en/LOAD_DATA.html. the [ESCAPED by '\\'] should be: [ESCAPED BY '\'] Why do you say that? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
