cpytoimpf double quotes

[LindaBany]

I am using the cpytoimpf command to create a .csv file. The end user wants it to have comma delimiters and enclose fields with double quotes. I've done all of that many times, the problem is that he wants the numeric fields (quantity and prices) to also be enclosed in double quotes. Problem cpytoimpf does not put quotes around numeric fields. I've recreated the file defined numeric fields as alphanumeric but then I end up with editing and negative sign problems. Any advice?
CPYTOIMPF FROMFILE(QS36F/TPOSD) TOFILE(QS36F/POSSRC) +
MBROPT(*REPLACE) RCDDLM(*EOR) +
DTAFMT(*DLM) STRDLM(*DBLQUOTE) +
RMVBLANK(*TRAILING) FLDDLM(',')

 

[mikrom]

Hi LindaBany,

A) First I created table LindaBany1 with one decimal and one character field and populated it with some data. Then I created table LindaBany2 with only character fields and inserted into it formatted data from the first table:

create or replace table LindaBany1 (
  NUMFIELD  decimal(8, 2), 
  CHARFIELD character(10)
);

insert into LindaBany1 values
  (123.45, 'foo'),
  (-67.89, 'bar')
;

create or replace table LindaBany2 (
  NUMFIELD  character(10), 
  CHARFIELD character(10)
);

insert into LindaBany2 
select VARCHAR_FORMAT(NUMFIELD,'999999.99'), CHARFIELD from LindaBany1
;

B) Then I copied the data from the second table LindaBany2 into CSV-file:
1) When I used this command

CPYTOIMPF FROMFILE(MIKROM1/LINDABANY2) 
          TOSTMF('/home/MIKROM/LindaBany2.csv') 
          MBROPT(*REPLACE) STMFCCSID(*PCASCII) 
          RCDDLM(*CRLF) [highlight #FCE94F]RMVBLANK(*TRAILING)[/highlight]

I got this CSV-file

"    123.45","foo"
"    -67.89","bar"

2) When I used this command

CPYTOIMPF FROMFILE(MIKROM1/LINDABANY2) 
          TOSTMF('/home/MIKROM/LindaBany2.csv') 
          MBROPT(*REPLACE) STMFCCSID(*PCASCII) 
          RCDDLM(*CRLF) [highlight #FCE94F]RMVBLANK(*BOTH)[/highlight]

I got this CSV-file

"123.45","foo"
"-67.89","bar"

Which of the two CSVs do you like better?

 

[LindaBany]

Thank you Mikrom, but perhaps I didn't give enough information. This system runs RPGIV code not LE. I am not sure what you doing with the manipulation of data prior to the cpytoimpf command. The file is an ext described file. The quantity and price fields are defined as numeric. At one point I tried changing the DDS and defining those fields as character and moving the numeric field into it but then that presents a problem with decimals and negatives. I believe that had an error on the negative when I attempted that method. What is the code you used SQL? RPGLE? I don't recognize it, we don't use it.

 

[mikrom]

Hi LindaBany,
In my previous post I used SQL. My idea was to create to the original file a temporary file which has only character fields. Then copy all fields from the original to temporary and finally create the CSV-file from the temporary file using CPYTOIMPF command.
Can you give a short example of your CSV-file what you get now and what you need ?

 

[LindaBany]

Hi Mikrom,
If I remember correctly, this file request originally came from one of the vendors for my client. They requested a .csv file that was going to be used for EDI (I think, I need to look back). Therefore, I had to follow the field descriptions for each field and of course the quantity and prices are defined as numeric. An RPG400 (not new rpgle), reads as/400 data base file and writes out to a file designed with their specific criteria. Actually 3 different files due to different record types. I use cpytoimpf followed by cpytostmf to place it on the IFS and then I send the file via email attachment.

The problem is no matter if I define as character or numeric I need to end up with an edited numeric field enclosed in double quotes. So unless there is an option on the cpytoimpf I don't see how I can do this. By the way, this is all automated, no one will be manipulating the file in order to give them what they need.

A piece of one record: The left most is a date which I had to define as char, then 3 numeric fields, then a char field. The system automatically does not place quotes around numerics.
"20211208",.595,.850,2720.00,"01-425052-00"

I'm going to try a couple of things but I don't hold out much hope.

 

[mikrom]

Hi LindaBany,

By the way, this is all automated, no one will be manipulating the file in order to give them what they need.

However you can add to your automation an post processing step which creates from your original CSV file other formatted CSV file. There is unix like shell called qshell on IBM i and it contains a tool called awk which is exactly for tasks like this.

If I have a CSV file like this:
myfile_01.csv

"20211208",.595,.850,2720.00,"01-425052-00"
"20211208",-.195,.750,3720.00,"02-425052-00"
"20211208",.295,-.950,4720.00,"03-425052-00"
"20211208",.295,.950,-5720.00,"03-425052-00"

then using this awk command:

$ awk 'BEGIN{FS=","; OFS=","}{fld2="\"" $2 "\""; fld3="\"" $3 "\""; fld4="\"" $4 "\""; print $1, fld2, fld3, fld4, $5}' myfile_01.csv > myfile_02.csv

I get this CSV file:
myfile_02.csv

"20211208",".595",".850","2720.00","01-425052-00"
"20211208","-.195",".750","3720.00","02-425052-00"
"20211208",".295","-.950","4720.00","03-425052-00"
"20211208",".295",".950","-5720.00","03-425052-00"

You can run the command given above on IBM i using STRQSH or QSH command like this:

QSH CMD(
  'touch -C 437 /myfolder/myfile_02.csv;
  awk ''BEGIN{FS=","; OFS=","}
  {fld2="\"" $2 "\""; fld3="\"" $3 "\""; fld4="\"" $4 "\""; 
   print $1, fld2, fld3, fld4, $5}'' 
  /myfolder/myfile_01.csv > /myfolder/myfile_02.csv'
)

I tried it on my IBM i and it works great.

 

[LindaBany]

Hi Mikrom
I want to thank you again for your help, however I could not find documentation to explain the syntax and I could not get this to work. However, I did solve my problem and so I thought I'd share that with you. I think I mentioned that I'm coding in RPG400 not RPGIV however I wrote a small simple RPGIV program that reads in my native file (including the 4 numeric fields) and I do %editc which converts the numeric field to a character field and edits the field. Then when I do my CPYTOIMPF I remove leading blanks. The result is that all fields including my previously defined numeric fields are enclosed with double quotes.

Eval PDSQTA = %editc( PDSQTY : 'L')
Eval PDUCSA = %editc( PDUCST : 'L')
Eval PDUPRA = %editc( PDUPRC : 'L')
Eval PDXPRA = %editc( PDXPRC : 'L')

CPYTOIMPF FROMFILE(QS36F/TPOSDA) +
TOFILE(QS36F/POSSRC) MBROPT(*ADD) +
RCDDLM(*EOR) DTAFMT(*DLM) STRDLM(*DBLQUOTE) +
RMVBLANK(*BOTH) FLDDLM(',')

"VS-GP250SA60S","1920-","161968","20211213","11.020","13.440","25804.80-","01
F10M-E3/45","5000","162043","20211216",".084",".120","600.00","01-411172-00",
"IRFD110PBF","260","162082","20211217",".210",".390","101.40","01-420482-01",

 

[mikrom]

Hi LindaBany,
Nice to hear that you solved the problem.