MySQL - Import .CSV/Excel file into MySQL

Question

How do I do it when I want to Import a csv/excel file but I want the contents to be in a specific column?

I have 6 columns in my database. Let's just call it a, b, c, d, e, f.

I want to insert contents to c, d, e from the excel file. Contents of the column a, b and f would be automatically generated.

I'm using the LOAD DATA INFILE script.

spencer7593 · Accepted Answer · 2014-09-16 01:40:46Z

0

One option is to specify user-defined variables as a target for a fiel, in the place of a column name in the column list at the end of the LOAD DATA statement.

For example (omitting most of the syntax):

LOAD DATA ... 
...     
(@dummy1, @dummy2, c, d, e, @dummy3)

The first field from a line will be assigned to user-defined variable @dummy1, the second field to another user-defined variable, the third, fourth and fifth fields will be assigned to columns c, d, and e (respectively), the sixth field will be assigned to another user-defined variable.

If you want to provide a value for a column that's the result of an expression, you can use the SET clause...

LOAD DATA ... 
...     
(@a, @b, c, d, e, @dummy3) 
SET a = UPPER(@a) 
  , b = STR_TO_DATE(@b,'%m/%d/%Y')
  , f = CONCAT(@a,@b)

UPDATE

If you only have three fields in the csv, and you want to assign the first field to column c, the second field value to column d, and the third field value to column e...

LOAD DATA ...
...
(c, d, e)

It's the position of the column name in the (col_or_user_var,...) list that determines which field value will be assigned to which column. To "skip" a field, assign it to user-defined variable... the field in the file has to be assigned to something, and if you don't want to assign it to a column, the assign it to user-defined variable.

It wasn't clear in your question how many fields were in the file, and which field should be assigned to which column.

edited Sep 16, 2014 at 1:40

answered Sep 16, 2014 at 1:17

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gibs Over a year ago

Would it affect the structure of the csv? I've read the SET part but I am thinking that it might ruin the database. The csv/excel contains the 3 columns that needs to be inserted into c, d, e. It might insert it into a, b and c then the SET part would be putting the autogenerated value at a and b which makes the c the only useable part. Please correct me if I'm wrong :)

spencer7593 Over a year ago

You don't need to use the SET clause; that's entirely optional. Each field in the line will be assigned to the column or the user-defined variable in the column list, in the corresponding position. If you don't need the value from the first field for anything, just assign it to a user-defined variable... and voila that field value is gone. (The first example is equivalent to an INSERT INTO mytable (c,d,e). Any columns in the table that aren't referenced will be assigned the DEFAULT defined for the column. (The SET clause gives you access to the values of the fields that were assigned to var

Collectives™ on Stack Overflow

MySQL - Import .CSV/Excel file into MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related