How I can Import data from CSV to MySQL?

Question

I have csv file with following structure:

    A   BA0011  U206    NAME    0000000000000149.00     000000.00  0000000000000118.93  S   N   N
    A   BB0011  U206    NAME    0000000000000150.00     000000.00  0000000000000118.93  S   N   N
    A   BC0011  U206    NAME    0000000000000151.00     000000.00  0000000000000118.93  S   N   N
    A   BD0011  U206    NAME    0000000000000152.00     000000.00  0000000000000118.93  S   N   N
    A   BE0011  U206    NAME    0000000000000153.00     000000.00  0000000000000118.93  S   N   N

Using following MySQL Function How I can import first and third column in MySQL table:

LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
  FIELDS TERMINATED BY \t' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

I apologize for my English!

Thanks in advance !

See the CSV storage engine http://dev.mysql.com/doc/refman/5.6/en/csv-storage-engine.html for extra fun. — biziclop, Mar 04 '12 at 13:54

SecStone · Accepted Answer · 2012-03-04T14:18:33.017

6

You can specify the columns and mark the unnecessary columns as '@dummy'.

LOAD DATA INFILE 'data.csv'
INTO TABLE t1
(column1, @dummy, column2, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy)
FIELDS TERMINATED BY '\t' ENCLOSED BY 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Replace t1, column1 and column2 as you like. To set other columns which are not in the data file, you can do it like this:

LOAD DATA INFILE 'data.csv'
INTO TABLE t1
(column1, @dummy, column2, @dummy, @val, @dummy, @dummy, @dummy, @dummy, @dummy)
FIELDS TERMINATED BY '\t' ENCLOSED BY 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
SET column3 = "test", column4 = CURRENT_TIMESTAMP, column5 = @val/10;

For further reference, I recommend you to take a look at the MySQL reference.

edited Mar 04 '12 at 14:18

answered Mar 04 '12 at 13:55

SecStone

1,733
4
20
31

If you must use this code, the table must contain all fields that are in the file. – dido Mar 04 '12 at 14:08
@dilyan_kn I added an example which defines some additional columns. – SecStone Mar 04 '12 at 14:23
where shd be data.csv putted ? – Bhavin Rana Jun 09 '13 at 13:51

score 0 · Answer 2 · answered Mar 04 '12 at 13:48

0

Perhaps you could import it into a table that has all the columns (to match the CSV), then select-insert it into your target table?

answered Mar 04 '12 at 13:48

Tim Gage

1,381
10
19

How I can Import data from CSV to MySQL?

2 Answers2

Linked

Related