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
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]
Another LOAD Infile Problem
Many thanks for the earlier response to why LOAD DATA INFILE wasnt working for me. However, another problem has appeared. In the file I am reading, 2 of the fields are SUPPOSED to be float values. However, in several places, they are set to UNKNOWN. This seems to cause LOAD to abort. Is there a way for me to tell it to ignore this problem and just use the default value for the column? Jason
Re: Another LOAD Infile Problem
The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). Jason On 9/26/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: Jason Ferguson wrote: Many thanks for the earlier response to why LOAD DATA INFILE wasnt working for me. However, another problem has appeared. In the file I am reading, 2 of the fields are SUPPOSED to be float values. However, in several places, they are set to UNKNOWN. This seems to cause LOAD to abort. Is there a way for me to tell it to ignore this problem and just use the default value for the column? How about just replace occurrences of the string UNKNOWN in the original file with NULL (the logical equivalent) or 0 (if you're using NOT NULL columns) before doing LOAD DATA INFILE? -- 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]
