Board index » delphi » Newbie: completely and totally lost; howto use excel file

Newbie: completely and totally lost; howto use excel file

I have a file named DS.xls.  I want to connect to the data with a Delphi
table or query.

First I open the BDE Administrator and create a new Database, right?  I
choose Microsoft Excel Driver for Type, and Database Name is the path to
the file. (C:\temp\SD.xls), and Apply changes.

OK, now I click the little plus sign next to my new Database SD; hit
enter at the logon and
boom!  BDE Error 13059  Invalid connection string attribute DATABASE

??????????? What am I missing? ?????????????

 

Re:Newbie: completely and totally lost; howto use excel file


Hi!

To access Excel from Delphi You must use ODBC Excel Driver.
There are 2 options :
   1)
         - define new DSN (from ODBC Administrator - see Control Panel)
         - within it choose your Excel file (see Select Workbook).
         - in Database Component in Alias field choose this DSN.
    2)
        - in Database Component choose Excel driver in "Use driver"  field
        - each time then you will connect to this file you will search the
real Excel file.

    Define TTable Component : it's Database Name := defined Database;
    If you have a predefined Named Range in Excel File insert manually it's
Name to Table Name field.
    If you don't have this Named Range you may or insert Sheet Name as, for
example, 'Sheet1$' (pay
    your attention on $ in name - mandatory) or as 'Sheet1$A1:B3'.
    In first case you will receive full Sheet and it's preferrable to insert
the Fields Names in the first row
    in Excel File (excatly as you see when you open DBF file in Excel).

    When you will perform Table1.Open you receive the RecordSet.

    One tip: if you will define Named Range on ONE cell  use
Table1.Fields[0].DisplayLabel to see the value
    of this cell (in this case you will not be able to change this cell from
Delphi. I used 2 vertical cells :
    first is treated as Field Name by Delphi and second as it's Value).

    Best regards,
    Vadim Duchovny,
    Beer-Sheva, Israel

Quote
Rose X wrote:
> I have a file named DS.xls.  I want to connect to the data with a Delphi
> table or query.

> First I open the BDE Administrator and create a new Database, right?  I
> choose Microsoft Excel Driver for Type, and Database Name is the path to
> the file. (C:\temp\SD.xls), and Apply changes.

> OK, now I click the little plus sign next to my new Database SD; hit
> enter at the logon and
> boom!  BDE Error 13059  Invalid connection string attribute DATABASE

> ??????????? What am I missing? ?????????????

Re:Newbie: completely and totally lost; howto use excel file


Thanks.  I don't understand very much; but after reading your post I
deleted the alias I had put in the BDE administrator; and kept the one
in ODBC (I didn't create a new one, just pointed the 'Excel Files' in
'DSN files' tab to my file.)  And then I put the $ as you cautioned, so
I have the data in Delphi, now!

What is the Database Component and where is it?  I saw nothing with an
Alias or Use driver.  If I ever have to create another one, I suppose
I'll need this.

rose

Quote
Vadim Duchovny wrote:

> Hi!

> To access Excel from Delphi You must use ODBC Excel Driver.
> There are 2 options :
>    1)
>          - define new DSN (from ODBC Administrator - see Control Panel)
>          - within it choose your Excel file (see Select Workbook).
>          - in Database Component in Alias field choose this DSN.
>     2)
>         - in Database Component choose Excel driver in "Use driver"  field
>         - each time then you will connect to this file you will search the
> real Excel file.

>     Define TTable Component : it's Database Name := defined Database;
>     If you have a predefined Named Range in Excel File insert manually it's
> Name to Table Name field.
>     If you don't have this Named Range you may or insert Sheet Name as, for
> example, 'Sheet1$' (pay
>     your attention on $ in name - mandatory) or as 'Sheet1$A1:B3'.
>     In first case you will receive full Sheet and it's preferrable to insert
> the Fields Names in the first row
>     in Excel File (excatly as you see when you open DBF file in Excel).

>     When you will perform Table1.Open you receive the RecordSet.

>     One tip: if you will define Named Range on ONE cell  use
> Table1.Fields[0].DisplayLabel to see the value
>     of this cell (in this case you will not be able to change this cell from
> Delphi. I used 2 vertical cells :
>     first is treated as Field Name by Delphi and second as it's Value).

>     Best regards,
>     Vadim Duchovny,
>     Beer-Sheva, Israel

> Rose X wrote:

> > I have a file named DS.xls.  I want to connect to the data with a Delphi
> > table or query.

> > First I open the BDE Administrator and create a new Database, right?  I
> > choose Microsoft Excel Driver for Type, and Database Name is the path to
> > the file. (C:\temp\SD.xls), and Apply changes.

> > OK, now I click the little plus sign next to my new Database SD; hit
> > enter at the logon and
> > boom!  BDE Error 13059  Invalid connection string attribute DATABASE

> > ??????????? What am I missing? ?????????????

Re:Newbie: completely and totally lost; howto use excel file


To read an Excel worksheet:

Create an ODBC DSN that points to the worksheet.

Excel files will not appear in the TTable.TableName property drop-down.
Enter the worksheet file name with no extension followed by a $ in the
TableName property.  You can optionally append a cell range.  For exmaple:
projects$A2:J1000

To read all of the rows in the worksheet when specifying a cell range make
the ending row number larger than the last possible row.

Bill

--

Bill Todd - TeamB
(TeamB cannot respond to email questions. To contact me
 for any other reason remove nospam from my address.)

Other Threads