0

I am trying to load a file into mysql table using LOAD DATA INFILE. The csv file and destination table are structured as listed below.

File /data/data.csv

,col1,col2,col3
,2101,val2,val3

Table

  • col0ID = INT autonum
  • col1 = INT
  • col2 = VARCHAR
  • col3 = VARCHAR

When I run the script below I receive the following error: Error Code: 1366. Incorrect integer value: '' for column 'col1' at row 1

I think this is because of the comma prefix at the start of the line. If I remove the comma at the start of the header and row then the import works fine.

Is there a way to instruct mysql to load the data ignoring the first comma in the header and rows? Or, will I have to preformat the file using code (python or C#) beforehand?

MySQL LOAD DATA snippet

load data infile '/data/data.csv'
into table Staging
fields terminated by ',' enclosed by ''
lines terminated by '\r\n'
ignore 1 LINES
(col1,col2,col3)
set col0ID=null;

As suggested in comments, I looked at a similar [question](How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?) and tried using a variable in columns, e.g. (@discard,col1,col2,col3) but still fails with same error. In my scenario the column header is also prefixed by a comma , It looks like I might have to preprocess and cleanse the file before import....

O. Jones
  • 81,279
  • 15
  • 96
  • 133
anon_dcs3spp
  • 802
  • 3
  • 23
  • 2
    Does this answer your question? [How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?](https://stackoverflow.com/questions/2139069/how-to-skip-columns-in-csv-file-when-importing-into-mysql-table-using-load-data) – LV98 May 23 '21 at 12:17
  • Thanks, tried using (@discard,col1,col2,col3) but still fails with same error. In my scenario the column header is also prefixed by a comma , It looks like I might have to preprocess and cleanse the file before import.... – anon_dcs3spp May 23 '21 at 12:23
  • Thanks!!! It did work, I receive a different error relating to a different row now. Appreciated :) I will remove the question within the next 15mins... – anon_dcs3spp May 23 '21 at 12:30
  • 2
    Please don't delete the question. Instead please answer it yourself saying what you did; That way the next person can get the benefit of your wisdom. – O. Jones May 23 '21 at 12:31

1 Answers1

0

Solved it thanks to suggestion in comments of a related question.

load data infile '/data/data.csv'
into table Staging
fields terminated by ',' enclosed by ''
lines terminated by '\r\n'
ignore 1 LINES
(@discard,col1,col2,col3)
set col0ID=null;

Essentially it loads the first empty column denoted by the , (at the start of the line) into a variable called @discard.

This now reads the data of 78 rows before different unrelated error encountered...

anon_dcs3spp
  • 802
  • 3
  • 23