Excel to DB

[tomraffe]

Tweet

I wondered if there was a way to export an Excel spreadsheet directly into a MySQL DB?

I would rather export it automatically rather than update it manually via my CMS.

[vanilla]

I'm pretty sure if you get the Excel spreadsheet and convert it to CSV format you can then import one of the csv columns into an SQL column within a table.

[tomraffe]

I tried that but didnt work.

There is a nice wizard within Navicat which im trying out now Navicat Oracle, MySQL Admin Tool - the World's Best Oracle Manager, MySQL GUI & PostgreSQL GUI for Windows, Linux & Mac OS X - Download Now! Support Access to MySQL, Excel to MySQL, MySQL editor, MySQL administrator, MySQL frontend, MySQL manager, ph

[-kudos-]

There are loads of ways of doing this. I havent ever done it myself but can remember researching for a project I have yet to start and it is possible.

I just done a quick Google and there are a few pre-written php classes that you could use.

[accelerator]

I wondered if there was a way to export an Excel spreadsheet directly into a MySQL DB?

I would rather export it automatically rather than update it manually via my CMS.

I know that MySQL management software navicat.com can import a .xls file into a MySQL database.

However, where you meaning you wanted to do this totally automatically through .php code?

Rgds

[RezaBadel]

Hi tomraffe,

buy.at have created a feed processing guide document, which can be found here:

http://files.perfiliate.com/solution...GuideFeb09.pdf

The example is for an XML feed, however can be easily used for a CSV/PSV/TSV Excel file format.

Here are some examples of getting a CSV file and then using the mysql query Insert or Update to get the data into your database:

PHP: fgetcsv - Manual
PHP MySQL Insert Into

If you need any help with this please contact myself Reza.Badel@platform-a.com or our Data Quality Manager Simon.Quick@platform-a.com.

Enjoy your weekend

Reza
Solutions Engineer
buy.at

[tomraffe]

Thanks all, Navicat worked well.

[chrisbrocklesby]

Also I have used in the pass Excel to MySql converter programs I have found from google

[Symbyo_SEM]

Before you start - backup your database in your Admin,Tools,Database Backup or from phpmyadmin.


In phpmyadmin choose your table
click BROWSE
scroll to the bottom of the page and hit EXPORT
choose CSV for MS Excel
choose from Excel edition: Windows (if on a PC)
click zipped
click GO and save you file to your PC, then unzip and alter in Excel.

When finished altering - Save as csv (comma delimited)

IMPORT CSV
To IMPORT your list of products (or whatever) in phpmyadmin



Go to phpmyadmin and choose your products table
click BROWSE
click SQL tab

Under the 'Run SQL query window' you will see a box marked

Or Location of the textfile:
Location of the textfile:


Underneath you will see some text that says...
"Insert data from a textfile into table"

Click it, you will see this....

Location of the textfile
CHOOSE YOUR CSV FILE YOU SAVED FROM EXCEL AS COMMA DELIMITED

Fields terminated by
CHANGE THIS FIELD TERMINATOR TO A COMMA , HENCE THE CSV (Comma Delimited File)

...DATA LOCAL
Keep this to DATA LOCAL

SUBMIT