load data infile - not working, omits some records!

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Alex Hunsley
    #1

    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
    Tags: None
    • Alex Hunsley
      #2
      Re: load data infile - not working, omits some records!

      Alex Hunsley wrote:[color=blue]
      > 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).[/color]

      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

        Comment

        • Alex Hunsley
          #3
          Re: load data infile - not working, omits some records!

          Alex Hunsley wrote:[color=blue]
          > Alex Hunsley wrote:
          >[color=green]
          >> 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).[/color]
          >
          >
          > 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[/color]
          Ok, problem solved...
          I needed to have this in my load data command:

          LINES TERMINATED BY '\r\n'

          ... for windows line feeds.

          lex

            Comment

            Previous template Next
            Working...