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.

There is a similar topic in http://stackoverflow.com/questions/11077801/import-csv-to-mysql-table/32949959#32949959 — marciomolusco, Jul 23 '16 at 21:22
It happened to me, I found out that the text file was written with lines terminated by '\r' and I was trying to import the data expecting the lines to be terminated using the '\n' — Tamer, Jan 29 '17 at 09:45
I wrote extensive tutorial to [load csv data into mysql](http://kedar.nitty-witty.com/blog/load-delimited-data-csv-excel-into-mysql-server) along with a [syntax generator tool in Excel](http://kedar.nitty-witty.com/blog/mysql-load-data-infile-syntax-generator-tool). It should be useful to readers. — mysql_user, Jan 24 '18 at 09:27

score 187 · Answer 1 · edited Apr 23 '19 at 12:48

187

You can use LOAD DATA INFILE command to import csv file into table.

Check this link MySQL - LOAD DATA INFILE.

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

For MySQL 8.0 users:

Using the LOCAL keyword hold security risks and as of MySQL 8.0 the LOCAL capability is set to False by default. You might see the error:

ERROR 1148: The used command is not allowed with this MySQL version

You can overwrite it by following the instructions in the docs. Beware that such overwrite does not solve the security issue but rather just an acknowledge that you are aware and willing to take the risk.

edited Apr 23 '19 at 12:48

Luis

989
1
8
30

answered Jan 03 '13 at 05:52

Saharsh Shah

27,121
8
41
77

2

May I know what ```IGNORE 1 LINES``` exactly does here? Thanks in advance. – Koushik Das May 26 '16 at 05:37
6

@KoushikDas It will ignore the first line which defines column names – Saharsh Shah May 26 '16 at 06:41
33

caution: the LINES TERMINATED BY '\r\n' clause works for windows files, but linux files should have '\n' only. – ChuckCottrill Jun 15 '16 at 23:58
2

Important to remember to use forward slashes: https://stackoverflow.com/a/42878067/470749 – Ryan May 29 '18 at 02:16
4

just a note that the (col1, col2, col3....) is not required. Useful if you are creating a table on the fly before using this to insert data. – Kieran Quinn Mar 04 '19 at 14:16
@ChuckCottrill It's better to define LINES TERMINATOR when export data. – Kymo Wang Mar 26 '21 at 07:13

score 53 · Answer 2 · edited Mar 28 '16 at 12:17

53

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;

edited Mar 28 '16 at 12:17

Sandeep

1,474
6
20
32

answered Jan 03 '13 at 05:14

ckim

894
6
7

1

LOAD DATA LOCAL INFILE...Without LOCAL it was access denied. – ketankk May 31 '18 at 14:18

score 51 · Answer 3 · edited Oct 23 '15 at 15:10

51

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

edited Oct 23 '15 at 15:10

Mohammed Sufian

1,625
3
31
59

answered Sep 16 '15 at 09:49

Sunny S.M

4,610
1
32
36

How can we validate if CSV file have proper datatype for each column because by default it ignores invalid datatype. – Umar Abbas Jan 08 '16 at 13:38
2

Sorry to disturb on such an old answer. It's very convincing but how can I skip some field. Let's say my table has projectId, surveyId and dateTime, how can I skip "views" field? – Danish Ansari May 25 '18 at 07:38
1

@MD Danish Ansari, Sorry to answer an old question, but you can specify the table columns. I did the following, where I need to skip the first field (an autoincrement primary key), as well as a few other fields: LOAD DATA LOCAL INFILE 'C:\\xampp\\htdocs\\data_files\\2018-10_statistics.csv' INTO TABLE my_log_tbl FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (UserId, CustomerId, InstitutionId, ApplicationId, FullURL, AccessTimeStamp, IPaddress); I had thought the columns should come after the table name, but that did not work. They should appear at the end. – kalinma Nov 02 '18 at 16:00

score 17 · Answer 4 · edited Mar 14 '18 at 14:31

Syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name' INTO TABLE `tbl_name`
CHARACTER SET [CHARACTER SET charset_name]
FIELDS [{FIELDS | COLUMNS}[TERMINATED BY 'string']] 
[LINES[TERMINATED BY 'string']] 
[IGNORE number {LINES | ROWS}]

See this Example:

LOAD DATA LOCAL INFILE
'E:\\wamp\\tmp\\customer.csv' INTO TABLE `customer`
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

score 10 · Answer 5 · edited Jul 22 '20 at 20:47

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
    ');
$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.

score 2 · Answer 6 · edited Oct 07 '16 at 01:13

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

score 2 · Answer 7 · answered Oct 07 '17 at 18:00

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

score 1 · Answer 8 · edited Nov 26 '18 at 07:01

1

You can try to insert like this :

LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

edited Nov 26 '18 at 07:01

Suraj Rao

28,186
10
88
94

answered Nov 26 '18 at 06:57

Hozayfa

11
3

score 0 · Answer 9 · answered Oct 06 '16 at 03:09

You can load data from a csv or text file. If you have a text file with records from a table, you can load those records within the table. For example if you have a text file, where each row is a record with the values for each column, you can load the records this way.

table.sql

id //field 1

name //field2

table.txt

1,peter

2,daniel

...

--example on windows

LOAD DATA LOCAL INFILE 'C:\\directory_example\\table.txt'
INTO TABLE Table
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

score 0 · Answer 10 · answered Aug 30 '17 at 02:37

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!!!

score 0 · Answer 11 · answered Nov 13 '19 at 17:17

0

By these days (ending 2019) I prefer to use a tool like http://www.convertcsv.com/csv-to-sql.htm I you got a lot of rows you can run partitioned blocks saving user mistakes when csv come from a final user spreadsheet.

answered Nov 13 '19 at 17:17

Jonathan Perrotta

71
1
2

MYSQL import data from csv using LOAD DATA INFILE

11 Answers11

table.sql

table.txt

Linked

Related