Create CSV

ricky76
ricky76 Member Posts: 204
edited 2008-04-16 in NAV/Navision Classic Client
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.

Comments

  • Xypher
    Xypher Member Posts: 297
    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
    ricky76 Member Posts: 204
    Still struggling i'm afraid. Even some tips on how to create a csv would be most appreciated
  • Leonh
    Leonh Member Posts: 8
    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
    ricky76 Member Posts: 204
    I am not using a dataport. The file is generated from code pulling in data from several different tables.
  • Xypher
    Xypher Member Posts: 297
    Post the file generating code.
  • Leonh
    Leonh Member Posts: 8
    Could you post a single line of your text file?
  • ricky76
    ricky76 Member Posts: 204
    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
    Leonh Member Posts: 8
    Could you not do a find tabs and replace with , in your text editor?
  • Xypher
    Xypher Member Posts: 297
    Yeah why not just do..
    Line[x] := TransportNo + ',' +
           SalesLine."Cross-Reference No." + ',' +
           FORMAT(QtyShipped3) + ',';
  • ricky76
    ricky76 Member Posts: 204
    Thank you for your help it is most appreciated. That worked fine.
  • SobyOne
    SobyOne Member Posts: 20
    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).
    _\~ () ]3 `/ () |\| [-
    http://www.SobyLand.com
    651-815-0698
  • David_Singleton
    David_Singleton Member Posts: 5,479
    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
    David Singleton
  • Leonh
    Leonh Member Posts: 8
    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
    David_Singleton Member Posts: 5,479
    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.
    David Singleton
  • Leonh
    Leonh Member Posts: 8
    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
    Xypher Member Posts: 297
    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

Categories