Odbc excel syntax drivers#
Odbc excel syntax code#

Excel data is not intended for simultaneous access by multiple connections, so don't go trying serve up your spreadsheets in an OLTP system.

Only the final cell data values are received via the proxy table, not formulas.

Odbc excel syntax drivers#

The Microsoft ODBC drivers for Excel 20 seem to impose limits on string lengths: 64 bytes if you're not lucky, 255 bytes if you are.

DOUBLE and NVARCHAR ( 255 ) seem to be good choices.

Odbc excel syntax code#

If you really want spaces, you'll have to code an explicit column name list in the CREATE EXISTING TABLE, with 'quoted column names' and data types. Expected 8.' it may be because you have included spaces in the row 1 column names. If you see an error like 'Too few parameters.

The names in row 1 of the spreadsheet are used to generate (and match) column names in the proxy table.

You might get away with having the workbook open in Excel at the same time you're running proxy table queries.

SQLCODE=-134, ODBC 3 State='HY000'Workarounds are welcome, this blog invites comments! These changes are no big deal, since proxy tables work so well, except for the fact that any behavior change is a pain in someone's patootie, and the INPUT and OUTPUT commands specify files that are relative to the client whereas (see above) proxy table DSNs are relative to the server.Īnd before anyone says 'LOAD and UNLOAD now have CLIENT FILE clauses!', check out how well LOAD and UNLOAD work with proxy tables: Feature 'unload from non-SQL Anywhere tables' not implemented Plus, ISQL INPUT cannot read Excel 2007 *.xlsx files in SQL Anywhere 10: 'The file may be in an unsupported format, or it could be corrupt.'

In related news, the ISQL INPUT and OUTPUT statements no longer support the Excel format in SQL Anywhere 11.

if the server is started as a service, the DSN must be a 'System DSN', not a 'User DSN'. If it's a network server on some other computer, that's where the DSN is located, and (unless you're using a mapped drive or UNC file specifications) that's where the Excel file is located as well.

The ODBC DSN must be set up on the same computer that's running the SQL Anywhere engine.

Here's how the ODBC Administrator was used to create the 'Excel 2007' DSN for this article:.

The file named in the 'Select Workbook' doesn't even have to remain in existence. You do have to use 'Select Workbook' to point the DSN at a specific file, but after that the file and sheet names are specified in your SQL code.

You only have to set up one ODBC DSN for everything, not one DSN for each sheet or workbook.

If you do need it, and you don't code it, you will see this bogus message bubbling up from the Excel driver: The remote table 'EXCEL.C:/temp/1' could not be foundĪside: Don't you try to code the AT clause using periods '.' like in that error message, you need to use semicolons ' ' to avoid ambiguity with the filespec.

You may or may not need to code the 'magic dollar sign' after the sheet name in the AT clause.

The syntax for the CREATE EXISTING TABLE.

it helps to know that when you're searching the docs.

The official name of the 'proxy table' feature is 'Remote Data Access'.

Here is what the table definition looks like in the SQL Anywhere catalog: CREATE EXISTING TABLE DBA.proxy_browsers - proxyĪT 'EXCEL C:/temp/browsers.xlsx Sheet1$' Here's what the data looks like: SELECT * You can read about setting up proxy tables for Excel here: ORDER BY hit_count DESC Here is what the output looks like Firefox is winning for this particular website (this blog, actually): Hit_count / total_hits * 100.0 AS percentĬROSS JOIN ( SELECT SUM ( hits ) AS total_hits SUM ( proxy_browsers.hits ) AS hit_count, SELECT proxy_browsers.browser AS brand_name, Hit counts are summarized by browser brand name, getting rid of the version number differences: CREATE SERVER EXCELĪT 'EXCEL C:/temp/browsers.xlsx Sheet1$'

Here's the code to answer the question, 'Who's winning the browser wars, IE or Firefox?'. Here is an Excel spreadsheet containing website hit counts by browser (MSIE 7, Firefox 3.03, etc): Q: How do I run SQL SELECT queries against data stored in Excel spreadsheets? (This is the opposite direction from importing data into a spreadsheet from a database e.g., via Excel - Data - From Other Sources - From Microsoft Query, etc.) A: You can use SQL Anywhere's proxy table feature to treat an Excel spreadsheet like a relational table, thus bringing the power of SQL SELECT to bear on spreadsheet data.įirst, a simple example, then some tips and suggestions.