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....