Reading Excel worksheets in Dynamics AX using CCADOConnection, CCADOCommand & CCADORecordSet classes

Recently I came across Bojan's post on performance improvements using CCADOConnection, CCADOCommand & CCADORecordSet classes for reading Excel sheets. You can read the original post here. Well, there is a minor change in the CCADORecordSet class in Dynamics AX 2009 compared to Dynamics AX 4.0 which most users should be aware of. But I thought of still documenting it here.

The CCADORecordSet.moveNext() method isn't present in AX 2009 anymore. To move to the next recordSet, we should use adoRecordSet.recordSet().moveNext();


void readExcelSheet()
{
CCADOConnection adoConnection;
CCADOCommand adoCommand;

CCADORecordSet adoRecordSet;
CCADOFields adoFields;
CCADOField adoField;

int i,j;
;

adoConnection = new CCADOConnection();

adoRecordSet = new CCADORecordSet();

adoConnection.open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile.text() + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'");

adoCommand = new CCADOCommand();
adoCommand.activeConnection(adoConnection);
adoCommand.commandText(@"SELECT * FROM [SHEET1$]");

adoRecordSet = adoCommand.execute();

while(!adoRecordSet.EOF())
{
adoFields = adoRecordSet.fields();

for (i=0; i<adoFields.count(); i++)
{
print adoFields.itemIdx(i).value();
}

// The below commented line doesnt work in AX 2009
// adoRecordSet.moveNext();
adoRecordSet.recordSet().moveNext();
}

pause;
}


Hope this helps.

Comments

verena_techie said…
I am wondering if you update tables in this manner, will the ID fields still populate correctly? What is your suggestion for loading data with almost a million rows?
Zubair Ahmed said…
Hi Verena,

If you use these classes to insert data, the RecId field wont be populated. It is pretty much the same way how you insert records using direct SQL.

Some upgrade classes make use of the systemSequence class to generate RecIds which are inserted manually then.

The generation of RecId is a concept internal to Dynamics AX. If you are inserting records directly using SQL or Excel, it will be your responsibility to fill in the RecId field as well.

For inserting million of records, you can write direct SQL statements generating and populating the RecId field manually or you can use AIF operations as well in which case the RecId will be generated automatically.

Popular posts from this blog

How to add empty ranges in query

Get selected records in Dynamics AX 2012

The field with ID '0' does not exist in table - Cause and resolution.