This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration on how to export or import CSV data using MySQL INFILE and OUTFILE.
The CSV files are most famous for the basic data migration activities.
Database developer does not need to write or create any particular database link between two servers.
They can easily import data from CSV and export that CSV data into another server and also CSV file does not have any data length limitation like XLS file.
User can store millions of records into CSV file.
This technique is preferred only for temporary purposes, and if it continues the process, the user has to write some automatic process like database server replication.
MySQL provides a different way to manage, data import and export in the CSV format.
You can also perform this exercise using MySQL workbench Import/Export wizard.
Very soon, I will prepare and share a video help for this workbench related solution.
Let me demonstrate it:
First, I created a table and import data from CSV file.
I already created a CSV file with sample data. Make sure that, your table column and CSV column would be same.
You can create this CSV file using a comma delimited save as option of Microsoft excel.
Create a sample table:
|
1 2 3 4 5 |
CREATE TABLE TestCSV ( Rno INTEGER ,Name VARCHAR(50) ); |
Now COPY data from the CSV file into this table:
|
1 2 3 4 5 6 |
LOAD DATA INFILE 'E:/dbrnd.com/TestCSV.csv' INTO TABLE TestCSV FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; |
You should ignore the first row because of CSV headers.
Check your sample table:
|
1 |
SELECT *from TestCSV |
You can also import particular column from CSV file to MySQL Table:
|
1 2 3 4 |
LOAD DATA LOCAL INFILE 'E:/dbrnd.com/TestCSV.csv' INTO TABLE TestCSV FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS (@col1,@col2) set name=@col2; |
As you can see in the above script, I used query variable and @col1 stores all Rno data, and this is working as a dummy import for column Rno.
I set values for Name column by assigning @col2, so this query only import second column data, and the first column would be NULL.
Now, script for export MySQL table data into CSV File:
|
1 2 3 4 5 6 7 8 |
SELECT IFNULL(Rno,'N/A') ,Name FROM TestCSV INTO OUTFILE 'E:/dbrnd.com/TestCSV2.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; |
After execution of the above query, please check your newly created CSV File.
Note: This query will create a new CSV file at the specified path.
