470,631 Members | 1,640 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,631 developers. It's quick & easy.

load data infile - not working, omits some records!

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 '\"' ESCAPED BY '\\';
Database changed
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 12
Another csv file with 933 records gets imported ok...

Can someone confirm for me I am using the right syntax for import?
Could it be that my CSV file doesn't religiously quote every field?
(i.e. empty fields are just empty, do not contain "")

I've tried removing the OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' from
the end and I get the same results.
The table structure is very 'normal' - the only only primary key is the
auto-increment index ID, the rest are normal fields (i.e. not primary,
unique, or index).

alex
Jul 20 '05 #1
2 6863
Alex Hunsley wrote:
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 '\"' ESCAPED BY '\\';
Database changed
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 12
Another csv file with 933 records gets imported ok...

Can someone confirm for me I am using the right syntax for import?
Could it be that my CSV file doesn't religiously quote every field?
(i.e. empty fields are just empty, do not contain "")

I've tried removing the OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' from
the end and I get the same results.
The table structure is very 'normal' - the only only primary key is the
auto-increment index ID, the rest are normal fields (i.e. not primary,
unique, or index).


update: I've made my CSV strictly quote every field, even if empty, and
I seem to be getting it to work now... is this a bug-bear with the CSV
import functionality of MySQL?

alex
Jul 20 '05 #2
Alex Hunsley wrote:
Alex Hunsley wrote:
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 '\"' ESCAPED BY
'\\';
Database changed
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 12
Another csv file with 933 records gets imported ok...

Can someone confirm for me I am using the right syntax for import?
Could it be that my CSV file doesn't religiously quote every field?
(i.e. empty fields are just empty, do not contain "")

I've tried removing the OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
from the end and I get the same results.
The table structure is very 'normal' - the only only primary key is
the auto-increment index ID, the rest are normal fields (i.e. not
primary, unique, or index).

update: I've made my CSV strictly quote every field, even if empty, and
I seem to be getting it to work now... is this a bug-bear with the CSV
import functionality of MySQL?

alex

Ok, problem solved...
I needed to have this in my load data command:

LINES TERMINATED BY '\r\n'

... for windows line feeds.

lex
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Bruce A. Julseth | last post: by
3 posts views Thread by hall | last post: by
reply views Thread by Montagna, Dan | last post: by
reply views Thread by Donald Tyler | last post: by
1 post views Thread by Jami Bradley | last post: by
5 posts views Thread by Justin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.