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!
3 Comments on “Import CSV File Into MySQL”
Pingback: PHP: Convert Variable To Date | Richard Cummings
I am wondering whether or not this will work for me. I have about 500,000 records in a csv file that I need to upload into posts. The csv file already has the metas for the custom fields and the custom fields are already set to display within the posts. I have been using CSV importer plugin for the last few weeks, however, it is slow for a big job like I have. I can currently only import/upload about 400 records into posts via the WP admin panel. The method that you just described….will it convert my records into posts? Please let me know, if you have the answer, I’m kind of stumped. I know there must be a faster way than the way I’ve been doing it. Any help will be much appreciated.
RottenElf,
WP is just displaying information from your MySql database so if you do the import correctly and the items are entered correctly in the imported data (i.e. posts are set to display), you should be fine. Bear in mind though, it can be dangerous to write directly to the WordPress database. People often try this and break their WordPress sites so proceed with caution.
Good Luck
Richard