load data infile - not working, omits some records!

[Alex Hunsley]

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

[Alex Hunsley]

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

[Alex Hunsley]

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