473,837 Members | 1,553 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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|C hatham|13|051 |320446N|081050 2W|32.07944|-
81.08389||||||| |Savannah

With this command:
LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DE CI.txt'
INTO TABLE locations2
FIELDS TERMINATED BY '|'
LINES TERMINATED BY "\r"
(@d,@d,LOCNAME, LOCTYPE,@d,STAT E,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,LOCTYP E,@d,STATE,COUN TY,@d,@d,LNG,LA T,@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

Sep 27 '05 #1
1 5954
DartmanX wrote:
LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DE CI.txt'
INTO TABLE locations2
FIELDS TERMINATED BY '|'
LINES TERMINATED BY "\r"
(@d,@d,LOCNAME, LOCTYPE,@d,STAT E,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.


Are you using MySQL 5.0.3 or later? Putting imported data into user
variables is not supported in releases earlier than 5.0.3, according to
this page:
http://dev.mysql.com/doc/mysql/en/load-data.html

"As of MySQL 5.0.3, the column list can contain either column names or
user variables..."

If you're using an earlier release of MySQL, I'd suggest creating a
temporary table with enough columns to store all the fields in your txt
file, and then once it's imported, you can copy the columns you need to
your locations2 table with INSERT...SELECT .

Another option is that you could install a copy of the latest MySQL 5.0
release candidate on another computer (or the same computer, if you're
feeling intrepid about maintaining two instances of MySQL on one host),
then import your text file using the @d dummy variables technique, then
make a SQL dump file of the resulting table, so it's easier to import to
your 'real' MySQL instance.

Regards,
Bill K.
Sep 27 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
9765
by: Bruce A. Julseth | last post by:
When I execute this SQL statement in my PHP code, I get an error "File '.\Address.txt' not found (Errcode: 2)" $File = addslashes(".\Address.txt"); $SQL = "Load Data InFile \"" . $File . "\" into table addresses"; $result = mysql_query($SQL) or die(mysql_error()); The file is located in the same directory as my .PHP file. How do I generate a relative address for this file so that it can be found?
3
4518
by: Otto | last post by:
Hello to all I have a problem how to write correctly a PHP string. I did the following import with the help of PHPMyAdmin: Nombre d'enregistrements insérés : 364 (traitement: 0.0553 sec.) requête SQL: LOAD DATA LOCAL INFILE '/tmp/phpVfO8r8' INTO TABLE `fncid`
0
6692
by: Donald Tyler | last post by:
Then the only way you can do it that I can think of is to write a PHP script to do basically what PHPMyAdmin is trying to do but without the LOCAL in there. However to do that you would need to be able to place the PHP file on the server, and I guess you probably can't do that either. Talk about catch 22... The only other way I can think of is to install MySQL on a machine you control, then import the data there using the method I...
1
4089
by: Jami Bradley | last post by:
HI all, For the past several months we have been using LOAD DATA LOCAL INFILE to bulk load tables within Perl modules. Recently, someone thought it would be a good idea to upgrade the Solaris machines - I'm thinking it wasn't that good an idea :-( We are now running MySQL 4.0.18 and the DBI version is 1.35. Now, our scripts are failing with the dreaded "The used command is not allowed with this MySQL version" message. Here's what I...
0
1861
by: Rajesh Kapur | last post by:
Hello, I am running MySQL 4.0.16. I get the error 'The used command is not allowed with this MySQL version' when using the 'load data local infile' command. The local_infile is ON; the select * into outfile works but load data local infile does not. What am I doing wrong? Thanks! DETAILS --->
2
7065
by: Alex Hunsley | last post by:
I'm using a mysql monitor under cygwin (on win xp) to do a 'load data infile' to put some data into a mysql database (I'm using the xampp bundle).. My problem is that I have a four line CSV file beign inserted, but only two records actually get inserted: mysql> use master; delete from data; LOAD DATA INFILE 'D:/customJobs/database setup/dbaseFourLines.csv' REPLACE INTO TABLE data FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'...
1
16091
by: Ray in HK | last post by:
What are the differences between LOAD DATA INFILE and LOAD DATA LOCAL INFILE ? I found some web hosting company do not allow using LOAD DATA INFILE but allow LOAD DATA LOCAL INFILE. The reason is for the sake of security. What does that mean ?
3
6823
by: chamindas | last post by:
$singlequote ="\'"; $myquery="load data local infile '$filename' replace into table $tablename fields terminated by ';' enclosed by '$doublequote' escaped by '$singlequote' "; $execute=$connect->query($myquery); when I am running above perl script it generates an errror like this DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax...
0
2175
by: lanesbalik | last post by:
hi all, right now i'm trying to migrate from db2 running under linux to mysql v5.1. i manage to export out the db2 structure & data into a del (ascii) file. but when i try to load the data from the del file to mysql table, it generate an error. below is the load data infile syntax i use =
0
9827
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10863
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10560
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10263
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7798
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6987
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5838
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4468
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3120
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.