BryanK BryanK - 6 months ago 48
MySQL Question

LOAD DATA INFILE, skipping first column in table

I am having trouble finding the correct syntax for loading a csv file into table, while skipping the first column which already exists in the table. My table columns looks like this:

ID COL1 COL2 COL3 LOG_DATE


and my csv looks like this :

dataForCol1,dataForCol2,dataForCol3


So I want to load the values in the csv into COL1 COL2 and COL3 , skipping ID. The closest I can get is with SQL like this:

LOAD DATA LOCAL INFILE 'test.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(ID,COL1,COL2,COL3,LOG_DATE)
SET ID=0, LOG_DATE=CURRENT_TIMESTAMP


Note that I dont know SQL too well and I am not sure if I am using the SET clause correctly either, but this statement will supply the
LOG_DATE
column with a timestamp, and It will auto increment the
ID
column (
ID
is
type: int(11
) and
auto_incerement
) but the other data is off by one column so
dataForCol1
is missing and
dataForCol2
is in
COL1
etc.

Answer

The column list should iclude the columns that are in the file only:

LOAD DATA LOCAL INFILE 'test.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n' 
(COL1,COL2,COL3)
SET LOG_DATE=CURRENT_TIMESTAMP