Times come when we have to insert so much of data into MySQL tables from a raw data collection such as from spreadsheets. It is tedious to enter these data manually into the table(s). Here, I will be showing how to import such bulk data directly into tables rather than inserting it one by one (without having to code) that is how to import CSV file into MySQL tables.
What is a CSV File?
In computing, a comma-separated values (CSV) file stores tabular data (numbers and text) in plain text. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.
– Wikipedia (Retrieved on 11/07/2017)
How to Create a CSV File?
Often bulk data of the kind I mentioned above are stored in spreadsheets. In Microsoft Excel and any other spreadsheet software has the functionality to save these data as a CSV file. While saving spreadsheet file save it with .csv extension. Make sure that you prepare the CSV file to have the fields in the same order as the MySQL table fields.
How to Import CSV File into MySQL Table
Let me assume your database to be on your localhost server, say WampServer.
- Go to the phpMyAdmin interface. Log in, click on the database in which your table is and click on the table name.
- Remove the header row from the CSV (if any), so that only the data is in the file gets entered in your table.
- Click the import button at the top.
- Browse to the CSV file.
- Select the option “CSV using LOAD DATA”.
- Enter “,” in the “fields terminated by”.
- Enter the column names in the same order as they are in the database table.
- Click the go button and you are done.
You may also like to read:
