Loading a CSV file using a Cobol program

[jewilson2]

I have a program that reads a csv file and loads it to an Oracle database. The program works fine using a sample file that I created in Excel. However, in the real world, it needs to use a file extracted from another system. This extract encases every fields' value in quotes " ". When trying to read this file the program fails with the following error:
**** End of File Attributes For File: MAS90CSV ****
CSV Read Error: Bad Format on Record Nbr 0

If I take this file and open it in excel, the save it (without making changes), it removes the quotes and the program will load it fine.

My questions are: Why do the quotes blow the program up?
and Is there anything I can do in the program itself to "handle" the field type identifiers (quotes)? I tried making all of the fields=Alpha in the WS and it made no difference.

Thanks in advance,
JW

 

[Dimandja]

Your question already assumes that the quotes are to blame. But there could be nmore to this that you haven't told us. Take time to fill us in:

1. COBOL version/model?

2. What's the "real world" other system? And why are you testing your real-world-bound code in a non-real world system anyway?

3. Code sample? Especially the file definition and the IO statements. How come your program is CSV aware? Are you mis/using special CSV IO routines? Written by what vendor?

4. Data sample? A couple of records should do.

Dimandja

 

[jewilson2]

Sorry. I had assumed it was the quotes because the same file without the quotes processes fine.

1. Microfocus Cobol

2. The other system is irrelevant. It exports a csv file that I would like to read and load to another system. I wrote and tested the load program using a csv file that I created simply to tweak the program. Now that the extract from the other system is complete, I am re-testing using that file. Again, the only difference is that this file encases the fields in quotes.

3. PERFORM 800-READCSV-MAS90CSV.
PERFORM 910-AUDIT-BEGIN.
PERFORM 800-CREATE-GLTRANSREL.
MOVE CSV-RUN-GROUP TO GTR-RUN-GROUP.
005600 MOVE CSV-SEQ-NUMBER TO GTR-SEQ-NUMBER.
005700 MOVE CSV-COMPANY TO GTR-COMPANY.
005800 MOVE CSV-OLD-COMPANY TO GTR-OLD-COMPANY.
005900 MOVE CSV-OLD-ACCT-NBR TO GTR-OLD-ACCT-NBR.
006000 MOVE CSV-SOURCE-CODE TO GTR-SOURCE-CODE.
006100 MOVE CSV-DATE TO GTR-DATE.
006200 MOVE CSV-REFERENCE TO GTR-REFERENCE.
006300 MOVE CSV-DESCRIPTION TO GTR-DESCRIPTION.
006400 MOVE CSV-CURRENCY-CODE TO GTR-CURRENCY-CODE.
006500 MOVE CSV-UNITS-AMOUNT TO GTR-UNITS-AMOUNT.
006600 MOVE CSV-TRAN-AMOUNT TO GTR-TRAN-AMOUNT.
006700 MOVE CSV-BASE-AMOUNT TO GTR-BASE-AMOUNT.
006800 MOVE CSV-BASERATE TO GTR-BASERATE.
006900 MOVE CSV-SYSTEM TO GTR-SYSTEM.
007000 MOVE CSV-PROGRAM-CODE TO GTR-PROGRAM-CODE.
007100 MOVE CSV-AUTO-REV TO GTR-AUTO-REV.
007200 MOVE CSV-POSTING-DATE TO GTR-POSTING-DATE.
007300 MOVE CSV-ACTIVITY TO GTR-ACTIVITY.
007400 MOVE CSV-ACCT-CATEGORY TO GTR-ACCT-CATEGORY.
007500 MOVE CSV-DOCUMENT-NBR TO GTR-DOCUMENT-NBR.
007600 MOVE CSV-TO-BASE-AMT TO GTR-TO-BASE-AMT.
007700 MOVE CSV-EFFECT-DATE TO GTR-EFFECT-DATE.
007800 MOVE CSV-JRNL-BOOK-NBR TO GTR-JRNL-BOOK-NBR.
007900 MOVE CSV-JBK-SEQ-NBR TO GTR-JBK-SEQ-NBR.
008000 MOVE CSV-NEGATIVE-ADJ TO GTR-NEGATIVE-ADJ.
008100 MOVE CSV-SEGMENT-BLOCK TO GTR-SEGMENT-BLOCK.
008200 MOVE CSV-RPT-AMOUNT-1 TO GTR-RPT-AMOUNT-1.
008300 MOVE CSV-RPT-RATE-1 TO GTR-RPT-RATE-1.
008400 MOVE CSV-RPT-ND-1 TO GTR-RPT-ND-1.
008500 MOVE CSV-RPT-AMOUNT-2 TO GTR-RPT-AMOUNT-2.
008600 MOVE CSV-RPT-RATE-2 TO GTR-RPT-RATE-2.
008700 MOVE CSV-RPT-ND-2 TO GTR-RPT-ND- PERFORM 820-STORE-GLTRANSREL.
PERFORM 925-AUDIT-END.
PERFORM 800-READCSV-MAS90CSV.

These routines are a specific function of the vendor's (Lawson Software).

4.
"MAS901003","1","0010","001010000","0000000000","M9","10012003","","POINT OF SALE ENTRY - 10/01/03","","","1766.58","","","GL","","","10312003","","","","","","","","","","","","","","","","","",""
"MAS901003","2","0010","001010000","0000000000","M9","10012003","","POINT OF SALE ENTRY - 10/01/03","","","429.7","","","GL","","","10312003","","","","","","","","","","","","","","","","","",""
"MAS901003","3","0010","001010000","0000000000","M9","10022003","","POINT OF SALE ENTRY - 10/02/03","","","1285.63","","","GL","","","10312003","","","","","","","","","","","","","","","","","",""

I hope this is the information you are looking for. Thanks for the reply

 

[webrabbit]

Without the DATA DIVISION, this code is meaningless.

 

[Dimandja]

And the IO statements are still missing.

 

[jewilson2]

Sorry guys....apparently I don't know what it is you're looking for. (as if you couldn't already tell) I'm a n00b to Cobol. Perhaps the statements I'm using are proprietary to the vendor's s/w and are not standard IO statments? What am I missing here?

Thanks for trying to help,
JW

 

[Dimandja]

To read a file in COBOL you use the READ statement:

READ <filename>

To describe a file you use:

FD <filename>...
01 record-name...

To assign a file you do:

SELECT <filename> ASSIGN TO <diskfile?>
ORGANIZATION IS...

Do you have any such thing in your program?

And, yes, you are most likely using some canned (vendor?) routine to read CSV files. In which case, the routine does not support something (quotes?) that your system is creating. There may be a way to tell it to agree with quotes if we know what is being used.

Dimandja

 

[etom]

What is being asked for is probably the code in 800-READCSV-MAS90CSV, specifically your Read statements. IO stands for input/output statements.

Another problem may be with the manner that the original application writes out the CSV file and what it uses to represent an end of record. Cobol (depending on how your &quot;SELECT&quot; statement is defined, expects certain characteristics to be present in a file. Your error message looks like to me that the Read statement may be having trouble locating end of record. By loading the file into EXCEL and then re-exporiting it Excel could be changing the end of record indicators.

We are asking for Data Division definitions to see exactly how you are defining your file and it's layout.

Is it possible the original file is comming from a UNIX application or an application that output files in UNIX format. There are several ways to create files on a PC each with different characteristics. Many editors and applications (like EXCEL) have the ability to deal with many of these formats and make it transparent to the user however, when writing you own program, you must know the details of your file. This is why we asked for the name of the creating application.

etom.

 

[jewilson2]

Ok. I think etom is correct...the IO statements are in the code of the &quot;canned&quot; API's from the vendor. I have no way of getting that code that I know of.

As for the originating application, it is a 3rd party GL application (MAS90) doing an extract of GL transactions for a given date range. That vendor has written the extract for the file I'm using, and is basically treating every field as TEXT. In my data definition for the INPUT, there are Alpha and Numeric fields. Perhaps this is the problem...writing Alpha data to Numeric fields.

I changed all of my field types to Alpha and it still failed. Next, the vendor will change the extract to only put quotes around the true Alpha fields and give that a shot. If that doesn't work, he will remove the quotes all together.

As for the record terminator..I have the option to select CR, NL, or Hex 0D0A. I have tried all 3 to no avail.

The data definition for the input file is as follows:
RUN-GROUP Alpha 15
SEQ-NUMBER Numeric 6.0
COMPANY Numeric 4.0
OLD-COMPANY Alpha 35
OLD-ACCT-NBR Alpha 25
SOURCE-CODE Alpha 2
DATE yyyymmdd 8
REFERENCE Alpha 15
DESCRIPTION AlphaLC 30
CURRENCY-CODE Alpha 5
UNITS-AMOUNT Signed 15.2
TRAN-AMOUNT Signed 15.2
BASE-AMOUNT Signed 15.2
BASERATE Signed 12.6
SYSTEM Alpha 2
PROGRAM-CODE Alpha 5
AUTO-REV Alpha 1
POSTING-DATE yyyymmdd 8
ACTIVITY Alpha 15
ACCT-CATEGORY Alpha 5
DOCUMENT-NBR Alpha 27
TO-BASE-AMT Signed 15.2
EFFECT-DATE yyyymmdd 8
JRNL-BOOK-NBR Alpha 12
MX-VALUE1 Alpha 32
MX-VALUE2 Alpha 32
MX-VALUE3 Alpha 32
JBK-SEQ-NBR Numeric 10.0
NEGATIVE-ADJ Alpha 1
SEGMENT-BLOCK Alpha 103
RPT-AMOUNT-1 Signed 15.2
RPT-RATE-1 Signed 12.6
RPT-ND-1 Numeric 1.0
RPT-AMOUNT-2 Signed 15.2
RPT-RATE-2 Signed 12.6
RPT-ND-2 Numeric 1.0

Thanks again for the handholding.

 

[Dimandja]

Let's keep it simple. If the file is CSV, the extract should be done with no quotes at all. The only thing you need to be sure of is that there are no embedded commas in any field.

Dimandja

 

[jewilson2]

I'm with you...keep it simple.

I was just trying to learn something...and attempt to handle the file with the commas.

I'm pushing the vendor to remove the quotes now.

Thanks again,
JW

 

[webrabbit]

It is easier for people to help you if you post the entire COBOL source. Documentation does not tell anyone how the code is implemented.