LOAD DATA INFILE (*.csv) - ignore empty cells

Question

I'm about to import a large (500 MB) *.csv file to a MySQL database.

I'm as far as that:

LOAD DATA INFILE '<file>'
    REPLACE
    INTO TABLE <table-name>
    FIELDS
        TERMINATED BY ';'
        OPTIONALLY ENCLOSED BY '"'
    IGNORE 1 LINES ( #Header
        <column-name1>,
        <column-name2>,
        ...
    );

I have a problem with one of the coluns (it's data type is int) - I get an error Message:

Error Code: 1366 Incorrect integer value: ' ' for column at row

I looked at this line in the *.csv-file. The cell that causes the error has just a whitespace inside (like this: ...; ;...).

How can I tell SQL to ignore whitespaces in this column?

As the *.csv-file is very big and I have to import even bigger ones afterwards, I'd like to avoid editing the *.csv-file; I'm looking for a SQL-solution.

Answer 1

Add a SET COLUMN like so:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

You need to replace the @var1/100 with an expression that handles the 'space' and convert to -Infinity or 0 or 42... not sure..