how to skip lines of a csv file while using LOAD DATA command?

Question

hi,

i'm using sql command load data to insert data in a csv file to mysql database. the problem is that at the end of the file there's a few line like ",,,,,,,,,,,,,,,,,," (the csv file is a conversion of an excel file). so when sql get to those lines he send me : #1366 - Incorrect integer value: '' for column 'Bug_ID' at row 661. the 'bug_id' is an int and i have 32 column.

how can i tell him to ignore those lines considering the number of filed lines is variable?

thanks for your help.

Answer 1

i fixed it: i just added a condition on the line in my csv parser

while ((line = is.readLine()) != null) {

  if (!line.equals(",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"))
      {
  Iterator e = csv.parse(line).iterator();
      ......
      }

}

Answer 2

MySQL supports a 'LINES STARTING BY "xxxx" ' for when reading delimited text files. If you can, require your specific .CVS file to have each data line with a 'prefix' and non-data lines to not have that prefix. This gives you the benefit of being able to putting comments into a .CSV if desired.

MySQL Doc: Load Data InFile

You can:

step 1 - (optionally) export data:

SELECT * 
  INTO OUTFILE "myFile.csv"
  COLUMNS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  ESCAPED BY '\\'
  LINES STARTING BY 'DATA:'
  TERMINATED BY '\n'
FROM table

step 2 - import data

LOAD DATA INFILE "myFile.csv"
INTO TABLE some_table
     COLUMNS TERMINATED BY ','
     OPTIONALLY ENCLOSED BY '"'
     ESCAPED BY '\\'
     LINES STARTING BY 'DATA:'

Effectively you can modify the .csv file to look like this:

# Comment for humans
// Comment for humans
Comments for us humans.
DATA:1,3,4,5,6,'asdf','abcd'
DATA:4,5,6,7,8,'qwerty','zxcv'
DATA:9,8,7,6,5,'yuio','hjlk'
# Comments for humans
// Comments for humans
Comments for humans
DATA:13,15,64,78,54,'bla bla','foo bar'

Only the lines with 'DATA:' prefix will be interpreted/read by the construct.

I used this technique to create a 'config' file for a SQL script that needed external control information. But there was a human element that needed to be able to easily manipulate the .csv file and understand its contents.

-- J Jorgenson --