Create CSV

ricky76

I have a customer who initially wanted to export information to a text file and i have wrote all the code to do this. However they now say they want this to be a csv file. Is there a quick way i can convert a text file created in code to csv format? Any help would be most appreciated.

Xypher

This this might work?

quoted_data := FALSE;

FOR i := 1 TO STRLEN(filedata) DO BEGIN
  IF filedata[i] = '"' THEN
    quoted_data := NOT quoted_data;

  IF (filedata[i] = ' ') AND NOT quoted_data THEN
    filedata[i] := ',';
END;

Just off the top of my head.. not sure how reliable it may be.

ricky76

Still struggling i'm afraid. Even some tips on how to create a csv would be most appreciated

Leonh

Sorry if I am missing what you need, but would it not be easier to modify your current dataport so it generates a new comma seperated file?

ricky76

I am not using a dataport. The file is generated from code pulling in data from several different tables.

Xypher

Post the file generating code.

Leonh

Could you post a single line of your text file?

ricky76

My code loops round all the records that meet the criteria and each time calls a function that writes the next line into the text file.

Line[x] := TransportNo + FORMAT(Tab) + SalesLine."Cross-Reference No." + FORMAT(Tab) +
FORMAT(QtyShipped3) + FORMAT(Tab);

Leonh

Could you not do a find tabs and replace with , in your text editor?

Xypher

Yeah why not just do..

Line[x] := TransportNo + ',' +
           SalesLine."Cross-Reference No." + ',' +
           FORMAT(QtyShipped3) + ',';

ricky76

Thank you for your help it is most appreciated. That worked fine.

SobyOne

Don't forget that you can use a dataport even with multiple tables.

You would benefit greatly by loading the information in to column variables and allowing the system to dump the data to the text file.

Changing from CSV to TAB to Fixed Width is an added benefit, but your code will go no faster than the dataport (I've tested codeunits vs. dataports).

David_Singleton

Xypher wrote:

Yeah why not just do..

Line[x] := TransportNo + ',' +
           SalesLine."Cross-Reference No." + ',' +
           FORMAT(QtyShipped3) + ',';

Just trhowing a comma in there does not make it a CSV file. [-X

Leonh

David Singleton wrote:

Just trhowing a comma in there does not make it a CSV file. [-X

Sorry if I come across as ignorant, and I realise there are other subtleties involved with CSV, but is it not essentially a flat file table with fields seperated by commas?

If I am missing something important would you mind directing me to further reading?

David_Singleton

Leonh wrote:

David Singleton wrote:

Just trhowing a comma in there does not make it a CSV file. [-X

Sorry if I come across as ignorant, and I realise there are other subtleties involved with CSV, but is it not essentially a flat file table with fields seperated by commas?

If I am missing something important would you mind directing me to further reading?

The reply was just to warn Xypher that she/he was over simplifying this. It was not aimed at you.

The issue is that a CSV file has special handling for strings that contain commas ( , )and double quotes ( " ). And these need to be taken into account. Basically if there is a ( , ) in a string it must be enclosed in quotes ( " ) and if there is a quote in the sting then different "standards have different ways of handling them. So a CSV line may look like

C00223,Jane's Deli,"Smith Street,  London",2,"10""disc"

Unfortunately the two most likely programs you are going to need to work with CSV files are Excel and NAV, and neither of them actually handle CSV files correctly. For instance Excel will remove leading zeros from a CSV field which it should not.

And it goes on. To be safe, you can surround EVERY field with " " and replace ALL " with "" like this

"C00223","Jane's Deli","Smith Street,  London","2","10""disc"

This link may help you more:

RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files

By the way sorry if you thought I was implying ignorance on your part. I was just trying to warn that the reply given to you was wrong.

Leonh

David Singleton wrote:

RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files

By the way sorry if you thought I was implying ignorance on your part. I was just trying to warn that the reply given to you was wrong.

No that's brilliant, thank you very much.

Xypher

You are right David,

I did, however, include some use of quotation mark detection in my initial code. Was just not on my mind when I replied that other time.. oops #-o