In this article, I will show you how to configure a CSV properly and then import the CSV file into MySql.
A client of mine recently asked me if I could upload his .CSV file into a MySQL database. He runs a sports site and wanted to have the entire NBA schedule available in MySQL. He presented me with the text file and it looked like this:
Of course, the real file was 1,200 lines long but you get the idea.
Now, when we import a .csv file into MySQL, we will need to tell it what separates each field and row. A space? Quotes? In this particular file, a space separates everything so we need to parse it out.
First, I separated the lines. I use UltraEdit for all of my quick text edits because, in my humble opinion, UltraEdit works miracles with text. So, to do this within UltraEdit, we simply do a find replace on the term “p.m”(because it is the end of each line) like this: Find: p.m., Replace with p.m.^p(which adds a new line). Fortunately, all of the games had a p.m designation. Now, our file looks like this:
Now, we clearly have an end marker for each row.
As you can see, our fields in the database will be as follows: DAY | DATE | AWAYTEAM | HOMETEAM | GAMETIME. The database also has an auto-increment field and I will get to how to deal with that in just a moment.
First, we need a delimiter for each of our fields. You can see that a space separates each field, but there are also spaces within each field which is not good. So, by simple replaces, I put a semi-colon between each field and eliminated the unneeded spaces elsewhere. I also had to substitute a team number for the team name as this is how the database was structured (the TEAM NAME field was an integer that was taken as a foreign key from another table).
After doing these searches and replaces which only took a few minutes, we come up with our .CSV file which is ready to import. It looks like this:
Now, the actual import of a .CSV file into MySQL is actually very easy in phpMyAdmin. You simply select your database, click import, choose your file, select CSV as the file type and do your import.
However, this import did not work the first time. Why? There was an auto-increment GAMEID field in the MySQL table. A lot of people ask how to deal with auto-increment fields when you import a .CSV file into a MySQL database. The answer is actually very simple: delete it and then re-create it after the import.
So, after deleting the GAMEID auto-increment field, I imported the .CSV file, which populated the table with all of the games, and then I recreated the GAMEID field which self-populated, because it’s set to auto-increment.
In the end, importing a CSV file into MySQL is actually very easy. The challenge is preparing the .CSV file for import. However, with a little bit of UltraEdit magic, this is no problem.
Next, you may want to read Convert Variable To Date to see how I converted the dates and times of these games to their proper MySQL type so that they much more usable to us!