MYSQL import data from csv using LOAD DATA INFILE

Question

I am importing some data of 20000 rows from a CSV file into Mysql.

Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns?

When I execute

LOAD DATA INFILE'abc.csv' INTO TABLE abc

this query adds all data to the first column.

Please suggest auto syntax for importing data to Mysql.

Answer 1

Before importing the file, you must need to prepare the following:

• A database table to which the data from the file will be imported.
• A CSV file with data that matches with the number of columns of the table and the type of data in each column.
• The account, which connects to the MySQL database server, has FILE and INSERT privileges.

Suppose we have following table :

CREATE TABLE USING FOLLOWING QUERY :

CREATE TABLE IF NOT EXISTS `survey` (
  `projectId` bigint(20) NOT NULL,
  `surveyId` bigint(20) NOT NULL,
  `views` bigint(20) NOT NULL,
  `dateTime` datetime NOT NULL
);

YOUR CSV FILE MUST BE PROPERLY FORMATTED FOR EXAMPLE SEE FOLLOWING ATTACHED IMAGE :

If every thing is fine.. Please execute following query to LOAD DATA FROM CSV FILE :

NOTE : Please add absolute path of your CSV file

LOAD DATA INFILE '/var/www/csv/data.csv' 
INTO TABLE survey 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

If everything has done. you have exported data from CSV to table successfully

Answer 2

You probably need to set the FIELDS TERMINATED BY ',' or whatever the delimiter happens to be.

For a CSV file, your statement should look like this:

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

Answer 3

If you are running LOAD DATA LOCAL INFILE from the windows shell, and you need to use OPTIONALLY ENCLOSED BY '"', you will have to do something like this in order to escape characters properly:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql" -u root --password=%password% -e "LOAD DATA LOCAL INFILE '!file!' INTO TABLE !table! FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"^""' LINES TERMINATED BY '\n' IGNORE 1 LINES" --verbose --show-warnings > mysql_!fname!.out

Answer 4

I was getting Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

This worked for me on windows 8.1 64 bit using wampserver 3.0.6 64bit.

Edited my.ini file from C:\wamp64\bin\mysql\mysql5.7.14

Delete entry secure_file_priv c:\wamp64\tmp\ (or whatever dir you have here)

Stopped everything -exit wamp etc.- and restarted everything; then punt my cvs file on C:\wamp64\bin\mysql\mysql5.7.14\data\u242349266_recur (the last dir being my database name)

executed LOAD DATA INFILE 'myfile.csv'

INTO TABLE alumnos

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES

... and VOILA!!!

Answer 5

Insert bulk more than 7000000 record in 1 minutes in database(superfast query with calculation)

mysqli_query($cons, '
    LOAD DATA LOCAL INFILE "'.$file.'"
    INTO TABLE tablename
    FIELDS TERMINATED by \',\'
    LINES TERMINATED BY \'\n\'
    IGNORE 1 LINES
    (isbn10,isbn13,price,discount,free_stock,report,report_date)
     SET RRP = IF(discount = 0.00,price-price * 45/100,IF(discount = 0.01,price,IF(discount != 0.00,price-price * discount/100,@RRP))),
         RRP_nl = RRP * 1.44 + 8,
         RRP_bl = RRP * 1.44 + 8,
         ID = NULL
    ')or die(mysqli_error());
$affected = (int) (mysqli_affected_rows($cons))-1; 
$log->lwrite('Inventory.CSV to database:'. $affected.' record inserted successfully.');

RRP and RRP_nl and RRP_bl is not in csv but we are calculated that and after insert that.

Answer 6

let suppose you are using xampp and phpmyadmin

you have file name 'ratings.txt' table name 'ratings' and database name 'movies'

if your xampp is installed in "C:\xampp\"

copy your "ratings.txt" file in "C:\xampp\mysql\data\movies" folder

LOAD DATA INFILE 'ratings.txt' INTO TABLE ratings FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Hope this can help you to omit your error if you are doing this on localhost