PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/19/2015 9:35 AM by  jaherb
double quote in empty field
 8 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Srini Rao
Private
Private
Veteran Member
(287 points)
Veteran Member
Posts:133


Send Message:

--
06/30/2014 2:06 PM
    Hello, In COBOL program, I need to replace the empty fields with double quote. how to do this?

    My out is in CSV format.
    jaherb
    Private
    Private
    Veteran Member
    (471 points)
    Veteran Member
    Posts:163


    Send Message:

    --
    06/30/2014 4:09 PM
    This should automatically happen with alphanumeric fields by default.   The csv attributes, below show the definition of this in the "Quote Character" field.

    Numeric fields will not have double quotes around empty fields, however.

    John Henley
    Private
    Private
    Senior Member
    (9563 points)
    Senior Member
    Posts:3205


    Send Message:

    --
    06/30/2014 4:45 PM
    If my memory serves correct, I think the quote characters are only used if the field isn't empty.
    In other words, since an empty field doesn't contain anything, the field separators are sufficient.
    In fact, the quote characters are really only supposed to be used when text is multi-line, or contains the field separator itself (i.e. "last name, first name").
    Always been one of the great debates over CSV, I guess.
    You can see this link for a couple of solutions:
    https://www.lawsonguru.com/forums/i...-csv-file/
    My solution is to use this:
    1. Use an unlikely character as the field separator (i.e. ~ or ^, etc.)
    2. then shell script calling sed (or some other character parsing utility) after the COBOL program runs, doing global replacements
    2.1 replace ^^ with ^""^
    2.2 replace ^ with ^""
    2.3 replace ^ with comma
    Thanks for using the LawsonGuru.com forums!
    John
    jaherb
    Private
    Private
    Veteran Member
    (471 points)
    Veteran Member
    Posts:163


    Send Message:

    --
    06/30/2014 5:30 PM
    By default, double quotes will surround every alpha numeric field, unless it is changed in the csv attributes form.
    John Henley
    Private
    Private
    Senior Member
    (9563 points)
    Senior Member
    Posts:3205


    Send Message:

    --
    07/03/2014 4:03 PM
    Posted By jaherb on 06/30/2014 05:30 PM

    By default, double quotes will surround every alpha numeric field, unless it is changed in the csv attributes form.


    In my experience, that is only the case if the field has a value. If it is empty, the quotes are omitted.

    Thanks for using the LawsonGuru.com forums!
    John
    Jeff Wardzala
    Consultant
    Self Employed
    Basic Member
    (27 points)
    Basic Member
    Posts:9


    Send Message:

    --
    09/25/2015 10:04 AM
    Quotes are omitted from fields without a value regardless of whether it is alpha or numeric.
    jaherb
    Private
    Private
    Veteran Member
    (471 points)
    Veteran Member
    Posts:163


    Send Message:

    --
    10/09/2015 11:02 AM
    I have to acknowledge that I incorrectly responded to this question and John and Jeff are 100% correct. The double quotes are stripped off of empty fields. I just recently ran into this situation as well and wanted to update with my solution to this. In order to achieve the double quotes on empty fields, after I closed the file and before the program ended, I pushed the file through a 'sed' command to replace the ,, with ,"", values.

    I had the program working fine with replacing the ,, with ," ", (space in between) but the vendor could not accept this space. So this is where the sed command came in nicely. The 'sed' commands are very powerful, but also very confusing as it looks like just a series of special characters strung together.

    The 'sed' command was place in the logic, as (sed 's/,,/,"",/g'). This worked fine.. however I then ran into the situation where there could be two null fields together and it would only catch the first set of ,, and replace that. So I would end up going from ,,, to ,"",,

    Since that was not the desired result, after some research and talking to a Unix guru friend of mind, I ended up created a Unix script and call that from within the program. Since I had the unique situation with multiple empty fields together, I created a sed file with the sed commands embedded in it and then executed that from within the Unix script.

    The sed command is set up to run the output file through 4 times checking for ,, occurrences as well as checking the first and last field positions in each record.

    Below is a snap shot of the the various parts of this, which work great.....

    PD

    INITIALIZE WS-CMD-STRING.
    *
    * The following sets up and executes a Unix Script that will
    * read the extract created and format empty fields in the csv

    * file to meet this requirement. They cannot accept null

    * value fields without the double quotes. This script will
    * replace ,, fields with ,"", values
    *
    STRING
    "/lawson/law/prod/" DELIMITED BY SIZE

    "hrscripts/SCRIPTNAME.ksh" DELIMITED BY SIZE

    INTO WS-CMD-STRING.
    CALL "system" USING WS-CMD-STRING GIVING WS-RETURN-PRM.
    DISPLAY " ".
    IF (WS-RETURN-PRM= 0)
    DISPLAY "FILE sed COMMAND SUCCESSFUL"
    ELSE
    DISPLAY "FILE sed COMMANY NOT SUCCESSFUL"
    DISPLAY "RETURN PARM = " WS-RETURN-PRM
    END-IF.

    Unix Script

    #!/usr/bin/ksh
    day=`date +%Y%m%d`

    sed -f /lawson/law/prod/hrscripts/filename.txt /lawson/law/prod/work/DIRECOTRY/filename > /lawson/law/prod/work/DIRECTORY/filename_$day.txt


    SED file

    s;||;|""|;g
    s;||;|""|;g
    s;||;|""|;g
    s;||;|""|;g
    s;^|;""|;g
    s;|$;|"";g

    Hope this helps anyone that runs into this issue.....
    Vijay S
    Lawson Developer
    Independent consultant
    Veteran Member
    (389 points)
    Veteran Member
    Posts:167


    Send Message:

    --
    11/19/2015 7:41 AM
    You will never get quotes for an empty field . Following is the o/p from the actual CSV file which I generated today itself....... If you look carefully there are no Quotes in the empty fields ........
    "0073",20140219,,"000001692","00","0000008100.0000"
    "0085",20140106,,"000380662","00","0000008400.0000"
    "0026",20140106,,"000380681","00","0000008600.0000"
    jaherb
    Private
    Private
    Veteran Member
    (471 points)
    Veteran Member
    Posts:163


    Send Message:

    --
    11/19/2015 9:35 AM
    VJS... if you use the method that I described above your response, it works just fine. You are correct with "out of the box" csv routines.

    Jim
    You are not authorized to post a reply.