double quote in empty field

Sort:
You are not authorized to post a reply.
Author
Messages
Srini Rao
Veteran Member
Posts: 148
Veteran Member
    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
    Veteran Member
    Posts: 164
    Veteran Member
      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
      Senior Member
      Posts: 3348
      Senior Member
        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.co...lds-in-4gl-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
        Veteran Member
        Posts: 164
        Veteran Member
          By default, double quotes will surround every alpha numeric field, unless it is changed in the csv attributes form.
          John Henley
          Senior Member
          Posts: 3348
          Senior Member
            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
            Basic Member
            Posts: 9
            Basic Member
              Quotes are omitted from fields without a value regardless of whether it is alpha or numeric.
              jaherb
              Veteran Member
              Posts: 164
              Veteran Member
                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
                Veteran Member
                Posts: 174
                Veteran Member
                  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
                  Veteran Member
                  Posts: 164
                  Veteran Member
                    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.