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

Miklos_Hollender

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?

Miklos_Hollender

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...

vaprog

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

MMV

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.