1

I am using the following statement in order to insert data from file.

load data infile 'filename.csv'
ignore 
into table tbl (clo);

I need to ignore the blank lines in the file. How can I do this.

Jury A
  • 19,192
  • 24
  • 69
  • 93

2 Answers2

1

You cannot skip lines unless they are at the beginning of the file which you can use the IGNORE N LINES option.

Use sed before importing:

sed -i '/^\s*$/d' filename.csv

For more details: Delete empty lines using SED

Community
  • 1
  • 1
shrimpwagon
  • 867
  • 12
  • 23
  • 1
    Ah, much more helpful. Always remember: __Links Die__. – TaW Jan 25 '16 at 17:20
  • If I have to import a 10 gig file, I really don't want to be pre-processing it, but I guess I can just write a custom program every time I want to import data efficiently... – ebyrob Dec 16 '17 at 17:18
-1
LOAD DATA INFILE 'filename.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

The \r is necessary on Windows files, if you're running this on *nix you shouldn't need them.

Rafe
  • 7,036
  • 5
  • 24
  • 27
  • 1
    Does not work. Gives me an error in the syntax. I need to specify the column exactly as I posted in my statement (I need the lines to be inserted in a specific column). – Jury A Oct 06 '12 at 16:39
  • @JuryA Yes, this does not answer the question – shrimpwagon Jan 25 '16 at 16:24