Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / ERROR While Importing ...
Microsoft Dynamics NAV (Archived)

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

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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

  • DieterD Profile Picture
    DieterD 5 on at
    RE: ERROR While Importing data from .CSV, .XLS Format Files in NAV 2016

    Hi Janaka,

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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: ERROR While Importing data from .CSV, .XLS Format Files in NAV 2016

    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')

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: ERROR While Importing data from .CSV, .XLS Format Files in NAV 2016

    Hi,

       Above Code is Working Fine

    Thank you...

  • Verified answer
    Binesh Profile Picture
    Binesh 7,885 on at
    RE: ERROR While Importing data from .CSV, .XLS Format Files in NAV 2016

    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');

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: ERROR While Importing data from .CSV, .XLS Format Files in NAV 2016

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: ERROR While Importing data from .CSV, .XLS Format Files in NAV 2016

    Hi,

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

    In the case of .XLSX Format It works Fine.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: ERROR While Importing data from .CSV, .XLS Format Files in NAV 2016

    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. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans