MYSQL import data from csv using LOAD DATA INFILE
Solution 1
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.
Solution 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;
Solution 3
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.
Solution 4
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;
Solution 5
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.
MANJEET
I like to keep exploring and learning new technologies on the internet like Javascript, Node.js, React.js; highly motivated by making the web faster by applying best practices for improving the performance of web applications.
Updated on July 14, 2021Comments
-
MANJEET almost 3 years
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.
-
Umar Abbas over 8 yearsHow can we validate if CSV file have proper datatype for each column because by default it ignores invalid datatype.
-
Koushik Das almost 8 yearsMay I know what
IGNORE 1 LINES
exactly does here? Thanks in advance. -
Saharsh Shah almost 8 years@KoushikDas It will ignore the first line which defines column names
-
ChuckCottrill almost 8 yearscaution: the LINES TERMINATED BY '\r\n' clause works for windows files, but linux files should have '\n' only.
-
Ryan almost 6 yearsImportant to remember to use forward slashes: stackoverflow.com/a/42878067/470749
-
ketankk almost 6 yearsLOAD DATA LOCAL INFILE...Without LOCAL it was access denied.
-
Kieran Quinn about 5 yearsjust 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.
-
Kymo Wang about 3 years@ChuckCottrill It's better to define LINES TERMINATOR when export data.
-
MAbraham1 over 2 yearsAdding 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'. -
BdR over 2 yearsThere's a CSV Lint plug-in for Notepad++ that can do the same thing github.com/BdR76/CSVLint
-
Peter Mortensen about 2 yearsYes, but the question was about CSV - "Import data in MySQL from a CSV file using LOAD DATA INFILE"
-
Peter Mortensen about 2 yearsWhat Windows shell? CMD? PowerShell (first introduced in 2006)?
-
Peter Mortensen about 2 yearsOn Windows, presumably? What version of MySQL was it tested with? What version of Windows?
-
Peter Mortensen about 2 yearsCalculations on the fly were not called for in the question.
-
Peter Mortensen about 2 yearsThe 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: "...always explain why the solution you're presenting is appropriate and how it works". Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).
-
krunal panchal almost 2 yearswe give extra , not wrong knowledge.