How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE

Question

I have a CSV file which contains 10 columns  I want to select only some columns from that file and load them into a MySQL database using the LOAD DATA INFILE command

User · Answer

Specify the name of columns in the CSV in the load data infile statement.

The code is like this:

LOAD DATA INFILE '/path/filename.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(column_name3, column_name5);

Here you go with adding data to only two columns(you can choose them with the name of the column) to the table.

The only thing you have to take care is that you have a CSV file(filename.csv) with two values per line(row). Otherwise please mention. I have a different solution.

Thank you.

User · Answer

LOAD DATA INFILE  file csv    INTO TABLE t1    column1   dummy  column2   dummy  column3       FIELDS TERMINATED BY     ENCLOSED BY     ESCAPED BY     LINES TERMINATED BY   r n     Just replace the column1  column2  etc   with your column names  and put  dummy anwhere there s a column in the CSV you want to ignore   Full details here

User · Answer

Load data into a table in MySQL and specify columns   LOAD DATA LOCAL INFILE  file csv  INTO TABLE t1  FIELDS TERMINATED BY     LINES TERMINATED BY   n      col1  col2  col3  col4  set name  col4 id  col2      col1 2 3 4 are variables to hold the csv file columns  assume 4   name id are table columns

User · Answer

if you have number of columns in your database table more than number of columns in your csv you can proceed like this   LOAD DATA LOCAL INFILE  pathOfFile csv  INTO TABLE youTable  CHARACTER SET latin1 FIELDS TERMINATED BY      you can use     if you have comma separated OPTIONALLY ENCLOSED BY      ESCAPED BY       LINES TERMINATED BY   r n   yourcolumn yourcolumn2 yourcolumn3 yourcolumn4

User · Answer

For those who have the following error        Error Code  1290  The MySQL server is running with the   --secure-file-priv option so it cannot execute this statement   You can simply run this command to see which folder can load files from   SHOW VARIABLES LIKE  secure file priv     After that  you have to copy the files in that folder and run the query with LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE

User · Answer

Example   contents of the ae csv file    Date  xpto 14   code   number   year   C   blab   15885   2016   Y   aeea   15883   1982   E   xpto   15884   1986   B   jrgg   15885   1400   A      CREATE TABLE Tabletmp         rec VARCHAR 9        For put only column 3   LOAD DATA INFILE   local ae csv   INTO TABLE Tabletmp FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   r n  IGNORE 2 LINES   col1   col2   col3   col4   col5  set rec    col3    select   from Tabletmp      2016     1982     1986     1400

[mysql] How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE

Examples related to mysql

Examples related to csv

Examples related to mysql-loadfile