Microsoft Dynamics NAV (Archived)

ERROR While Importing data from .CSV, .XLS Format Files in NAV 2016

(0) Share

Report

Posted on by Community Member

Hi Experts,

In NAV 2009R2 table I could Import data from any type of format files(i.e. .CSV, .XLS, XLSX).

But coming to NAV 2016 I can't Import data from .CSV, .XLS files.

While importing I got following error in NAV 2016 when i click Select Sheet name

---->>>>

A call to Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookReader.Open failed with this message: The Microsoft
Excel workbook cannot be opened. This may be caused by one of the following reasons:
1. The path to the file is a web address. You must navigate to the file by using the Open File dialog box, or you
can save the file locally and then open it.
2. The workbook is not saved in the Open XML file format. In Microsoft Excel, save the workbook as an Excel
workbook (.xlsx file), and then try your request again.
The following error occurred: File contains corrupted data..

----->>>>

I am using following Standard functions

FileName:=FileMgt.UploadFile('Import Excel File','xls'); //(419 File Management Codeunit)

SheetName := ExcelBuf.SelectSheetsName(FileName); //(370 Excel Buffer table)

ExcelBuf.OpenBook(FileName,SheetName);

ExcelBuf.ReadSheet;

Can anyone suggest me, How to Import data from .CSV, .XLS Format Files in NAV 2016 Table.

Thanks in Advance...

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (2)

Community Member on at

Like (0)

Report

Hi,

Are you able to select your file using Open Dialog ? Does it work for .xls file?

The code which you have written works fine for me.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

My Problem is While Imoprting .CSV, .XLS Format Files getting above ERROR.

In the case of .XLSX Format It works Fine.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The error is generated in the line SheetName := ExcelBuf.SelectSheetsName(FileName); while importing CSV.

It looks like Excel Buffer supports only xlsx. You can use Xml port for importing CSV.

Was this reply helpful? Yes No
Verified answer

Binesh 7,885 on at

Like (1)

Report

Hello,

Don't use Excel Buffer ReadSheet functionality, Instead of use bellow code.

Note:

Variables are "Microsoft.Office.Interop"

For selecting sheet,opening dialog you can use FileMgt.

-----------------------------

FileName:='C:\Temp\XlFile.xlsx';
IF ISNULL(XlApp) THEN
XlApp := XlApp.ApplicationClass;
XlApp.Visible(FALSE);// FALSE if you don't want to show.
XlWrkBk := XlApp.Workbooks.Add(FileName);
XlWrkBk := XlApp.ActiveWorkbook;
XlWrkSht := XlWrkBk.Worksheets.Item(1);
SheetName := XlWrkSht.Name;
XlWrkSht := XlWrkBk.Worksheets.Item(SheetName);

Maxi := XlWrksht.Cells.SpecialCells(11,2).Row; //Finding Last RowNo---/ICS--->>
Maxj := XlWrksht.Cells.SpecialCells(11,2).Column; //03.30.2016 Finding Last Column no.---/ICS--->>
i := 2;
K :=1;

REPEAT
YourTable.INIT;
YourTable."Entry No." := EntryNumG;
YourTable."Col 1" := DELCHR(FORMAT(Xlwrksht.Range('D' + FORMAT(i),'D' + FORMAT(i)).Value),'<>',' ');
YourTable."Col 2" := DELCHR(FORMAT(Xlwrksht.Range('E' + FORMAT(i),'E' + FORMAT(i)).Value),'=',',');
YourTable.INSERT;
i += 1;
UNTIL i > Maxi;
XlApp.Quit;
CLEAR(XlApp);
MESSAGE('Completed');

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (1)

Report

Hi,

Above Code is Working Fine

Thank you...

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Binesh,

I am getting below error on XlWrkBk := XlApp.Workbooks.Add(FileName);

Microsoft Dynamics NAV

---------------------------

A call to System.__ComObject.Add failed with this message: The type of one or more arguments does not match the method's parameter type.

---------------------------

OK

---------------------------

Could you please check whether these variables are correct

XlApp (Microsoft.Office.Interop.Excel.ApplicationClass.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')

XlWrkBk (Microsoft.Office.Interop.Excel.Workbook.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')

XlWrkSht (Microsoft.Office.Interop.Excel.Worksheet.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')

Was this reply helpful? Yes No
DieterD 5 on at

Like (0)

Report

Hi Janaka,

I am facing thesame problem as you, did you find a solution?

Was this reply helpful? Yes No