RE: Using CPYFRMIMPF

This has been an interesting discussion. I MUCH prefer *TAB as a delimiter.

Right now, I'm dealing with a pipe delimited file from our corporate middleware. Originally, I had *DBLQUOTE for string delimiters (the default). Then, we got a record with a data element for 60" (inch) Gizmo. Well, the double quote hosed that up. So, I made string delimiter *NONE. Then, we got a data element that had a pipe embedded in the data. So, that hosed up too. Fields get split in the middle and everything gets pushed down one field. Haven't come up with a solution yet other than to error out my import once I hit bad numerics or dates further along in the record..

The sad part is the data is supplied as JSON from the vendor but our corporate middleware translates it to pipe delimited as the lowest common denominator for the various ERP's they have (via acquisitions). I said "Just give me the JSON." Nope, no can do... UGH.



-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of John Yeung
Sent: Thursday, June 3, 2021 10:16 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Using CPYFRMIMPF

On Thu, Jun 3, 2021 at 8:48 AM Howie, Bill <BHowie@xxxxxxxxxxxxxxxx> wrote:

I'm working on importing a CSV file to the IBM I using CPYFRMIMPF. The file started out as an XLS file that I saved as a CSV file, and then did the CPYFRMIMPF. Is there a foolproof way to handle the alpha fields that have commas as part of the data? I know that saving the file as a CSV causes the data with commas in it to have double quotes around it. These records all fall out into the error file that I'm using with the command. Thoughts? Any info is most appreciated.

Well, someone suggested maybe you have to adjust the parameters on the
CPYFRMIMPF command. If there is some combination of settings that
works, then that is of course a quick and easy option.

Someone else suggested uploading the data directly from the Excel
workbook using the data transfer capabilities that come with ACS.
That's another fairly convenient and perhaps more robust option.

But I think this is a great excuse/opportunity to get your feet wet
with Python, which can handle CSVs or Excel workbooks (.xls or .xlsx).
For me, that is the most foolproof way to handle getting data out of
Excel and into IBM i database files.

John Y.