Scott Klement's Working With CSV Data in RPG

[csvrpg]

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

[Scott Klement]

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?

[csvrpg]

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

[Scott Klement]

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.)

[csvrpg]

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

[Scott Klement]

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.

[Scott Klement]

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.)

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) 

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.

Code: Select all

 CPYFRMIMPF FROMSTMF('/your-ifs-dir/inventoryBefore.csv') 
            TOFILE(your-library/F5542109)
            RCDDLM(*CRLF)
            RMVBLANK(*NONE)

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).

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; 

Once you're done, you'd just use CPYTOIMPF to convert the file back to CSV format:

Code: Select all

CPYTOIMPF FROMFILE(your-lib/F5542109) 
          TOSTMF('/your-ifs-dir/niceOutput.csv') 
          STMFCCSID(1252) 
          RCDDLM(*CRLF) 
          STRDLM(*NONE)
          RMVBLANK(*BOTH)

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.

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; 

Hope that helps

[csvrpg]

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.

[csvrpg]

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

[csvrpg]

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