Hit exactly the same problem attempting to import CSV files onto the HP a few years ago.
The problem is that Excel uses more than just a comma to delimit columns in CSV files - it can use commas and quote marks combined.
My first experience of this problem was when an application that had been working for ages suddenly reported that it could no longer import the file. What had happened is that the user had opened the CSV file in an older version of EXCEL and then saved it - and the format of the records in the file had changed from
A,123,FOO,BAR,,ETC,
to
"A","123","FOO","BAR","","ETC",
In this particular instance, the problem was resolved by simply stripping out the quote marks in the COBOL program.
However, if you experiment with CSV files in EXCEL and then view the same file in notepad, you'll see how EXCEL behaves when any of the columns actually contain a quote or a comma. This may not be a problem for you, but I mention it just as a warning. For example saving the following in EXCEL
A,123,F"O,BAR,,ETC,
Looks like the following in notepad:
A,123,"F""O",BAR,,ETC
So parsing the record can be very very tricky !
Best of luck.
-----Original Message-----
From: HP-3000 Systems Discussion [mailto:[log in to unmask]] On Behalf Of Olav Kappert
Sent: 08 August 2013 01:32
To: [log in to unmask]
Subject: Re: [HP3000-L] Cobol: Import CSV text file variable length numeric field
No problem with unstring. Just use the length filed and determine if the length = 0.
You can create a loop and keep finding the ",". By the way, determine the record length and set the last byte+1 to "~" so that the unstring can determine the end of record.
Olav.
Tony Girgenti wrote:
>Thanks to all for their replies.
>
>I did not explain this in my original post, but I have no control over the format of the input CSV file. The file is coming from a bank and we have to use what they give us.
>
>I would like to use the UNSTRING command and redefine the two fields that I need which are check number(Reference Number) and check amount(Amount). Here is a sample of the first two records in the file:
>
>"Date","ABA Number","Currency","Account Number","Account Name","Description","BAI Code","Amount","Serial Number","Reference Number","Detail"
>"06/03/2013","999999999","USD","9999999999","XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX","Check Paid","999","-39,400.65","99999999","999999","CHECK <br>"
>
>Of course I replaced all of the numbers with nines and the Account Name with X's.
>
>I realize I need to read past the first record which is a header.
>
>In using the UNSTRING command, I can use "," OR "<br>" as the DELIMITERS and it should work.
>
>The Amount has a dash in front of it, so I can INSPECT it REPLACING all SPACES and "-" WITH ZERO.
>
>The only problem I have now, is that one of the records looks like this:
>"06/03/2013","999999999","USD","9999999999","XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX","Check Paid","999","-39,400.65","99999999",,"CHECK <br>"
>
>As you can see, the Reference Number is empty with no quotes around it. I don't know if this is a common occurrence with this file. I have not contacted the bank yet about that.
>
>I'm not sure of how to handle that field with the UNSTRING command, or if there is some other method I should use.
>
>Thanks,
>Tony
>
>* To join/leave the list, search archives, change list settings, *
>* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *
>
>
>
* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *
The contents of this email are confidential to the intended recipient and may not be disclosed. Although it is believed that this email and any attachments are virus free, it is the responsibility of the recipient to confirm this.
Details of Smith & Williamson group companies and Nexia Smith & Williamson Audit Limited and their regulators (where applicable), can be found at this URL
http://www.smith.williamson.co.uk/disclosure
* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *
|
