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?
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.
IGNORE 1 LINES exactly does here? Thanks in advance.
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;
Before importing the file, you need to prepare the following:
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.
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;
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.
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
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.
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.
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
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.
id //field 1
name //field2
1,peter
2,daniel
...
LOAD DATA LOCAL INFILE 'C:\\directory_example\\table.txt'
INTO TABLE Table
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';
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);
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à!!!
create A table in Mysql
CREATE TABLE Address_Book (
ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(35) NOT NULL,
Email VARCHAR(35),
Mobile INT(12),
Address VARCHAR(45)
);
than create a csv file at location D:\\csvfiles\\address.csv
paste the below content into the csv file
ID, Name, Email, Mobile, Address
"1","Tushar Bhadwaj","[email protected]","4848488","123 Park Street"
"2","Peter","[email protected]","4848485","321 Fake Avenue"
"3","Michael Clark","[email protected]","4848487","321 Park Avenue"
"4","James Franklin","[email protected]","4848489","321 Fake Avenue"
And than execute the below command for import the csv file content---
LOAD DATA LOCAL INFILE 'D:\\csvfiles\\address.csv'
INTO TABLE Address_Book
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;
All the detail already provided in the page .In case you can't understand you can click on below link .The content is same as on page.
LOAD DATA INFILE 'abc.csv' INTO TABLE abc fields terminated by '|' (@filecol1,@filecol2,@filecol3)
SET filecol =@filecol1, filecol2 =@filecol2, filecol3 =@filecol3
Here you would require to check your CSV how the fields are being terminated I worked on certain files. The Files may use , | ; \t etc
To add more context to the accepted answer, I would like to give an example.
Here’s a sample CSV:
Your MySQL configuration may dictate the success (or failure) of the LOAD INFILE. With mine, loading is disabled for both the client and server. So, initially, I get this error:
Loading local data is disabled; this must be enabled on both the client and server sides.
So, you might need to check the secure_file_priv server variable and set this in the client:
SET GLOBAL local_infile = 1;The location of the CSV file also matters depending on your client. Since I use dbForge Studio for MySQL, it looks for the path on where dbForge is installed (C:\Program Files\Devart\dbForge Edge\dbForge Studio for MySQL). Or, if there’s a path in secure_file_priv, put it there.
Once the requirements are in place, the LOAD INFILE should work. However, you may need to adjust based on runtime errors thrown to you.
Below shows the target table and the exact LOAD INFILE syntax I used:
Note that the customer table has more columns than the CSV file. That’s why at the end of the LOAD INFILE, the customer_name, email, and address columns are explicitly specified. The column names are based on the target table but the arrangement is based on the columns from the CSV file (See above images).
In the end, I got this:
After using LOAD INFILE, I used a simple SELECT to the customer table to see if the 10 rows are there.
Using LOAD INFILE is fine to import a CSV to MySQL. But dealing with syntax errors may be annoying. So, using a GUI is my preferred method. Of course, it’s not without problems too but connecting, loading, and column mappings, are easier understood. I used SSIS and Skyvia for some time. Others may have different preferences but I have experience on these so I mentioned them here.
I hope it helps.
