DL: Tips And Tricks: ADO Database Reader (Jet way) - Excel Example - Part 1

Hello,

here is the first tip and first post of mine in this blog for reading any data file/database using ADO and jet drivers. You can find proper connection strings on http://www.connectionstrings.com/

As an example we will discover how to read Excel file using Jet driver. It's not as flexible as using direct Excel Automation (using Automation you can access field properties and so on), but if You would like to read plain data - it's much faster solution and easy to implement.

Let's look at code:

define next variables:

Name DataType Subtype Length
Connection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection  
Recordset Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset  
Fields Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Fields  
Field Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Field  
filename Text   250
Sheetname Text   250
SELECTSTRING CONST SELECT * FROM [%1$]  

Now let's discover code in a codeunit (OnRun trigger):

 

//Here we must define a filename to read from
1: filename:='c:\1.xls';

//Here we must define sheetname of excel file to read

2: sheetname:='Sheet1'; 

//Now we are creating Connection automation
3: CREATE(Connection); 

//Here we define a connection string: in our case it‘s a filename as Data Source
4: Connection.ConnectionString:='Data Source='+filename; 

//Define provider (in out case it‘s Jet.OLEDB driver), and additional properties related to Excel
//option (using version 8.0), HDR - means is first row
// a header to skip it, if your excel file has a header in a first row - use this option to skip it
// "IMEX=1;" tells the driver to always read data as text.
5: Connection.Provider:='Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=No;IMEX=1";'; 
 
//Opening the connection
6: Connection.Open(); 

//Creating Recordset variable
7: CREATE(Recordset); 

//Opening connection and read data using SELECT * FROM SheetX way, so we are reading all the
// data from the sheet
8: Recordset.Open(STRSUBSTNO(SELECTSTRING,sheetname),Connection,1,1);

//Now we will start reading Excel file using a loop
//Loop started
//while not end of recordset - read data
9: WHILE NOT Recordset.EOF DO BEGIN 
10:  Fields:=Recordset.Fields; 

//Now we have row data in Fields variable
11:  MESSAGE(FORMAT(Fields.Item(i).Value));  

//Output Column (i) value in a message box- where i variable is an integer
12:  Recordset.MoveNext;

//Move to the next row in a recordset
13:END;

//Loop finished
//Stop Reading Excel file
//Closing Recordset
14: Recordset.Close;

//Clearing Recordset variable
15: CLEAR(Recordset);
//Closing Connection
16: Connection.Close();
//Clearing Connection automation variable
17: CLEAR(Connection);

 Vuala

Source: http://igor.beeone.googlepages.com/DLJET.zip

 

Comment List
  • Cool! BTW how are you supposed to know Excel book's sheet names? :-) Don't assume they will be always something like 'SheetN'.

  • Hello,

    You will need some ADO magic.

    After opening Connection You can use method

    OpenSchema which returns _RECORDSET with the sheets information. So the steps should be:

    create new variable tablenames with an automation ADO type of recordset and use this method:

    tablenames:=Connection.OpenSchema(20);

    we use number 20 as an enum of schema:

    20 -  adSchemaTables   TABLE_CATALOG

                                         TABLE_SCHEMA

                                         TABLE_NAME

                                         TABLE_TYPE

    And using repeat clause read sheet names like this:

     while Not tablenames.EOF do

         Fields:=tablenames.Fields;

         MESSAGE(FORMAT(Fields.Item(3).Value));  

         tablenames.MoveNext;

    end;

    Hope it helps!

    Br,

    Igor Beeone

  • Hello Everybody,

    here is a next step of this tutorial.

    What's included?

    1) SelectSheetName - implementation of sheet selection functionality

    2) ReadSheet - reads sheet fully to Excel Buffer

    After reading this article You can now replace standart functionality with a new one for fast data reading. So if You're interested please follow my next Blog Post:

    DL Tips And Tricks: ADO Database Reader (Jet way) - Excel Example Part 2

Related
Recommended