MySQL: Import and Export CSV data with Headers

In this post, I demonstrate to export or import CSV data using MySQL INFILE and OUTFILE.

The CSV files are most popular for the basic data migration activities. Database developer does not need to write or create any special database link between two servers.They can easily import data into CSV and export that CSV data into another server and also CSV file does not have any data length limitation like any other XLS file. User can store millions of records into CSV file.

Generally, this technique is preferred only for temporary purposes and if it is continued process, then user has to write some automated process like database server replication.

Recently I have also created CSV data demo for PostgreSQL.

MySQL provides a different way to manage, data import and export to CSV format.
You can also perform this exercise using MySQL workbench Import/Export wizard.
Very soon I will prepare and share one video help for this workbench related solution.

In this post, I am sharing script to Import and Export CSV data.

Let me demonstrate this.

First, I create one table and import data from CSV file. I have already created one CSV file with sample data. Make sure that, your table column and CSV column would be same.
You can create this CSV file using comma delimited save as option of Microsoft excel.


Create one sample table:

Now COPY data from CSV file into this table:

You should ignore 1 rows because of CSV headers.
Check your sample table:

MySQL CSV Table Data

You can also import particular column from CSV file to MySQL Table:

As you can see in above script that, I have used query variable and @col1 stores all Rno data and this is working as a dummy import for column Rno.
I have set values for Name column by assigning @col2 so this query only import second column data and first column would be NULL.
Now script to export MySQL table data into CSV File:

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.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of