Import CSV Files into a MySQL Database
Web Developer have to face a large amount of obstacles in their every day tasks in order to complete them. There are some thing that are easier to complete than other but a little bit of help can make a big difference when it comes down to finishing on time. Importing a CSV file into a MySQL database is often needed when creating projects for new clients. This is because they will use a variety of desktop software and they will probably tell you, “Here you go” when it comes to geting the data. To your advantage most of this applications have some way of exporting their data into Comma Separated Value files or CSV. Basically it’s a file that has a layout similar to a spreadsheet but instead of boxes for columns you have a coma separating the fields. In the same way in lieu of rows you will have a new line in the document.
The first step is to get that file from the customer or from it’s source and upload it into your server. After this you will need to access your server’s command line via an application like Putty and start your MySQL server.
$ mysql -h localhost -u root -ppassword
You will need to create your database with the fields that you will be needing. once your database has been set up you can import the data from your CSV file into your MySQL database. Here is the command that you will require in order to import the information into your database table.
load data local infile 'myphpdeveloper.com/httpdocs/_assets/cities.csv' into table phpdev.gc_cities fields terminated by ',' enclosed by '"' lines terminated by '\n' (zipCode, city, state, county, areaCode);
In the first line of the command is where the location to your Comma Separated Values or CSV file is located at. The second line is the table that you want the information to go into. The third line is telling your MySQL server that the values are separated by comas. In the same way the fourth line is describing how the values are enclosed by single quotes. The fifth line is telling your server that your row ends when the line end. Finally the sixth lane is the fields in your database table. They need to be aligned with the order of your file for example if your files is ID, Price, Description then your locations will be (id, price, description) and so on.
Often there are times that the above command will not work. This is due to the fact that this option is considered to be a big security risk. Some hosting companies like Media Temple are disabling this by default so you will need to make a couple of small changes in order for it to work.
You will have to locate your MySQL configuration file which is usually located at ‘etc/my.cnf’ and comment out the line that prevents the import. You will have to do this while being a root user in your server, if not it will only allow you to read the file but not to reconfigure it. The line that you are looking for is:
local-infile=0
You will have to comment it by adding a “#’ sign at the beginning of the line like:
#local-infile=0
You can learn more about this on the MySQL Documents. After you have completed the change you will need to restart the MySQL server and then do the process once more. The command to restart the server is:
service mysql restart
Hope that this helps any web developers out there. Please feel free to leave any comments, questions or suggestions down below and we will be happy to assist you.
