Reading Excel – Alternative

Most of us had already experienced the joy of getting an error while reading Excel through C/AL:

To support the <Language> (<Country>) language, install the Language Package for your version of Microsoft Office.

It seems that even by using automations, DotNet or the Excel buffer, you still get this nice error…
If you search the internet, you get a solution that states you need to change the culture info to en-US, but trust me, that is not easy in NAV. I believe I have tried doing this in the past, but never actually succeeded…

Today, I can offer you another method of reading your Excel file, without this pain in the ass.Since the problem only occurs on RTC as far as I know, this solution is using DotNet variables. I will also assume that all of you use Excel with a version of at least 2007.

Ok, but how? Maybe you have heard of the “database engine” in Microsoft Office products which is called Joint Engine Technology (or JET). It allows to read data from Excel as if excel was a database. The latest versions has been renamed to ACE.

By using OleDB, we can easily access the data inside the Excel file and read that data. This way, we do not need to have another language package installed nor active.

These are the variables I have declared to read the file:

I have these declared to run on ClientSide however for my example.
Next thing is to “open” the file and read the file.

The code above assumes that “FileName” contains the complete path to the file, and “SheetName” has the sheet name to read. It will return the value of each first column.
The GetValue function returns the value for each column, by supplying the column number, except it is zero based, meaning the first column is zero, the second is 1, and so on.

Some remarks:- The DataReader also has other functions as GetBoolean, GetDouble, …
– If a cell is empty the GetValue returns “null”, this could probably be checked with the Reader Function IsDBNull.
– If you have a sheet where the data is 2 columns (eg. X and Y) and only starts on row 10. I noticed that the first row read, is row 10. GetValue(0) will return column X
– In the connection string, you see the property HDR=No. If changed to Yes, it will skip reading the first row, this property states that you have a header data row.
– IMEX=1 implies to process cells as text for the OleDB Connection. In some cases, where you read the header, and the rest of the column contains numeric data, the connection could crash. However, supplying IMEX=1 would make sure it wouldn’t.

That’s about it. Enjoy it 🙂

UPDATE:

In case you are interested in a Sheet Select as well:

These are the variables:

the null variable is a little trick, by creating an object and never assigning a value to it…

UPDATE 2:

The code for reading the sheets does not seem to work in 2013. The place where we supply a single NULL, actually requires an array. Below is a version for 2013. I expect this would probably also work for NAV 2009.

We did however notice that in 2013 the sheetnames are returned containing the $ in the end.

these are the parameters:

 

19 thoughts on “Reading Excel – Alternative

    • Hello Johannes Sebastian,
      I have already worked with NAV2013 Excel Buffer which does indeed use DotNet now. However, they have their own component and are not using the standard interops as you might have noticed. I expect they still have this issue, but solved it in their component.

      As Delyby said, this was for a NAV2009 database, where we cannot use this 2013 functionality.

    • Hello Natalie,

      Good to know, never heard about this trick.
      I suppose my code would also work on the service tier where there is no Excel installed though 😉

      Thanks for mentioning this workaround!

      Regards,
      Magno

  1. Hello Magno,

    how to get the actual row number or column number while importing a file. Do you have any ideas?

    • I didn’t find a way yet to receive the row number yet. Column number would be harder, since 1 row is 1 record.

      I tried by putting filters in the select, to make sure that A is always the first column, but OleDB always starts reading at the first not blank column/row…

      • Yes, that is a big issue 🙂 I hav tried a lot of things, but actual i dont have a solution for this.

  2. I also have to ask:

    How do you get the Guid for sheer select. Actual it isn’t working for me, because i get the error.

    Error: Bad parameters for oledbschematable

    OleDBConnection_l := OleDBConnection_l.OleDbConnection(‘Provider=Microsoft.ACE.OLEDB.12.0;Data Source=’ + FileName_p + ‘;Extended Properties=”Excel 12.0;HDR=NO;IMEX=1″‘);
    OleDBConnection_l.Open;

    EVALUATE(tempGuid, ‘c8b52229-5cf3-11ce-ade5-00aa0044773d’);
    //OleDBDataTable_l := OleDBConnection_l.GetSchema;
    OleDBDataTable_l := OleDBConnection_l.GetOleDbSchemaTable(tempGuid,Null);

    FOR Counter_l := 0 TO OleDBDataTable_l.Rows.Count – 1 DO BEGIN
    TmpString_l := FORMAT(OleDBDataTable_l.Rows.Item(Counter_l).Item(2));
    SheetString_l += COPYSTR(TmpString_l, 1, STRLEN(TmpString_l) – 1) + ‘,’;
    END;

    It crashes on this line:
    OleDBDataTable_l := OleDBConnection_l.GetOleDbSchemaTable(tempGuid,Null);

    Actually I dont know why, but maybe is a wrong guid?

    • Hey Robert,

      The GUID should actually be a property of a DotNet component: OleDbSchemaGuid.Tables

      If you declare this in NAV, you do not find the Tables property (or at least I didn’t at first).
      I created a new Visual Studio Project and did a message of “OleDbSchemaGuid.Tables”.
      The result was this guid.

    • Hello Robert,

      Apparently, there were a few changes in the DotNet wrapper for 2013. Below, you will find some code that does the trick in 2013. We did however notice that it did return the $ in the sheetname, where in 2009, it did not:

      Null was changed to MSCorLib => System.Object
      ArrayList was added: MSCorLib => System.Collections.ArrayList

      SelectSheet() : Text[50]
      ………
      OleDBConn.Open();
      EVALUATE(tempGuid,’c8b52229-5cf3-11ce-ade5-00aa0044773d’);

      //New Code
      ArrayList := ArrayList.ArrayList();
      ArrayList.Add(Null);
      ArrayList.Add(Null);
      ArrayList.Add(Null);
      ArrayList.Add(‘TABLE’);
      DataTable := OleDBConn.GetOleDbSchemaTable(tempGUID,ArrayList.ToArray());
      //End New Code

      //Old Code
      //DataTable := OleDBConn.GetOleDbSchemaTable(tempGuid,Null);
      //End Old Code
      …………

      • Hi Magno,

        thx. It works perfectly. I have made some change to delete the char “$² and the char ‘.

        Appreciate your work and your blog.

  3. Hi Magno,

    I have tried as you told. I am saving an XLS file from a Link and have written the following code to read the Data from the XLS file and write to my table.

    DateField :=TRUE;
    OleDBConn := OleDBConn.OleDbConnection(‘Provider=Microsoft.ACE.OLEDB.12.0;Data Source=’+ FileName + ‘;Extended Properties=”Excel 12.0;HDR=No;IMEX=1″‘);
    SheetName := ‘Deviza’;
    OleDBConn.Open();
    OleDBCmd := OleDBCmd.OleDbCommand(‘select * from [‘ + SheetName + ‘$A4:D25]’,OleDBConn);
    OleDBReader := OleDBCmd.ExecuteReader();

    “UploadCurrencyExch.Rate2”.SETCURRENTKEY(“UploadCurrencyExch.Rate2″.”Entry No.”);
    IF “UploadCurrencyExch.Rate2”.FINDLAST THEN
    “EntryNo.” := “UploadCurrencyExch.Rate2″.”Entry No.”;

    WHILE OleDBReader.Read() DO BEGIN
    IF FORMAT(OleDBReader.GetValue(0)) <> ‘null’ THEN BEGIN
    “UploadCurrencyExch.Rate2”.INIT;
    IF DateField = TRUE THEN BEGIN
    EVALUATE(UploadDate,FORMAT(OleDBReader.GetValue(0)));
    DetectDuplicateCurrencyUpload(UploadDate);
    EVALUATE(“UploadCurrencyExch.Rate2″.”Upload Date”,FORMAT(OleDBReader.GetValue(0)));
    //”UploadCurrencyExch.Rate2″.”Upload Date” := TODAY;
    “UploadCurrencyExch.Rate2”.Status := “UploadCurrencyExch.Rate2”.Status::Open;
    “UploadCurrencyExch.Rate2″.”Exchange Status” := “UploadCurrencyExch.Rate2″.”Exchange Status”::Noon;
    “UploadCurrencyExch.Rate2″.”Entry No.” := “EntryNo.”+1;
    CLEAR(“UploadCurrencyExch.Rate2″.”Currency Code”);
    “EntryNo.” := “EntryNo.”+1;
    END ELSE BEGIN
    EVALUATE(“UploadCurrencyExch.Rate2″.”Upload Date”,UploadDate);
    //”UploadCurrencyExch.Rate2″.”Upload Date” := TODAY;
    IF FORMAT(OleDBReader.GetValue(0)) <> ‘null’ THEN
    “UploadCurrencyExch.Rate2″.”Currency Code” := FORMAT((OleDBReader.GetValue(0)));
    IF FORMAT(OleDBReader.GetValue(1)) <> ‘null’ THEN
    EVALUATE(“UploadCurrencyExch.Rate2″.”Buying Rate”,FORMAT(OleDBReader.GetValue(1)));
    IF FORMAT(OleDBReader.GetValue(2)) <> ‘null’ THEN
    EVALUATE(“UploadCurrencyExch.Rate2″.”Agent Rate”,FORMAT(OleDBReader.GetValue(2)));
    IF FORMAT(OleDBReader.GetValue(3)) <> ‘null’ THEN
    EVALUATE(“UploadCurrencyExch.Rate2″.”Sales Rate”,FORMAT(OleDBReader.GetValue(3)));
    “UploadCurrencyExch.Rate2”.Status := “UploadCurrencyExch.Rate2”.Status::Open;
    “UploadCurrencyExch.Rate2″.”Exchange Status” := “UploadCurrencyExch.Rate2″.”Exchange Status”::Noon;
    “UploadCurrencyExch.Rate2″.”Entry No.” := “EntryNo.”+1;
    “EntryNo.” := “EntryNo.”+1;
    END;
    “UploadCurrencyExch.Rate2”.INSERT;
    DateField := FALSE;
    END;
    END;
    OleDBReader.Close();
    OleDBConn.Close();
    OleDBConn.ReleaseObjectPool();

    The Issue i am getting is the XLS read by above function is not latest, it always take the Old Data it read.
    I think the cache is not clearing. Have you face the same issue?.

    • Hello Prajeesh ,

      If I understand correctly you mean something as follows:
      – read excel
      – change excel
      – read excel again

      2nd time you read, it still read the first version?

      I have not run into this specific situation, so I have no idea about this problem.
      A quick search around the internet, only leads me to the close and ReleaseObjectPool statements you already have…

      On this site: http://blogs.msdn.com/b/selvar/archive/2007/11/10/ole-db-resource-pooling.aspx

      Here I find some changes you could try in the connection string:
      No services “OLE DB Services = 0;”

      I hope this helps. Next to that, I’m afraid, I am as much in the dark about this one as you are.

      Regards,
      Magno

      • I found the Issue. I have two servers like Server 1 & Server 2, i was trying to run the Server 2 Service from the Server 1. The Excel was saving in the Server 1 But the automation is always taking the Excel from the Server 2.

        I have changed the Setting of the automation variable to run on the client, which solved my issue.

        Thanks Magno, i have achieved my requirement with the help of your blog. Great Work.

          • Solving one Issue always comes new one 🙁

            Now i am facing an issue when i tried to automate it with NAS

            Microsoft Dynamics NAV
            —————————

            Microsoft Dynamics NAV Server attempted to issue a client callback to create an Automation object: 88d96a0a-f192-11d4-a65f-0040963251e5 (CodeUnit 50004 Upload Currency Exchange Rate). Client callbacks are not supported on Microsoft Dynamics NAV Server.
            —————————
            OK
            —————————

            But Manually its working fine. Have you faced it? any solution?.

          • Client Side is indeed not supported on NAV Server. Nothing we can do about that I’m afraid. Maybe you can elaborate a bit more about the Server1/Server2 mixup. Since I don’t understand how 1 service tier would open files on another, except when you would be calling webservices or something…

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.