Highlighted
Absent Member.
Absent Member.
1559 views

CSV file output

Jump to solution

What is the easiest way to create and write a CSV file?  Is it necessary to use Excel?

0 Likes
4 Solutions

Accepted Solutions
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution

Introducing an Excel library call would add a level of complexity that is not needed in this instance.  I will typically use a line sequential file and string all the fields together into a record. 

In this example, I have CNS-FIELD-DELIMITER as a constant equal to a "|" character.

            SELECT CSN-FLAT-FILE
               ASSIGN TO RANDOM "/sort/CSNInvFile"
               ORGANIZATION IS LINE SEQUENTIAL
               ACCESS MODE  IS SEQUENTIAL
              FILE STATUS  IS FILE-STATUS.
        FD  CSN-FLAT-FILE
            RECORD IS VARYING IN SIZE
              FROM 1 TO 300 CHARACTERS.
       01  CSN-FLAT-FILE-RECORD.
            03 CFF-LINE                     PIC  X(300).

 

        78  CNS-FIELD-DELIMITER             VALUE "|".
           INITIALIZE CFF-LINE.

           STRING
             CNS-SUPPLIER-ID     DELIMITED BY SIZE,
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-PROD-NUM         DELIMITED BY " ",
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-AVAIL-ALPHA      DELIMITED BY " ",
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-BO-QTY-ALPHA     DELIMITED BY " ",
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-PO-QTY-ALPHA     DELIMITED BY " ",
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-CONV-DATE        DELIMITED BY " ",
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-DISCONTINUED-SW  DELIMITED BY SIZE,
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-DESCRIPTION      DELIMITED BY SIZE
           INTO
             CFF-LINE
END-STRING.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
If you would want to have this opened upon completion you can do a simple c$system call out with a "start path\created_file_name.csv" If Excel exists and is associated it will then open in Excel or may just open in notepad, which is fine with this file type.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
Unless you get tired of calculating the size of the records manually, then you can use a utility program like I did called XVARCHAR. XVARCHAR accepted strings of up to 32,000 bytes, calculated the size of the record, and then created the variable length records based upon the length needed for each string sent to it. I created XML, CSV, SQL, and other files letting the computer do the calculation for me. I sent max string length to XVARCHAR too and included a flag that accepted "O" for open output, "E" for open extend, and "D" for delete. Using variable length saves disk space.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
For alpha-numeric fields that have spaces inside them like "Bob Jones" you'll want to make sure to left justify them first with
C$JUSTIFY and then use INSPECT [field name] REPLACING TRAILING SPACES BY "_". Then you can delimit by "_".

View solution in original post

11 Replies
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution

Introducing an Excel library call would add a level of complexity that is not needed in this instance.  I will typically use a line sequential file and string all the fields together into a record. 

In this example, I have CNS-FIELD-DELIMITER as a constant equal to a "|" character.

            SELECT CSN-FLAT-FILE
               ASSIGN TO RANDOM "/sort/CSNInvFile"
               ORGANIZATION IS LINE SEQUENTIAL
               ACCESS MODE  IS SEQUENTIAL
              FILE STATUS  IS FILE-STATUS.
        FD  CSN-FLAT-FILE
            RECORD IS VARYING IN SIZE
              FROM 1 TO 300 CHARACTERS.
       01  CSN-FLAT-FILE-RECORD.
            03 CFF-LINE                     PIC  X(300).

 

        78  CNS-FIELD-DELIMITER             VALUE "|".
           INITIALIZE CFF-LINE.

           STRING
             CNS-SUPPLIER-ID     DELIMITED BY SIZE,
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-PROD-NUM         DELIMITED BY " ",
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-AVAIL-ALPHA      DELIMITED BY " ",
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-BO-QTY-ALPHA     DELIMITED BY " ",
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-PO-QTY-ALPHA     DELIMITED BY " ",
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-CONV-DATE        DELIMITED BY " ",
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-DISCONTINUED-SW  DELIMITED BY SIZE,
             CNS-FIELD-DELIMITER DELIMITED BY SIZE,

             WF-DESCRIPTION      DELIMITED BY SIZE
           INTO
             CFF-LINE
END-STRING.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
If you would want to have this opened upon completion you can do a simple c$system call out with a "start path\created_file_name.csv" If Excel exists and is associated it will then open in Excel or may just open in notepad, which is fine with this file type.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
Unless you get tired of calculating the size of the records manually, then you can use a utility program like I did called XVARCHAR. XVARCHAR accepted strings of up to 32,000 bytes, calculated the size of the record, and then created the variable length records based upon the length needed for each string sent to it. I created XML, CSV, SQL, and other files letting the computer do the calculation for me. I sent max string length to XVARCHAR too and included a flag that accepted "O" for open output, "E" for open extend, and "D" for delete. Using variable length saves disk space.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
For alpha-numeric fields that have spaces inside them like "Bob Jones" you'll want to make sure to left justify them first with
C$JUSTIFY and then use INSPECT [field name] REPLACING TRAILING SPACES BY "_". Then you can delimit by "_".

View solution in original post

Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
For this application, I knew what was going to be in those fields. So, I avoided the extra step of the INSPECT...REPLACING.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
Also, if you have version 10.2 or later, the TRIM intrinsic function can simplify the handling of trailing spaces.
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
I thought so, I was just mentioning it for other csv's.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
I wish they had given me 9 and then 10. I was stuck using 8, but that's not as bad as the other 2 developers who were stuck using 5.1. Notice how you can multiply the 5.1 by 2 to get the 10.2? LOL. Oh well. I'm no longer working at "Never Upgrade Inc".
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
Ouch. While I approve of any version, from the inside, a lot of good things happened in 6+. The generated code is just so much better starting with the 6.0 descriptor enhancements.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: CSV file output

Jump to solution
I think that I will be looking at upgrading us to 10.2 next year. There are a couple things that are nice in there including this and C$PDF. Not having to shell out a PDF conversion would be nice.
0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

RE: CSV file output

Jump to solution
I might as well throw in my favorite way. I would use XML Extensions and create the CSV using XSLT on the output.

Tom Morrison
Consultant

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.