Scott Klement's Working With CSV Data in RPG
-
- New User
- Posts: 7
- Joined: Wed Jun 16, 2021 2:47 pm
- First Name: cheryl
- Last Name: carpenter
- Company Name: K&M Associates
- Phone: 4017842427
- Address 1: 425 Dexter Street
- City: Providence
- State / Province: Rhode Island
- Zip / Postal Code: 02907
- Country: United States
- Contact:
Scott Klement's Working With CSV Data in RPG
Has anyone had the opportunity to modify the program so that it updates a field in the .csv file?
Any ideas or help appreciated.
Thank you,
Cheryl
Any ideas or help appreciated.
Thank you,
Cheryl
-
- Experienced User
- Posts: 2711
- Joined: Wed Aug 01, 2012 8:58 am
- First Name: Scott
- Last Name: Klement
- Company Name: Profound Logic
- City: Milwaukee
- State / Province: Wisconsin
Re: Scott Klement's Working With CSV Data in RPG
Hi Cheryl,
Welcome to the forums!
I've written a lot of stuff over the years, and I'm not quite sure which of my CSV examples you're referring to. Do you have a link to where you found it so I can refresh my memory?
Welcome to the forums!
I've written a lot of stuff over the years, and I'm not quite sure which of my CSV examples you're referring to. Do you have a link to where you found it so I can refresh my memory?
-
- New User
- Posts: 7
- Joined: Wed Jun 16, 2021 2:47 pm
- First Name: cheryl
- Last Name: carpenter
- Company Name: K&M Associates
- Phone: 4017842427
- Address 1: 425 Dexter Street
- City: Providence
- State / Province: Rhode Island
- Zip / Postal Code: 02907
- Country: United States
- Contact:
Re: Scott Klement's Working With CSV Data in RPG
Wow, thank you so much for the personal reply: Yes, it is: https://www.scottklement.com/csv/article.html
I got the sample programs loaded on my iSeries and even got the report to print my .csv file's fields.
My goal, and my intense struggle! is to take the attached .csv file, update column N with the on-hand inventory from my JDE file on my iSeries. CPYFRMIMPF does not work due to the carriage returns, etc. I have to return the file to the user who downloaded it from the website in exactly the same format as it is.
Appreciate your help Scott!
Cheryl
I got the sample programs loaded on my iSeries and even got the report to print my .csv file's fields.
My goal, and my intense struggle! is to take the attached .csv file, update column N with the on-hand inventory from my JDE file on my iSeries. CPYFRMIMPF does not work due to the carriage returns, etc. I have to return the file to the user who downloaded it from the website in exactly the same format as it is.
Appreciate your help Scott!
Cheryl
- Attachments
-
- inventorybefore.csv
- (54.88 KiB) Downloaded 1251 times
-
- Experienced User
- Posts: 2711
- Joined: Wed Aug 01, 2012 8:58 am
- First Name: Scott
- Last Name: Klement
- Company Name: Profound Logic
- City: Milwaukee
- State / Province: Wisconsin
Re: Scott Klement's Working With CSV Data in RPG
You're welcome! (When you said you were pleased by a "personal reply" that made me feel special!)
Generally speaking, you can't "update" a CSV file. They aren't databases... if you change the length of any data, the whole remainder of the file has to be shifted over that many bytes. So an "update" would essentially involve rewriting the whole file after the point where you made the change. The way people usually handle this sort of thing is by reading through the entire input file, looping through each record, and copying it to a new record in a new file. If there's a change needed to that record, you write out the changed one.
I realize this seems *** when you're used to databases -- indeed -- that's one of the reasons databases are so popular, because they are so easy to do stuff like updates, et al.
I don't understand your comment about CPYFRMIMPF not working for you. It seems to me that this would work okay, you could use CPYFRMIMPF to copy your CSV into a database, then make your updates, and use CPYTOIMPF to replace the CSV with the new version. Can you explain why that wouldn't work for you?
Currently the CSVR4 tool does not have any options to output CSVs, it is a read-only tool. However, I will take it as a suggestion to add writing capabilities. (It sounds like this could be useful to some people.)
Generally speaking, you can't "update" a CSV file. They aren't databases... if you change the length of any data, the whole remainder of the file has to be shifted over that many bytes. So an "update" would essentially involve rewriting the whole file after the point where you made the change. The way people usually handle this sort of thing is by reading through the entire input file, looping through each record, and copying it to a new record in a new file. If there's a change needed to that record, you write out the changed one.
I realize this seems *** when you're used to databases -- indeed -- that's one of the reasons databases are so popular, because they are so easy to do stuff like updates, et al.
I don't understand your comment about CPYFRMIMPF not working for you. It seems to me that this would work okay, you could use CPYFRMIMPF to copy your CSV into a database, then make your updates, and use CPYTOIMPF to replace the CSV with the new version. Can you explain why that wouldn't work for you?
Currently the CSVR4 tool does not have any options to output CSVs, it is a read-only tool. However, I will take it as a suggestion to add writing capabilities. (It sounds like this could be useful to some people.)
-
- New User
- Posts: 7
- Joined: Wed Jun 16, 2021 2:47 pm
- First Name: cheryl
- Last Name: carpenter
- Company Name: K&M Associates
- Phone: 4017842427
- Address 1: 425 Dexter Street
- City: Providence
- State / Province: Rhode Island
- Zip / Postal Code: 02907
- Country: United States
- Contact:
Re: Scott Klement's Working With CSV Data in RPG
Well.. you are special!
I put together a few screen prints in the attached word doc. that demonstrate some of my issues. I appreciate you taking a look at this this. Thank you so much again!
Again my goal is to update the inventory column and give the file back the same exact way it is to the user.
Cheryl
I put together a few screen prints in the attached word doc. that demonstrate some of my issues. I appreciate you taking a look at this this. Thank you so much again!
Again my goal is to update the inventory column and give the file back the same exact way it is to the user.
Cheryl
- Attachments
-
- F5542109 DDS.txt
- (9.61 KiB) Downloaded 1476 times
-
scott1.docx
- (428.96 KiB) Downloaded 1322 times
-
- Experienced User
- Posts: 2711
- Joined: Wed Aug 01, 2012 8:58 am
- First Name: Scott
- Last Name: Klement
- Company Name: Profound Logic
- City: Milwaukee
- State / Province: Wisconsin
Re: Scott Klement's Working With CSV Data in RPG
Hi Cheryl,
First problem, Notepad++ shows 7.5” using the Windows-1252 "curly quote" character. This character does not exist in EBCDIC, so you would either need to translate the character to a straight double quote character like " or process the file in Unicode. This is the reason that DBU is showing it incorrectly -- doesn't seem to have anything to do with a flaw in CPYFRMIMPF.
Second problem... You say questions marks are wrong in 'around an animal.</p>??<p><span><span><span>This stone has grounding energy.' I respectfully disagree... it is Notepad++ that seems to be interpreting this wrong. (And, I bet CSVR4 would have the same problem.) The file uses the typical Windows convention of ending each line with Carriage Return followed by Line Feed (CRLF, or hex 0d0a in ASCII). The two question marks are additional line feed (hex 0a) characters, but without an 0d in front of them, they should not be treated as the end of a record! If you look at the format of the data. The <p><span> stuff appears to be part of the same (description) field and should NOT be treated as the handleId of the next record, and that appears to be what Notepad++ is doing. If you look at the database data in hex (instead of text) I think you'll find that after converting to EBCDIC its actually x'25' (or maybe x'15') so they aren't actually question marks, the issue is simply that your 5250 terminal doesn't know how to display them. If it were me, I'd want them to remain linefeed characters so when I wrote it back out it would preserve the format as it was... so CPYFRMIMPF seems to be doing the right thing here, as well.
It seems to me you'd have the same or worse problems with CSVR4. Have you tried it and it works better? Can you explain how you are using it that is helping? I guess maybe you're doing some preprocessing of the file or else processing it entirely in Unicode? More info would be helpful.
First problem, Notepad++ shows 7.5” using the Windows-1252 "curly quote" character. This character does not exist in EBCDIC, so you would either need to translate the character to a straight double quote character like " or process the file in Unicode. This is the reason that DBU is showing it incorrectly -- doesn't seem to have anything to do with a flaw in CPYFRMIMPF.
Second problem... You say questions marks are wrong in 'around an animal.</p>??<p><span><span><span>This stone has grounding energy.' I respectfully disagree... it is Notepad++ that seems to be interpreting this wrong. (And, I bet CSVR4 would have the same problem.) The file uses the typical Windows convention of ending each line with Carriage Return followed by Line Feed (CRLF, or hex 0d0a in ASCII). The two question marks are additional line feed (hex 0a) characters, but without an 0d in front of them, they should not be treated as the end of a record! If you look at the format of the data. The <p><span> stuff appears to be part of the same (description) field and should NOT be treated as the handleId of the next record, and that appears to be what Notepad++ is doing. If you look at the database data in hex (instead of text) I think you'll find that after converting to EBCDIC its actually x'25' (or maybe x'15') so they aren't actually question marks, the issue is simply that your 5250 terminal doesn't know how to display them. If it were me, I'd want them to remain linefeed characters so when I wrote it back out it would preserve the format as it was... so CPYFRMIMPF seems to be doing the right thing here, as well.
It seems to me you'd have the same or worse problems with CSVR4. Have you tried it and it works better? Can you explain how you are using it that is helping? I guess maybe you're doing some preprocessing of the file or else processing it entirely in Unicode? More info would be helpful.
-
- Experienced User
- Posts: 2711
- Joined: Wed Aug 01, 2012 8:58 am
- First Name: Scott
- Last Name: Klement
- Company Name: Profound Logic
- City: Milwaukee
- State / Province: Wisconsin
Re: Scott Klement's Working With CSV Data in RPG
Hi Cheryl,
I was thinking about this some more, and decided to throw together an example. The first thing I would do, is change your file to use Unicode fields. In DDS, that's done by setting the data type to G=Graphic, and setting a Unicode CCSID. For this purpose, I'm going to use CCSID 13488 (which is UCS-2... an older standard, but you don't need anything new for this simple example,and it's the default CCSID for Unicode in RPG.)
Now that all of the fields are Unicode, it should preserve all of the special characters. You can use CPYFRMIMPF to copy the CSV into the Unicode fields.
Since fields that have no data will be set to null, your RPG code should use ALWNULL(*USRCTL) to allow your program to work with the null indicators. All of the fields of the PF should be defined as RPG data type UCS2 (or type 'C' in fixed format). So your code to run through the file and update the inventory in column N would look something like this (I have no idea what the logic to look up the inventory value would be).
Once you're done, you'd just use CPYTOIMPF to convert the file back to CSV format:
The data is processed entirely in UCS-2 Unicode -- which has a curly quote character. Since the data is never converted to EBCDIC, this should not have any problems with special characters such as the "curly quotes" that you showed in your example. They will simply be preserved as-is. However, stuff like viewing it in a debugger in RPG, or anything that involves converting it to EBCDIC will always result in these characters looking funny.
If for some reason you'd like to get rid of those curly quote characters, you can use the %XLATE BIF to convert them while the data is still in Unicode. Then you can convert them to straight quotes which will work in just about any character set. Once so converted, you could move it to an EBCDIC field without problems -- but, the data won't be identical to your input, of course, since you're changing this stuff. So whether you do this is up-to-you.
Hope that helps
I was thinking about this some more, and decided to throw together an example. The first thing I would do, is change your file to use Unicode fields. In DDS, that's done by setting the data type to G=Graphic, and setting a Unicode CCSID. For this purpose, I'm going to use CCSID 13488 (which is UCS-2... an older standard, but you don't need anything new for this simple example,and it's the default CCSID for Unicode in RPG.)
Code: Select all
A****************************************************************
A* FILE: F5542109 *
A* PROGRAMMER: C. Carpenter *
A* DATE CREATED: 05/04/21 *
A* JDE TASK#: 2265 *
A* *
A* FUNCTION: Inventory Upload File From Spreadsheet - *
A* Hope & Co. *
A* *
A****************************************************************
A* Program Revision Log
A* --------------------
A* JDE
A* Date Programmer Task# Description
A* ------- ---------- ---- -------------------------------
XX01 A* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
A*****************************************************************
A R I5542109
A COLUMNA 50G ALWNULL CCSID(13488)
A COLUMNB 50G ALWNULL CCSID(13488)
A COLUMNC 50G ALWNULL CCSID(13488)
A COLUMND 1000G ALWNULL CCSID(13488)
A COLUMNE 1000G ALWNULL CCSID(13488)
A COLUMNF 200G ALWNULL CCSID(13488)
A COLUMNG 25G ALWNULL CCSID(13488)
A COLUMNH 50G ALWNULL CCSID(13488)
A COLUMNI 50G ALWNULL CCSID(13488)
A COLUMNJ 50G ALWNULL CCSID(13488)
A COLUMNK 50G ALWNULL CCSID(13488)
A COLUMNL 50G ALWNULL CCSID(13488)
A COLUMNM 50G ALWNULL CCSID(13488)
14 A COLUMNN 15G ALWNULL CCSID(13488)
A COLUMNO 50G ALWNULL CCSID(13488)
A COLUMNP 50G ALWNULL CCSID(13488)
A COLUMNQ 50G ALWNULL CCSID(13488)
A COLUMNR 50G ALWNULL CCSID(13488)
A COLUMNS 50G ALWNULL CCSID(13488)
A COLUMNT 50G ALWNULL CCSID(13488)
A COLUMNU 50G ALWNULL CCSID(13488)
A COLUMNV 50G ALWNULL CCSID(13488)
A COLUMNW 50G ALWNULL CCSID(13488)
A COLUMNX 50G ALWNULL CCSID(13488)
A COLUMNY 50G ALWNULL CCSID(13488)
A COLUMNZ 50G ALWNULL CCSID(13488)
A COLUMNA2 50G ALWNULL CCSID(13488)
A COLUMNB2 50G ALWNULL CCSID(13488)
A COLUMNC2 50G ALWNULL CCSID(13488)
30 A COLUMND2 50G ALWNULL CCSID(13488)
A COLUMNE2 50G ALWNULL CCSID(13488)
A COLUMNF2 50G ALWNULL CCSID(13488)
A COLUMNG2 50G ALWNULL CCSID(13488)
A COLUMNH2 50G ALWNULL CCSID(13488)
A COLUMNI2 1000G ALWNULL CCSID(13488)
A COLUMNJ2 50G ALWNULL CCSID(13488)
A COLUMNK2 1000G ALWNULL CCSID(13488)
A COLUMNL2 50G ALWNULL CCSID(13488)
A COLUMNM2 50G ALWNULL CCSID(13488)
A COLUMNN2 50G ALWNULL CCSID(13488)
A COLUMNO2 50G ALWNULL CCSID(13488)
A COLUMNP2 50G ALWNULL CCSID(13488)
A COLUMNQ2 50G ALWNULL CCSID(13488)
A COLUMNR2 50G ALWNULL CCSID(13488)
A COLUMNS2 50G ALWNULL CCSID(13488)
A COLUMNT2 50G ALWNULL CCSID(13488)
A COLUMNU2 50G ALWNULL CCSID(13488)
A COLUMNV2 50G ALWNULL CCSID(13488)
A COLUMNW2 50G ALWNULL CCSID(13488)
A COLUMNX2 50G ALWNULL CCSID(13488)
A COLUMNY2 50G ALWNULL CCSID(13488)
Code: Select all
CPYFRMIMPF FROMSTMF('/your-ifs-dir/inventoryBefore.csv')
TOFILE(your-library/F5542109)
RCDDLM(*CRLF)
RMVBLANK(*NONE)
Code: Select all
**free
ctl-opt dftactgrp(*no) alwnull(*USRCTL);
dcl-f F5542109 disk usage(*input:*update);
dcl-s recno int(10);
recno = 0;
setll *start F5542109;
read I5542109;
dow not %eof(F5542109);
recno += 1;
// -------------------------------------------------------
// do logic to get inventory, here.
// since I don't know what that is, I'm just going to set
// it to the same as the record number.
// -------------------------------------------------------
%nullind(columnN) = *off;
evalr columnN = %char(recno);
update I5542109;
read I5542109;
enddo;
*inlr = *on;
Code: Select all
CPYTOIMPF FROMFILE(your-lib/F5542109)
TOSTMF('/your-ifs-dir/niceOutput.csv')
STMFCCSID(1252)
RCDDLM(*CRLF)
STRDLM(*NONE)
RMVBLANK(*BOTH)
If for some reason you'd like to get rid of those curly quote characters, you can use the %XLATE BIF to convert them while the data is still in Unicode. Then you can convert them to straight quotes which will work in just about any character set. Once so converted, you could move it to an EBCDIC field without problems -- but, the data won't be identical to your input, of course, since you're changing this stuff. So whether you do this is up-to-you.
Code: Select all
**free
ctl-opt dftactgrp(*no) alwnull(*USRCTL);
dcl-f F5542109 disk usage(*input:*update);
dcl-s recno int(10);
dcl-c LEFT_SINGLE_QUOTE u'2018';
dcl-c RIGHT_SINGLE_QUOTE u'2019';
dcl-c LEFT_DOUBLE_QUOTE u'201C';
dcl-c RIGHT_DOUBLE_QUOTE u'201D';
dcl-c STRAIGHT_SINGLE_QUOTE u'0027';
dcl-c STRAIGHT_DOUBLE_QUOTE u'0022';
dcl-s fancyQuotes varucs2(4);
dcl-s straightQuotes varucs2(4);
fancyQuotes = LEFT_SINGLE_QUOTE + RIGHT_SINGLE_QUOTE
+ LEFT_DOUBLE_QUOTE + RIGHT_DOUBLE_QUOTE;
straightQuotes = STRAIGHT_SINGLE_QUOTE + STRAIGHT_SINGLE_QUOTE
+ STRAIGHT_DOUBLE_QUOTE + STRAIGHT_DOUBLE_QUOTE;
recno = 0;
setll *start F5542109;
read I5542109;
dow not %eof(F5542109);
recno += 1;
// -------------------------------------------------------
// if you want to replace the (windows-only) curly quotes
// with the more universal straight ones, just use XLATE
//
// You'll need to repeat the process for each column you
// want to do this for.
//
// If you don't want to change the quotes, just omit
// this logic.
// -------------------------------------------------------
if %nullind(columnC) = *off;
columnC = %xlate( fancyQuotes: straightQuotes: columnC);
endif;
if %nullind(columnD) = *off;
columnD = %xlate( fancyQuotes: straightQuotes: columnD);
endif;
if %nullind(columnE) = *off;
columnE = %xlate( fancyQuotes: straightQuotes: columnE);
endif;
if %nullind(columnF) = *off;
columnF = %xlate( fancyQuotes: straightQuotes: columnF);
endif;
if %nullind(columnI2) = *off;
columnI2 = %xlate( fancyQuotes: straightQuotes: columnI2);
endif;
if %nullind(columnJ2) = *off;
columnJ2 = %xlate( fancyQuotes: straightQuotes: columnJ2);
endif;
if %nullind(columnK2) = *off;
columnK2 = %xlate( fancyQuotes: straightQuotes: columnK2);
endif;
// -------------------------------------------------------
// do logic to get inventory, here.
// since I don't know what that is, I'm just going to set
// it to the same as the record number.
// -------------------------------------------------------
%nullind(columnN) = *off;
evalr columnN = %char(recno);
update I5542109;
read I5542109;
enddo;
*inlr = *on;
-
- New User
- Posts: 7
- Joined: Wed Jun 16, 2021 2:47 pm
- First Name: cheryl
- Last Name: carpenter
- Company Name: K&M Associates
- Phone: 4017842427
- Address 1: 425 Dexter Street
- City: Providence
- State / Province: Rhode Island
- Zip / Postal Code: 02907
- Country: United States
- Contact:
Re: Scott Klement's Working With CSV Data in RPG
Dear Scott -
Again, thank you so much for taking the time to help. You have no idea how this is appreciated. I am printing your instructions and will let you know how I make out.
Thank you!!!!!!!
Cheryl
p.s. What a dumb user name I chose, I am going to change it LOL.
Again, thank you so much for taking the time to help. You have no idea how this is appreciated. I am printing your instructions and will let you know how I make out.
Thank you!!!!!!!
Cheryl
p.s. What a dumb user name I chose, I am going to change it LOL.
-
- New User
- Posts: 7
- Joined: Wed Jun 16, 2021 2:47 pm
- First Name: cheryl
- Last Name: carpenter
- Company Name: K&M Associates
- Phone: 4017842427
- Address 1: 425 Dexter Street
- City: Providence
- State / Province: Rhode Island
- Zip / Postal Code: 02907
- Country: United States
- Contact:
Re: Scott Klement's Working With CSV Data in RPG
Hi Scott, I have made all these changes and I think the file looks good! I am waiting for the user to upload to the WIX website. I will let you know the results! I am on vac. next week so it may be the following week. If this works I have to send you a fruit basket or perhaps a cigar :)
Thank you again,
Cheryl
Thank you again,
Cheryl
-
- New User
- Posts: 7
- Joined: Wed Jun 16, 2021 2:47 pm
- First Name: cheryl
- Last Name: carpenter
- Company Name: K&M Associates
- Phone: 4017842427
- Address 1: 425 Dexter Street
- City: Providence
- State / Province: Rhode Island
- Zip / Postal Code: 02907
- Country: United States
- Contact:
Re: Scott Klement's Working With CSV Data in RPG
Hi Scott, I hope you haven't broken up with me yet :)
I made the DDS and CLP programming changes you suggested. Attached please find the output .csv file I asked my user to upload, a copy of the DDS of the file and a copy of the CL program.
The user said:
Hey Cheryl,
Just tested the upload, but received errors below.
Opened the file and all the columns/text were out of line – see second screenshot below
I believe you have the .csv input file in this thread already. I am including Scott2 and Scott3 which are attachments that the user provided in her email to me.
Appreciate your time,
Cheryl
I made the DDS and CLP programming changes you suggested. Attached please find the output .csv file I asked my user to upload, a copy of the DDS of the file and a copy of the CL program.
The user said:
Hey Cheryl,
Just tested the upload, but received errors below.
Opened the file and all the columns/text were out of line – see second screenshot below
I believe you have the .csv input file in this thread already. I am including Scott2 and Scott3 which are attachments that the user provided in her email to me.
Appreciate your time,
Cheryl
- Attachments
-
- scott3 080321.jpg (248.06 KiB) Viewed 38840 times
-
- File DDS 080321.txt
- (9.61 KiB) Downloaded 1024 times
-
- CLP 080321.txt
- (8.46 KiB) Downloaded 1505 times
Who is online
Users browsing this forum: No registered users and 1 guest