sql tools and excel

[tom kroto]

I searched forum and found this example back in 2000 where for an excel database you use back quotes see `sheet1$` and the use of the $.
But 2000 was a long time ago. When I execute I get "too few parameters"

Is my information out of date? This is excel 2007. I have .dsn.

the statement worked fine for an access sql (no back quotes used there)




sSQLStatement = "SELECT date, description " + _
"FROM `sheet1$` " + _
"WHERE description like '" + sNameSearch + "'" + _ 'note how this looks
"order by date "

Thanks

[Edwin Knoppert]

You really must know the name of the sheet and then use [].

[tom kroto]

how? where?

I called up excel and only see checkbook7 on top. That is name of spreadsheet. Where do I get the "table" name? all I see is Sheet1, etc at the bottom

[Eric Pearson]

From the SQL Tools Help File (Appendix A):

Special Microsoft Excel Characters

Microsoft Excel treats each "sheet" in a spreadsheet file as a separate table. By default, they are named SHEET1$, SHEET2$, and so on. This creates a problem because the dollar sign ($) is an ODBC reserved character. Most versions of Excel also require an unusual character, the left-apostrophe or "back-tick" character, to delimit table names which contain dollar signs. So a very simple SELECT statement for an Excel spreadsheet might look like this:

SELECT * FROM `SHEET1$`

Note the use of the left-apostrophe, not the normal right-apostrophe, around the table name. Oddly, you must use the normal apostrophe for all other quoted strings, like this:

SELECT * FROM `SHEET1$` WHERE MYCOLUMN = 'HELLO'

-- Eric Pearson, Perfect Sync Software

[Michael Mattias]

The square brackets should work, too. I just used "raw" ODBC and this worked fine:

select count(*) from [dbo_ship_carton$]

When the data source was

08-06-2009                    ODBC DRIVER/DBMS REPORT                    11:14:53

Connection String Attributes:
DSN=BPI_PORTAL_XLS
DBQ=D:\Clients\Cloyes Gear\_System Processing\ASN Production\Portal\BPITABLES.XLS
DefaultDir=D:\Clients\Cloyes Gear\_System Processing\ASN Production\Portal
DriverId=790
FIL=excel 8.0
MaxBufferSize=2048
PageTimeout=5


Data Source Name     :BPI_PORTAL_XLS
Database Name        :D:\Clients\Cloyes Gear\_System Processing\ASN Production\Portal
DBMS Name            :EXCEL
DBMS Version         :08.00.0000
Driver Name          :odbcjt32.dll
Driver ODBC Ver      :03.51
Environment ODBC Ver : 3(ODBC 3)

Supported Capabilities
Transactions (DML)   : N
CREATE TABLE         : Y
DROP   TABLE         : Y
ALTER  TABLE         : N
CREATE INDEX         : Y
DROP   INDEX         : Y
Nullable Columns     : N
Max Table  Name Size :  64
Max Column Name Size :  64
Max Concurrent Stmt  :  0 (Unable to determine)
                                   Datatypes Supported

 DATA_TYPE     SQL Type     DBMS Literal     Column Size   MinScale   MaxScale  CreateParams
 ---------     --------     ------------     -----------   ---------  --------   ------------
      -7            -7      LOGICAL                   1        0             0      
       2             2      CURRENCY                 19        4             4      
       8             8      NUMBER                   53        0             0      
      12            12      VARCHAR                 255        0             0      
      93             9      DATETIME                 19        0             0      

*** END OF REPORT ***

FWIW, SQLTables and SQLColumns work, too... that is, when I connect to this spreadsheet file, it gives me a list of "table names" which are the individual worksheet names.

Since SQL Tools has a simpler way (simpler than 'raw' ODBC API calls, that is) to do lots of things, you should be able to get a list of the 'tables' in your spreadsheet file using some SQL Tools' native function. If table names end in "$" you can bracket 'em. But I'd think bracketing would 'always' work, too.



MCM

[José Roca]

Yes, brackets work. An additional problem is that, as Excel uses localization, Sheet1$ is not valid for non-English speaking countries. For example, you have to use Hoja1$ with the Spanish version.

There are some examples in my forum (using ADO and direct interface calls):

http://www.jose.it-berater.org/smfforum/index.php?topic=2601.0

[Michael Mattias]

Call me an optimist, Jose, but seems to me if one is using an Excel spread sheet as part of a larger application, and someone bothered to make "row one" into column headers with meaningful names, the default sheet/table names should also have been changed to something meaningful - a long time ago.

MCM

[Edwin Knoppert]

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Edwin Knoppert\Mijn documenten\BIBLIO.xls;Extended Properties=Excel 8.0;Persist Security Info=False

Excel 8.0 for xls or Excel 12.0 for xlsx is important.

[tom kroto]

ok ok

Thank you for help. I recommend that anyone starting to use sql tools run sql-inf1 sql-inf2 against the file they have chosen.

When I did this for my excel database I saw that I had several misconceptions about the database. For example the headings for the excel table were in row 3 so my field names were wrong.

This coupled with the information given solved the problem.

Thank you.

[Eric Pearson]

Glad you got it working, Tom. Excel, being a free-form spreadsheet, can act a bit different from other databases.

> sql-inf1 sql-inf2

Background for lurkers: those are SQL Tools sample programs that analyze a database and produce lists of table names, column names, column data types, etc.

-- Eric

[Michael Mattias]

those are SQL Tools sample programs that analyze a database and produce lists of table names, column names, column data types, etc.

That's even better than 'naked function descriptions presented in some logical order and/or grouping.'