Wednesday, December 20, 2006

Read Excel table via ADO

I think it's quite a common task to read data from Excel files. A straight-forward way is to use Excel COM interfaces (Workbook, Worksheet, Range, Cell, etc), but damn it's slow! On the other hand there is ADO (ActiveX Data Objects) that is fast, flexible and familiar to many developers. So it would be nice to access Excel files via ADO to speed up data import and simplify application code.

Here is a utility class that allows reading Excel worksheets as Recordsets. It uses ADOX.Catalog to collect Excel worksheet names and ADODB.Connection, ADODB.Recordset to access worksheet data with a simple "select * from [sheetname]" Recordset's command.

A typical scenario would be something like this:

Counter         cnTotal = 0;
ItemId          itemId;
ItemName        itemName;
AmountCur       price;
Filename        strFilename;
container       conSheets;
ExcelImportADO  xlImport;
;
strFilename = @"c:\import.xls";
xlImport    = new ExcelImportADO(strFilename);
try
{
    // open the first sheet by default
    if (!xlImport.openFile())
    {
        throw error(strfmt("Error opening Excel file '%1'", strFilename));
    }
    if (xlImport.getFieldsCount() < 3)
    {
        throw error(strfmt("Too few columns in the recordset: found %1, expected at least %2",
                           xlImport.getFieldsCount(), 3));
    }
    while (!xlImport.eof())
    {
        itemId   = xlImport.getFieldValue(1);
        itemName = xlImport.getFieldValue('ItemName');
        price    = xlImport.getFieldValue('ItemPrice', false);
        // process data...
        cnTotal++;
        xlImport.moveNext();
    }
    xlImport.finalize();
    Box::info(strfmt("%1 records read", cnTotal));
}
catch (Exception::Error)
{
    xlImport.finalize();
}

You can download the source code of the class at axaptapedia.com. I would like to thank Gustav and blokva for inspiration and very useful tips on improving the class.

Note: ADOX.Catalog returns table names (Excel sheet names) in an alphabetical order - not in the order they appear in a worksheet, and by default the class uses the first name returned by ADOX.Catalog!

5 comments:

Uthisthatu Haindava said...

Hi,
Thanks for the import utility from excel. I am trying to import amount Data in to Ax . This equivalent to AmountCur in AX. While I import this, it does not show me values with decimal places. For example values present in excel are 3293.24, but while imported it only gets 3293.00 .
Please let me know where do i need to alter the code. Also tried with changing the format cells, but nothing is working. Please guide.

gl00mie said...

Note that getFieldValue() method returns value as a string by default. When reading numerc data (e.g. currency amount), pass false as a second argument of this method. In this case it will return the data in it "genuine" format and all the conversions will be held by the DAX kernel.

Unknown said...

Hi,
I have formatted multi tab excel file (price proforma) with data started not from 1st line. On each page data starts from the same line.
Using this class I receive error "External table is not in the expected format."
How I can to define start loading line? Is it possible?

Unknown said...

Found = it was newest file format. save it as oldest format and all start work

Unknown said...

many thanks you for very quick class!