How to convert Microsoft Excel sheet to a MySQL database for free.

After searching for an hour I didn’t find anything too promising without fronting some money; which probably means there is an easier free way than my method I just didn’t find it. So here it is.

First you’ll need to convert your Excel sheet to a CSV text file. Stands for comma-separate values. From there you will need to modify the sheet to import to MySQL via phpmyadmin easier. As an example you’ll have a text like this:

Tommy Gun,12mm,$500,600 rounds,60 per clip

Glock,9mm,$200,400 rounds,8 per clip

Revolver,15mm,$900,100 rounds,6 per

Shot Gun,12ga,$100,50 rounds, 2 per

Open your new CSV file with notepad (Windows) or TextEdit (Mac) and replace (comma):

,

with (single quote comma single quote):

‘,’

And replace (new line):

with (single quote new line single quote). Tip you can’t enter an return in the Find and Replace fields but you can copy and paste it there:

For some reason phpmyadmin won’t recognize an exported CSV without these modifications. You should have something that looks similar to this:

‘Tommy Gun’,’12mm’,’\$500′,’600 rounds’,’60 per clip’

‘Glock’,’9mm’,’\$200′,’400 rounds’,’8 per clip’

‘Revolver’,’15mm’,’\$900′,’100 rounds,’6 per’

‘Shot Gun’,’12ga’,’\$100′,’50 rounds,’2 per’

Now go into phpmyadmin and create a new table with 5 fields and create using the appropriate type. Now you are ready to Import the CSV file into the MySQL database. Fields terminated by are terminated by commas and fields enclosed by single quotes. It should successfully import into your database assuming you aren’t using any crazy characters which means only periods, dashes, and letters. Semi-colons, colons, dollar signs, number signs, etc will throw it off. If you want to use those characters place a forward slash just before the character so $ would need to be \$ in the CSV before importing. Again a find a replace is the easiest way to do this.

P.S. If you are importing dates make sure you format them before saving the Excel sheet as a CSV file. Select the dates, go to formatting, go to special, enter “YYYY-MM-DD” as the format as that is what MySQL uses.

This entry was posted in Technology and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *