Options

If I need to use CSV is Excel Automation a future proof way?

Miklos_HollenderMiklos_Hollender Member Posts: 1,598
I need to develop some exporting and importing of CSV files, spec is fixed by an external company. What I was thinking is that instead of doing it with DataPorts and then later having to redo in XMLPorts-as-CSV when we must move to the RTC I could use Excel Automation and use some kind of a VBA open-CSV and save-CSV function. Does it sound like a good idea?

Comments

  • Options
    Miklos_HollenderMiklos_Hollender Member Posts: 1,598
    This seems to be working... but not sure if conceptually a good idea.

    For opening a say orders.csv file ExcelBuffer.Open('C:\In\orders.csv', 'orders'); works well.

    For saving this added to the Excel Buffer table:
    SaveAsCSV(FName : Text[1000])
    XlApp.DisplayAlerts := FALSE;
    IF XlWrkBk.FullName = FName THEN BEGIN // Same filename
       XlWrkBk.Save;
    END ELSE BEGIN
       IF FILE.EXISTS(FName) THEN // Forced overwrite, in case the File already exists!
         IF ERASE(FName) THEN;
             XlWrkBk.SaveAs(FName, 6); //6 = xlCSV
    
    END;
    XlApp.Quit;
    XlApp.DisplayAlerts := TRUE;
    CLEAR(XlApp);
    CLEAR(XlWrkBk);
    


    But not sure if it is a good idea...
  • Options
    vaprogvaprog Member Posts: 1,125
    CSV is not a well defined file format. There are many variants when it comes to character encoding, line endings, quoting of fields, quoting of characters within fields, allowed/disallowed characters, representation of data (dates, numbers (decimal separator, thousands separator, negative numbers)) ...

    In the general case, neither Excel nor Dataport/XMLport are flexible enough. The most flexible, fastest and most portable/future-proof way to do it therefor is to use text file io. It is the most demanding variant though, when it comes to initial development. Although, making sure Excel does what you need even when a user changed settings within Excel will not be easy either, and might not be portable for different versions of excel.

    Using XMLports from the beginning is not an option for you? Flat file import/export with XMLports is available since at least NAV 2009 (not sure about NAV 5.0 at the moment).
  • Options
    MMVMMV Member Posts: 99
    I would totally agree to Vaprog because I have a tried a lot of permutations and combinatins and gave up everything and ended with generating data in TEXT file, which seems to be accurate at all times.
Sign In or Register to comment.