Handling Excel files in NAV 2013 (including R2) is not as easy as it seems at first. The Excel Buffer table – an obvious choice of the old days – supports only Open XML format (*.xlsx), and Excel Interoperability implementation seems a bit buggy (check the comments in my old post about .NET interoperability: https://vjeko.com/blog/the-beauty-and-the-beast-nav-and-net). You can still use automation, but then you must handle everything, or at least most of it, on the client end. And, to make it all just a bit worse, none of these approaches, even when they work, will impress you with their speed. All in all, if you want to handle legacy Excel files (*.xls) you will have to make some compromises.
Last week, Mark asked me for a bit of help on a project where he had to import *.xls files into NAV 2013, and he hit all of the possible walls provided by the Excel Buffer, .NET Interoperability components for Excel, and automation walls possible.
When I gave Mark my solution, he said: you should blog about this. So, here I am, blogging about a simple way to read data from Excel, any version, lightning fast.
Let me start by explaining the problem first: all Excel Interoperability bugs (or headaches) come from a very simple fact – there is no native .NET interoperability component for Excel, or any other Office application for that matter. All of them are simply – the good old Automation (COM) components, wrapped into .NET through COM Interoperability feature of .NET. So, when talking to Excel through .NET, you are actually doing the same thing as if accessing it through Automation, except that you are doing that through an extra layer, which is all but lightweight.
If you want my advice – avoid it.
And now, to the solution. What most people are not aware is that you can treat Excel files as external databases. And in Windows world, external databases means that you can access them through native Windows database access components. There have been multiple names for these, as technologies shifted, so you may know these data access components as ODBC, OLEDB, or ADO. They are, of course, not nearly the same thing, but this does not matter for this post.
What matters is that you can use ADO.NET to connect to any Excel file, as if it were a database.
Yes, of course, it does have prerequisites – either you must install Excel on your machine, or you must have Microsoft Data Access Components, or Microsoft Access Database Engine, or whichever other database access pack provided by Microsoft includes it. If you have troubles googling (sorry, binging) it up, let me know and I’ll post some links here.
Once you have those, everything is a matter of connecting to the Excel file using the correct connection string to feed to the ADO.NET Connection object. The connection string does not always look the same, and it depends on whether you are using Open XML or legacy files, but in the end, it will look something like this:
[code]
Provider=Microsoft.ACE.OLEDB.15.0; data source=C:\pathtofile.xls; Extended Properties=”Excel 8.0;HDR=YES”
[/code]
To look up the right connection string for your specific situation, take a look here: http://www.connectionstrings.com/excel/ (this site has most of the connection strings for most of known databases).
Once you have done that, use the ADO.NET to access the data in the file as if it were a table in a database. This piece of code shows how to do that:
[code]
Adapter := Adapter.OleDbDataAdapter(‘SELECT * FROM [Sheet 1$]’,’Provider=Microsoft.ACE.OLEDB.15.0; data source=C:\Temp\ExcelFile.xls; Extended Properties=”Excel 8.0;HDR=YES”‘);
Table := Table.DataTable;
Adapter.Fill(Table);
Rows := Table.Rows;
EnumRows := Rows.GetEnumerator;
Columns := Table.Columns;
WHILE EnumRows.MoveNext DO BEGIN
Row := EnumRows.Current;
FOR i := 0 TO Columns.Count – 1 DO BEGIN
ValVar := Row.Item(Columns.Item(i).ColumnName);
// here you have the value
END;
END;
MESSAGE(‘Processed %1 rows with %2 columns’,Rows.Count,Columns.Count);
[/code]
All of the types should be obvious once I tell you that they are from System.Data namespace (Adapter, Table, Rows, Columns, and Row). EnumRows is the IEnumerator. Again, if you need more help than this, comment, and I’ll put all the instructions here.
And that’s all there is. And it’s way faster than you can imagine. Go – try it, and let me know!
This solution is good if you only need to import or export data from or to excel.
If you ask me.. the real headache comes when you need to format your excel sheet in a specific way.
For instance the customer has an excel template which you want to fill with data add some layout-functionality.
IMHO the best solution here is to use .NET Interop in combination with ClosedXML (http://closedxml.codeplex.com/). Even with the limitions of .net interop in NAV… it works pretty well.
To give you some more background: I had asked Mark to take over a few tasks, for me, and he had handed the performance part of this one to Vjeko. It’s good to have some friends in the channel :-).
Vjeko’s solution, as described in his blog post, has taken the Excel import speed from “no go” to “no problem”. This was not about adding some speed to make a functionality more comfortable, but about whether a key business process can be made to work or not, since it was closely connected to processing a lot of data in a relatively short time frame.
We need to get accustomed to looking into .NET integration for tasks like this.
@wakestar
Don’t forget that we can use JetReport to fill data to a Excel document with predefined layout.
ClosedXML is also limited to the Open XML file type.
Thank you Vjeko, Mark and Gary. Great solution.
Pingback: Excel Interoperability | MS Excel | Power Pivot | DAX
Pingback: Excel Interoperability Woes in NAV 2013 - Navigate Into Success - Microsoft Dynamics NAV - Microsoft Dynamics Community
Hi Vjeko,
can you give us an overview about used variables? We have tried to connect via ADO, but we dont get it work.
Appreciate it.
@Robert: I’ll do it at my earliest convenience – hope it’s not urgent for you.
Name DataType Subtype Length
OleDBAdapter DotNet System.Data.OleDb.OleDbDataAdapter.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
OleDataTable DotNet System.Data.DataTable.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
OleRows DotNet System.Data.DataRowCollection.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
OleEnumRows DotNet System.Collections.IEnumerator.’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
OleColumns DotNet System.Data.DataColumnCollection.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
OleRow DotNet System.Data.DataRow.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
I have used this variables, but I have a problem with the provider and the “Microsoft.ACE.OLEDB.12.0” connection string.
I always get the error message, that the provide isn’t registered on client machine. I have installed all kind of access database connection tools of the web (on local client and NST). Does anyone have any suggesstions?
microsoft ace oledb 12 provider is not registered on the local machine
That is the error message at the moment :((((((((((
It works now 🙂
I have to use the 64bit variant of this driver:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
Thanks a lot for the blog. It is helping us out. It was a little bit hard to get it work, but thank you.
Stay tuned.
Hello again,
last step: How to make a sheet select in a report for 97-2013 excel files? Any ideas?
@Robert: I am not quite sure I understand this question. What exactly are you trying to achieve? Is it: how to make it possible for the user to select a sheet from an existing Excel file, regardless of the Excel version, in a request page for a report? Or maybe I misunderstood it 🙁
Hi Vjeko,
yea of course. The user should select a sheet in the request page of a report, before importing the excel file . There is only one problem!
The function SelectSheetName of the Excel Buffer dont work with xls files. So I’m actual searching for a way to get this run. Until now I don’t have found a way to get it work with nav 2013 r2 and a xls-file.
Secondly I tested the code in this blog and it skips the first row of each excel file, so the import starts always in the second row of a excel file.
best regars 🙂
Change the HDR=YES to NO to get first row data.
This is great, and I think this is the ONLY way to import from Excel (old format) in NAV 2013 R2 under Application Server. Thanks Vjeko!
Hi Vjeko! Great this was the solution I was looking for. Thank you so much!
You’re welcome! Glad it helps 🙂
Hi Vjeko, Currently testing this code but keep geeting error when reaching the code line:
Adapter.Fill(Table);
with error Could not find installable ISAM ,
My code is exactly the same as your, unless I have used a wrong .net type
Any Ideas, tried on 2 different machines to figure out if this was related to missing components.
Running Dynamics NAV 2015
Hi Morten,
Sorry – I don’t have a clue what this is. I have never encountered it, and right now I don’t have much time to play around to figure it out. Did you manage to find a solution in the meantime?
Hi Vjeko,
Can i have Demo Objects for this. I am not able to find the proper DotNet Variables 🙁
Hi Vjeko,
Please provide some information regarding the Variable you have used. Thanks.
Hi Prajeesh, unfortunately, I don’t have demo objects for this, this was written a long time ago. Regarding variables, they are all from ADO.NET, you should check the MSDN documentation to figure out which variable types I used. Here’s a link with documentation on MSDN: https://msdn.microsoft.com/en-us/library/ms254937(v=vs.110).aspx
Hi Vjeko,
I faced with the same issue and my solution was to save the copy of original file as *.xlsx and then process it in standard way.
Here is the part of my code
ServerFileName :=FileMgt.OpenFileDialog(Text006,xcelFileExtensionTok,”);
//Try to save xls as xlsx
XlApp := XlApp.ApplicationClass;
XlHelper.CallOpen(XlApp,ServerFileName);
XlWrkBk := XlApp.ActiveWorkbook;
TempServerFileName := DELSTR(ServerFileName,(STRLEN(ServerFileName)-2),3)+’xlsx’;
XlWrkBk.SaveAs(TempServerFileName,ExcelFileFormat.xlWorkbookDefault,”, ”, FALSE, FALSE,
ExcelXlSaveAsAccessMode.xlNoChange, FALSE, FALSE, FALSE, FALSE, FALSE);
XlWrkBk.Close(FALSE,”,TRUE);
CLEAR(XlHelper);
CLEAR(XlWrkBk);
XlApp.Quit;
CLEAR(XlApp);
ServerFileName := FileMgt.UploadFileSilent(TempServerFileName);
ExcelBuf.OpenBook(ServerFileName,SheetName);
ExcelBuf.ReadSheet;
Hi Valerly,
Thanks for sharing. Just one hint – your CLEAR(…) calls tell me that you are using globals, and you shouldn’t. These should be locals, and then you can go without CLEAR. In any case, ADO.NET will be faster in this case.
Yes, you are right about the speed, but it’s just for alternative way.
P.S. Thanks for code review 🙂
Excellent post.
Is it possible to write back to the Excel file using the adapter?
I tried Table.Reset, Adapter.Update(Table) with no luck.
I’m trying to clear or remove all rows in the sheet.
Hm, good question 🙂 I don’t know the answer by heart, though. I’d have to try it out and do some trial and error.
What we did for now is the following:
Adapter := Adapter.OleDbDataAdapter(‘SELECT * FROM [‘+ SheetName +’$A2:ZZ99999]’,’Provider=Microsoft.ACE.OLEDB.12.0;Data Source=’ + FileName + ‘; Extended Properties=”Excel 12.0 Xml;HDR=NO”;’);
Table := Table.DataTable;
Adapter.Fill(Table);
Columns := Table.Columns;
EnumColumns := Columns.GetEnumerator;
OleDbConnection := OleDbConnection.OleDbConnection(‘Provider=Microsoft.ACE.OLEDB.12.0;Data Source=’ + FileName + ‘; Extended Properties=”Excel 12.0 Xml;HDR=NO”;’);
SqlQuery := ‘UPDATE [‘+ SheetName +’$A2:ZZ99999] SET ‘;
EnumColumns := Columns.GetEnumerator;
WHILE EnumColumns.MoveNext DO BEGIN
Column := EnumColumns.Current;
SqlQuery += Column.ColumnName + ‘ = NULL,’;
END;
SqlQuery := DELCHR(SqlQuery,’>’,’,’) + ‘;’;
OleDbCommand := OleDbCommand.OleDbCommand(SqlQuery,OleDbConnection);
OleDbConnection.Open;
OleDbCommand.ExecuteNonQuery;
OleDbConnection.Close;
I found out that if you set the .net variables to runoncilent=true (even on a standalone installation), the performance have a massive degradation (more than 10x slower).
I am not totally sure about the reason behind it, tho…
I got it: serialization/deserialization of dotnet variables