Is it possible to do a daily import using the NAS of a csv file using XMLPorts in NAV 2009 R2?

matthiasclaes

We are still using the classic client and the NAS, but the webservice service is operational. (Dynamics NAV 2009 R2, build 39038)
I created a dataport to import the data manually and that works. Only after doing that work did I find out that the NAS cannot run dataports.
So, I've got to import the CSV data using an XMLPort, but I found several posts on the internet stating that using XMLPorts for non-XML imports does not work from the classic environment. And the NAS is 'Classic Client'. So, how do you set up an automated import of non-XML data?

ftornero

You can use a report, like this one

OBJECT Report 50001 Read CSV File
{
  OBJECT-PROPERTIES
  {
    Date=06-13-18;
    Time=00:00:00;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    ProcessingOnly=Yes;
    OnPostReport=BEGIN
                   IF GUIALLOWED THEN
                     MESSAGE('Process ended, lines %1', i);
                 END;

  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table2000000026;
        DataItemTableView=SORTING(Number)
                          WHERE(Number=CONST(1));
        OnAfterGetRecord=BEGIN
                           ReadFile(FileName);
                         END;

      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=12000;
            SectionHeight=846;
          }
          CONTROLS
          {
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
      SaveValues=Yes;
    }
    CONTROLS
    {
      { 1000000002;TextBox;3630 ;220  ;5170 ;440  ;InPage=-1;
                                                   CaptionML=ENU=File Name;
                                                   SourceExpr=FileName }
      { 1000000003;Label  ;220  ;220  ;3300 ;440  ;ParentControl=1000000002;
                                                   InPage=-1 }
    }
  }
  CODE
  {
    VAR
      FileName@1000000000 : Text[1024];
      Sep@1000000004 : TextConst 'ESP=";"';
      i@1100288000 : Integer;

    PROCEDURE ReadFile@1000000000(FileName@1000000000 : Text[1024]);
    VAR
      qFile@1000000001 : File;
      Line@1000000002 : Text[1024];
      Value@1000000009 : Text[1024];
      qFecha@1000000010 : Date;
      qDec@1000000011 : Decimal;
      qInt@1000000012 : Integer;
      qDialog@1000000008 : Dialog;
    BEGIN
      IF GUIALLOWED THEN
        qDialog.OPEN('Reading File: #1######');
      i := 0;
      qFile.TEXTMODE := TRUE;
      IF qFile.OPEN(FileName) THEN BEGIN
        WHILE qFile.POS < qFile.LEN DO BEGIN
          qFile.READ(Line);
          i := i +1 ;
          IF GUIALLOWED THEN
            qDialog.UPDATE(1, i);
          Value := UPPERCASE(Entry(1, Line, Sep));
        END;
      END;
    END;

    PROCEDURE Entry@1000000004(PintEntry@1000000000 : Integer;PtexEntries@1000000001 : Text[1024];PtexSeperator@1000000002 : Text[1]) PtexReturnValue : Text[1024];
    VAR
      LintLength@1000000003 : Integer;
      Lint@1000000004 : Integer;
    BEGIN
      // Entry
      // gives an entry of a list base on an integer
      // (like Navision SELECTSTR, but without bugs)
      // PARAMETERS :
      // PintEntry : which element must be returned
      // IF (PintElement <= 0) OR (PintElement > NumEntries(PtexEntries,PtexSeperator))
      // THEN '' is returned
      // PtexEntries : list
      // PtexSeperator : character used as separator
      // if = '' then ',' is used
      // PtexReturnValue : selected element

      PtexReturnValue := '';
      LintLength := STRLEN(PtexEntries);

      IF (LintLength = 0) OR (PintEntry <= 0) THEN
        EXIT('');

      IF PtexSeperator = '' THEN
        PtexSeperator := ',';

      Lint := 0;

      WHILE Lint < LintLength DO BEGIN
        Lint := Lint + 1;
        IF COPYSTR(PtexEntries,Lint,1) = PtexSeperator THEN
          PintEntry := PintEntry - 1
        ELSE IF PintEntry = 1 THEN
          PtexReturnValue := PtexReturnValue + COPYSTR(PtexEntries,Lint,1);
      END;

      EXIT(PtexReturnValue);
    END;

    BEGIN
    END.
  }
}

Regards

ftornero

You can use a report, like this one

OBJECT Report 50001 Read CSV File
{
  OBJECT-PROPERTIES
  {
    Date=06-13-18;
    Time=00:00:00;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    ProcessingOnly=Yes;
    OnPostReport=BEGIN
                   IF GUIALLOWED THEN
                     MESSAGE('Process ended, lines %1', i);
                 END;

  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table2000000026;
        DataItemTableView=SORTING(Number)
                          WHERE(Number=CONST(1));
        OnAfterGetRecord=BEGIN
                           ReadFile(FileName);
                         END;

      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=12000;
            SectionHeight=846;
          }
          CONTROLS
          {
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
      SaveValues=Yes;
    }
    CONTROLS
    {
      { 1000000002;TextBox;3630 ;220  ;5170 ;440  ;InPage=-1;
                                                   CaptionML=ENU=File Name;
                                                   SourceExpr=FileName }
      { 1000000003;Label  ;220  ;220  ;3300 ;440  ;ParentControl=1000000002;
                                                   InPage=-1 }
    }
  }
  CODE
  {
    VAR
      FileName@1000000000 : Text[1024];
      Sep@1000000004 : TextConst 'ESP=";"';
      i@1100288000 : Integer;

    PROCEDURE ReadFile@1000000000(FileName@1000000000 : Text[1024]);
    VAR
      qFile@1000000001 : File;
      Line@1000000002 : Text[1024];
      Value@1000000009 : Text[1024];
      qFecha@1000000010 : Date;
      qDec@1000000011 : Decimal;
      qInt@1000000012 : Integer;
      qDialog@1000000008 : Dialog;
    BEGIN
      IF GUIALLOWED THEN
        qDialog.OPEN('Reading File: #1######');
      i := 0;
      qFile.TEXTMODE := TRUE;
      IF qFile.OPEN(FileName) THEN BEGIN
        WHILE qFile.POS < qFile.LEN DO BEGIN
          qFile.READ(Line);
          i := i +1 ;
          IF GUIALLOWED THEN
            qDialog.UPDATE(1, i);
          Value := UPPERCASE(Entry(1, Line, Sep));
        END;
      END;
    END;

    PROCEDURE Entry@1000000004(PintEntry@1000000000 : Integer;PtexEntries@1000000001 : Text[1024];PtexSeperator@1000000002 : Text[1]) PtexReturnValue : Text[1024];
    VAR
      LintLength@1000000003 : Integer;
      Lint@1000000004 : Integer;
    BEGIN
      // Entry
      // gives an entry of a list base on an integer
      // (like Navision SELECTSTR, but without bugs)
      // PARAMETERS :
      // PintEntry : which element must be returned
      // IF (PintElement <= 0) OR (PintElement > NumEntries(PtexEntries,PtexSeperator))
      // THEN '' is returned
      // PtexEntries : list
      // PtexSeperator : character used as separator
      // if = '' then ',' is used
      // PtexReturnValue : selected element

      PtexReturnValue := '';
      LintLength := STRLEN(PtexEntries);

      IF (LintLength = 0) OR (PintEntry <= 0) THEN
        EXIT('');

      IF PtexSeperator = '' THEN
        PtexSeperator := ',';

      Lint := 0;

      WHILE Lint < LintLength DO BEGIN
        Lint := Lint + 1;
        IF COPYSTR(PtexEntries,Lint,1) = PtexSeperator THEN
          PintEntry := PintEntry - 1
        ELSE IF PintEntry = 1 THEN
          PtexReturnValue := PtexReturnValue + COPYSTR(PtexEntries,Lint,1);
      END;

      EXIT(PtexReturnValue);
    END;

    BEGIN
    END.
  }
}

Regards

ftornero

Sorry I forgot to say that with this line you get de first field in the line.

Value := UPPERCASE(Entry(1, Line, Sep));

You can convert the Value that it is a Text to the value that you are expecting,

To get de second field

Value := UPPERCASE(Entry(2, Line, Sep));

An so on.

ftornero

Of course you don't need to UPPERCASE the Text, I copy and paste from a real report I have.

matthiasclaes

ftornero wrote: »

You can use a report, like this one

Regards

So basically, there is no solution using the built-in tools if you want to automate things?
I'm a bit disappointed. Coding up something like this is like reinventing a wheel that has been invented decades ago.
I can do it in a codeunit as well and avoid future classic -> rdlc conversion work.