No announcement yet.

sql tools and excel

  • Filter
  • Time
  • Show
Clear All
new posts

  • sql tools and excel

    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 "


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


    • #3
      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


      • #4
        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:
        -- Eric Pearson, Perfect Sync Software
        "Not my circus, not my monkeys."


        • #5
          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:
          DBQ=D:\Clients\Cloyes Gear\_System Processing\ASN Production\Portal\BPITABLES.XLS
          DefaultDir=D:\Clients\Cloyes Gear\_System Processing\ASN Production\Portal
          FIL=excel 8.0
          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.

          Last edited by Michael Mattias; 6 Aug 2009, 11:22 AM.
          Michael Mattias
          Tal Systems (retired)
          Port Washington WI USA
          [email protected]


          • #6
            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):


            • #7
              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.

              Michael Mattias
              Tal Systems (retired)
              Port Washington WI USA
              [email protected]


              • #8
                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.


                • #9
                  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.


                  • #10
                    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
                    "Not my circus, not my monkeys."


                    • #11
                      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.'
                      Michael Mattias
                      Tal Systems (retired)
                      Port Washington WI USA
                      [email protected]