Excel Database to MySQL

[Fall]

Hiya,

We have an excel database I'd like to cross over into MySQL, I see plenty of PC apps that can do it, but are there any Mac ones?

There's some Mac application at sqlboss that is MacOSX native, but the site doesn't give a real good description on what the hell it does!

Anyone know if it can be done, and if so, how?

Thanks!

 

[Rower_CPU]

It's actually really easy.

1) Export the Excel file as a CSV (text-file) with comma breaks.
2) Open the text file in something like BBEdit and append commas to the beginning and end of every line.
3) Via the command line, or a utility like phpMyAdmin to import the text file, using the commas as field delimiters into a pre-existing table.

It's fun.

 

[eddie22487]

how exactly do i do that?

I'm trying to make a store locator for my website. My boss sent me a .xml spreadsheet of over 1,000 stores that carry our products and just told me to make a store locator with it. Is this the appropriate way to go about it?

I'm on a Macbook Pro.

edit: wow, sorry about bringing such an old topic-- I just was googling for answers :\

 

[Reptar50]

sorry to reply to a really old topic but can anyone point me in the right direction with step by step directions on how to do this with the latest versions of excel mysql and phpmyadamin or whatever?

 

[MisterMe]

sorry to reply to a really old topic but can anyone point me in the right direction with step by step directions on how to do this with the latest versions of excel mysql and phpmyadamin or whatever?

Excel is a spreadsheet application, not a database management system. As Rower_CPU made abundantly clear in Post No. 2 of this thread, save the spreadsheet as a CSV file. From there, you will need other applications.

 

[jackrv]

It's actually really easy.

1) Export the Excel file as a CSV (text-file) with comma breaks.
2) Open the text file in something like BBEdit and append commas to the beginning and end of every line.
3) Via the command line, or a utility like phpMyAdmin to import the text file, using the commas as field delimiters into a pre-existing table.

It's fun.

As Rower_CPU said, this. I will go into a little more detail.

I do this all the time, as I have to export a list of MS Exchange contacts to Excel, then save as a CSV and import into a MySQL database for a PHP program I wrote to do mass mailings.

You will need PhpMyAdmin for this. You can google it for instructions to install it, or it also comes with many bundles, like XAMPP (available for Mac).

If you have the file opened in Excel, save the file as a .CSV. You don't need to add more commas to it (At least from Excel in Windows). In PhpMyAdmin, select the database and table you want to add it to (create it with the necessary columns if you need to), and click the Import tab. Browse to the .CSV file, and select the "LOAD DATA" option (I've found if you dont select this, sometimes the first row doesn't get imported from a CSV). Click to import, and if your MySQL DB columns match the fields in the CSV, you should be good to go.

Alternatively, if you are using MySQL and understand PHP, you could write a script to parse the XML file and use INSERT to populate the database. This would also let you add additions in the future without needing PhpMyAdmin.