I have this following code to import selected columns into mysql workbench.
SET autocommit=0;
LOAD DATA LOCAL INFILE '150926.csv' INTO TABLE innodb.fh FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ignore 1 lines
(@dummy,date,@dummy,@dummy,@dummy,**CS-URI-STEM**,@dummy,@dummy,@dummy,c-ip,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy, session_id,@dummy,@dummy,@dummy);
COMMIT;
The issue is that I had a column named as "CS-URI-STEM" in my csv file, and my code is not working. I am just wondering if I need to change the column name in my csv file since it has dash in it. Or please let me know if I need to change the code. Many thx!!!!!!
The column names in the parentheses aren't column names from the file, they are column names from the table you're importing into. It's confusing if you think about it like the column name or value listing in an insert query because this works differently - here you specify which column in the file (by position in the list) goes into which column in the table (by the name of the column in the table). You have to forget about the column names in the file because they don't matter at all.
For example: Given a CSV file like this:
Row Number,Date,IP,Session,Whatever,Other columns...
1,2016-01-01,10.0.0.1,21312,"ABC",...
2,2016-01-02,10.0.0.2,21889,"XYZ",...
And a table like this:
| session_id | ip_address | action_date | session_description |
The respective column name list for the import would be:
(@dummy,action_date,ip_address,session_id,session_description,@dummy,@dummy,@dummy,...)
You can, in fact, leave off the trailing dummy variables, if you only need to import the data from the first columns (i.e., if you only need the data from the first five columns but the file has 10 columns, you can write only five columns in the import list.) MySQL will give a warning about (#1262, "Row X was truncated; it contained more data than there were input columns") it but it will import the data.