Importing excel file using xmlport in navision

azimkhan96
azimkhan96 Member Posts: 13
edited 2010-04-16 in NAV/Navision Classic Client
Hi,

Can anyone tell me, is it possible to import an xls file using xmlport in navision or not.
If yes tell please give way or any article which i can refer for this.

Thanks

Comments

  • kine
    kine Member Posts: 12,562
    XLS is not XML. XLSX includes some XML, but it is zipped. And th complexity of the XML is beyond the limits of the XMLPort.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • garak
    garak Member Posts: 3,263
    azimkhan96 wrote:
    Hi,
    ... is it possible to import an xls file using xmlport ...

    A XLS (Excel Spreadsheet) is not XML (Extensible Markup Language) File.
    So you can import a xls file on different ways. As csv with a dataport or for example like the function "Import from Excel" in form 113.

    Is your xls file a XLSX file as kine ask?

    Regards
    Do you make it right, it works too!
  • azimkhan96
    azimkhan96 Member Posts: 13
    Thanks for replying.


    As mentioned by you, i have changed the excel file extension to xlsx but still error persists at the time of importing.
    The error is "The XML Parser setup fails Error = c00cee2d Code meaning = Unknown error 0xC00CEE2D:"

    I have also checked the form 113 Budget. The Budget form have function which in turn call the code to write the data from the excel file into navision table. If this is way to import/export from excel file, then what is the use of xmlport with respect to excel file import/export.
  • DenSter
    DenSter Member Posts: 8,307
    XMLPorts are used to import XML documents, not Excel files.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • bhalpin
    bhalpin Member Posts: 309
    If you are trying to read an Excel file (xls) then I would you look at Table 370 - Excel Buffer. It has most of the functionality needed to read a spreadsheet. (In NAV 4.x it would die on formula cells, but I've got a fix for that if you need it.)

    If you define ExcelBuf as Table 370, you simply:

    ExcelBuf.OpenBook(FileName,SheetName);
    ExcelBuf.ReadSheet;

    That loads all the data into table 97 (it uses one record for each cell in the spreadsheet.) Then you work through that table doing whatever you want to with the values.

    Try the load then look at what's in 97 to get yourself oriented. Then look at the code in that table to see wehat else is on the menu.

    Good luck

    Bob
  • vincentc
    vincentc Member Posts: 6
    Hey bhalpin,

    I use this functionality too... works great. How did you get past the issue with formula cells?
  • bhalpin
    bhalpin Member Posts: 309
    Here's what I changed:
    Table 370 - Excel Buffer - Ver NAVW14.00 - ReadSheet()
-----------------------------------------------------------------------
Window.OPEN(
  Text007 +
  '@1@@@@@@@@@@@@@@@@@@@@@@@@\');
Window.UPDATE(1,0);

DELETEALL;
XlRange := XlWrkSht.Range(GetExcelReference(5)).SpecialCells(11);
Maxi := XlRange.Row;
Maxj := XlRange.Column;
i := 1;
REPEAT
  j := 1;
  VALIDATE("Row No.",i);
  REPEAT
    VALIDATE("Column No.",j);
// ---------------------------------------
//  Handle formula cells
//  "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');
    "Cell Value as Text" := '';
    IF FORMAT(XlWrkSht.Range(xlColID + xlRowID).HasFormula) = 'No' THEN
      "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');
// ---------------------------------------
    IF "Cell Value as Text" <> '' THEN BEGIN
      INSERT;
    END;
    j := j + 1;
  UNTIL j > Maxj;
  i := i + 1;
  Window.UPDATE(1,ROUND(i / Maxi * 10000,1));
UNTIL i > Maxi;
XlWrkBk.Close(FALSE);
XlApp.Quit;
CLEAR(XlApp);
Window.CLOSE;

Categories