Import data in MySQL from a CSV file using LOAD DATA INFILE

Question

I am importing some data of 20,000 rows from a CSV file into MySQL.

Columns in the CSV file are in a different order than MySQL tables' columns. How can I 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.

What is the 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
Do *any* of the existing answers actually answer the question *"Columns in the CSV file are in a different order than MySQL tables' columns. How can I automatically assign columns corresponding to MySQL table columns?"*? They all instead *seem* to answer ***the more general question*** in the title of the question. Alternatively, is the question illogical or unclear? — Peter Mortensen, Apr 11 '22 at 15:06

score 209 · Answer 1 · edited Apr 11 '22 at 14:22

209

You can use the LOAD DATA INFILE command to import a CSV file into a table.

Check the 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 holds 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 documentation. Beware that such an overwrite does not solve the security issue, but rather is just an acknowledgment that you are aware and willing to take the risk.

edited Apr 11 '22 at 14:22

Peter Mortensen

30,030
21
100
124

answered Jan 03 '13 at 05:52

Saharsh Shah

27,975
8
43
82

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
38

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
3

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
1

Adding to the caution note by @ChuckCottrill, some Windows programs, such as WordPad, may use `\r` as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'. – MAbraham1 Sep 03 '21 at 13:27

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

59

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,510
7
21
32

answered Jan 03 '13 at 05:14

ckim

954
6
7

2

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

score 54 · Answer 3 · edited Apr 11 '22 at 14:38

Before importing the file, you 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 the following table:

Create the table using the 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 the following attached image:

If everything is fine, please execute the following query to load data from the CSV file:

Note: Please add the 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 been done, you have exported data from the CSV file to the table successfully.

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

score 17 · Answer 4 · edited Apr 11 '22 at 14:38

17

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;

edited Apr 11 '22 at 14:38

Peter Mortensen

30,030
21
100
124

answered Oct 20 '16 at 13:14

Md. Nashir Uddin

662
5
17

On Windows, presumably? What version of MySQL was it tested with? What version of Windows? – Peter Mortensen Apr 11 '22 at 14:38

score 12 · Answer 5 · edited Apr 11 '22 at 14:44

12

Insert bulk more than 7,000,000 records in 1 minute in the database (superfast query with calculation):

    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,
         ID = NULL

RRP and RRP_bl are not in the CSV file, but we are calculating those and insert them after that.

edited Apr 11 '22 at 14:44

Peter Mortensen

30,030
21
100
124

answered Feb 17 '17 at 06:34

krunal panchal

407
4
8

Calculations on the fly were not called for in the question. – Peter Mortensen Apr 11 '22 at 14:46
we give extra , not wrong knowledge. – krunal panchal May 19 '22 at 12:24

score 2 · Answer 6 · edited Apr 11 '22 at 14:41

2

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

edited Apr 11 '22 at 14:41

Peter Mortensen

30,030
21
100
124

answered Oct 06 '16 at 23:13

Kikoz

146
4

What Windows shell? [CMD](https://en.wikipedia.org/wiki/Cmd.exe)? [PowerShell](https://en.wikipedia.org/wiki/PowerShell) (first introduced in 2006)? – Peter Mortensen Apr 11 '22 at 14:36

score 2 · Answer 7 · edited Apr 11 '22 at 15:00

Let’s 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 the "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;

This may work if you are doing this on localhost.

score 1 · Answer 8 · edited Apr 11 '22 at 14:46

1

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.

File table.sql

id //field 1

name //field2

File 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';

edited Apr 11 '22 at 14:46

Peter Mortensen

30,030
21
100
124

answered Oct 06 '16 at 03:09

Jorge T

119
1
2
8

Yes, but the question was about CSV - *"Import data in MySQL from a CSV file using LOAD DATA INFILE"* – Peter Mortensen Apr 11 '22 at 14:34

score 1 · Answer 9 · edited Apr 11 '22 at 14:50

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 Apr 11 '22 at 14:50

Peter Mortensen

30,030
21
100
124

answered Nov 26 '18 at 06:57

Hozayfa

11
3

The last part, "(field1,field2,field3)", is different from most of the other answers. An explanation would be in order. E.g., what is the idea/gist? From [the Help Center](https://stackoverflow.com/help/promotion): *"...always explain why the solution you're presenting is appropriate and how it works"*. Please respond by [editing (changing) your answer](https://stackoverflow.com/posts/53476068/edit), not here in comments (***without*** "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Peter Mortensen Apr 11 '22 at 15:00

score 1 · Answer 10 · edited Apr 11 '22 at 15:04

1

By these days (ending 2019) I prefer to use a tool like Convert CSV to SQL.

If you got a lot of rows, you can run partitioned blocks saving user mistakes when the CSV comes from a final user spreadsheet.

edited Apr 11 '22 at 15:04

Peter Mortensen

30,030
21
100
124

answered Nov 13 '19 at 17:17

Jonathan Perrotta

91
1
2

There's a CSV Lint plug-in for Notepad++ that can do the same thing https://github.com/BdR76/CSVLint – BdR Oct 25 '21 at 19:22

Abhijit Das · Answer 11 · 2022-04-30T15:11:24.217

Edit my.ini file and add the following secure-file-priv = "" under [mysqld] section. Also if already any variable set for this then remove that.
Then restart the MySQL service and execute the following command to check whether the value has been successfully changed or not:

SHOW VARIABLES LIKE "secure_file_priv";

Then you can execute the following query in the MySQL installed system and import the data,

LOAD DATA INFILE "csv file location" INTO TABLE tablename
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(column1,column2,.....);

Note: (column name separated by comma as per csv file)

To add any CSV file from any other remote system you can also do the following after the above step:

show global variables like 'local_infile';
-- Note: if the value is OFF then execute the next command below
set global local_infile=true; 
-- check back again if the value has been set to ON or not

Execute the following command to import data from CSV file from any other system,

LOAD DATA LOCAL INFILE "csv file location" INTO TABLE tablename
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(column1,column2,.....);
-- column name separated by comma as per csv file

It would be useful if you wrapped your code with the code tags. — Jairus, Apr 29 '22 at 14:06
Thanks @Jairus for the suggestion will further use the code tags. — Abhijit Das, Apr 30 '22 at 15:14

score 0 · Answer 12 · edited Apr 11 '22 at 14:57

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 WAMP Server 3.0.6 64 bit:

I edited my.ini file from C:\wamp64\bin\mysql\mysql5.7.14.
Delete entry secure_file_priv c:\wamp64\tmp\ (or whatever directory you have here).
I stopped everything—with exit wamp, etc.—and restarted everything; then put my CVS file on C:\wamp64\bin\mysql\mysql5.7.14\data\u242349266_recur (the last directory being my database name)

I executed

 LOAD DATA INFILE 'myfile.csv'

 INTO TABLE alumnos

 FIELDS TERMINATED BY ','

 ENCLOSED BY '"'

 LINES TERMINATED BY '\r\n'

 IGNORE 1 LINES

... and voilà!!!

Import data in MySQL from a CSV file using LOAD DATA INFILE

12 Answers12

File table.sql

File table.txt

Example on Windows

Linked

Related