Announcement

Collapse
No announcement yet.

sql tools and excel

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Michael Mattias
    replied
    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.'

    Leave a comment:


  • Eric Pearson
    replied
    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

    Leave a comment:


  • tom kroto
    replied
    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.

    Leave a comment:


  • Edwin Knoppert
    replied
    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.

    Leave a comment:


  • Michael Mattias
    replied
    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

    Leave a comment:


  • José Roca
    replied
    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/smffo...p?topic=2601.0

    Leave a comment:


  • Michael Mattias
    replied
    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
    Code:
    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
    Last edited by Michael Mattias; 6 Aug 2009, 11:22 AM.

    Leave a comment:


  • Eric Pearson
    replied
    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:
    Code:
    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:
    Code:
    SELECT * FROM `SHEET1$` WHERE MYCOLUMN = 'HELLO'
    -- Eric Pearson, Perfect Sync Software

    Leave a comment:


  • tom kroto
    replied
    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

    Leave a comment:


  • Edwin Knoppert
    replied
    You really must know the name of the sheet and then use [].

    Leave a comment:


  • tom kroto
    started a topic sql tools and excel

    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 "

    Thanks
Working...
X