Excel Import

[garybarnes]

Once again I am trying to import an excel table into my MySQL database, and once again I am having to look to other software to do it.

I have NEVER been able to get this to work! Why can't we have a simple Excel import function? This must be one of THE most common requirements. It's not good enough to say, as mentioned elsewhere in this forum that the ODBC driver is 'buggy'. Can anyone please tell me what the SQL statement to import all columns and records from an Excel table is – [Sheet$1] or `Sheet$1` doesn't seem to work (as recommended elsewhere).

[Graycode]

Try using the “Define Name” feature of Excel to define a range of data. The name(s) you define will be treated as the SQL table name(s).

Here's an ASP example showing what to do in Excel:

http://members.tripod.com/~housten/exceldata.html

It defines a named range of cells called “ClassTable”, and then that data can be accessed via ODBC with “SELECT * FROM ClassTable”

[peterlaursen]

I never managed to get the ODBC driver for Excel to work.

And never heard about anyone who did. 

[garybarnes]

I'm not sure if this is a problem with SQLyog or the ODBC driver.

Either way, I use program called Navicat purely for Excel imports which is quite expensive (although a demo is available) but works like a dream.

[garybarnes]

Graycode wrote on Mar 30 2007, 03:57 PM:

Try using the “Define Name” feature of Excel to define a range of data. The name(s) you define will be treated as the SQL table name(s).

Here's an ASP example showing what to do in Excel:

http://members.tripod.com/~housten/exceldata.html

It defines a named range of cells called “ClassTable”, and then that data can be accessed via ODBC with “SELECT * FROM ClassTable”

That's it! Thanks very much for the tip!

I found that you simply can't select the entire sheet otherwise it tries to create columns up to ZZ, which will cause a failure, but if you make sure you select just the data region on the worksheet it will work. I must say I find the whole process of having to create a file data source a bit tedious, and I find that having attempted the import once the file remains locked until SQLyog is exited. As I mentioned elsewhere in this thread, the process used by the Navicat program is much more friendly.

[Graycode]

garybarnes wrote on Mar 31 2007, 08:00 AM:

That's it! Thanks very much for the tip!

You're welcome, glad you got it working. 🙂

[Author]

Posts