Excel Import

[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