Using CPYTOIMPF and apostrophes

[Bill Howie]

Hello all......

I have what I hope is a simple question. I am using CPYTOIMPF to
create a CSV file for an application I am working on. One of the
fields I have in the ISeries file that I am copying is a numeric field
that I convert to alpha with the %EDITC BIF. This field translates to
the CSV file just fine. However, we have a need for an apostrophe to
be right before the number in the cell in the CSV file that this field
occupies. In Excel, this identifies the field as a label. I can do
the CPYTOIMPF without attaching an apostrophe on that field, and then
within the CSV sheet maunally stick an apostrophe in the cell and the
cell becomes identified as a label (with the small green triangle in
the upper left corner). This is what is needed for the application.
So I thought to myself, "add the apostrophe on before you send the file
down". But if I try to put the apostrophe in there on the ISeries side
and then do the CPYTOIMPF, it comes across to the CSV file looking like
this: '3.

Is there a parameter or some sort of workaround that would allow this
field to show up in the CSV file with this cell defined as a label? Or
am I barking up the wrong tree in using the CPYTOIMPF command? Any and
all help would be greatly appreciated. Thanks!


Bill Howie
Programmer/Analyst
Coastal Pet Products, Inc.

[Drew Dekreon]

The green triangle is not a label marker, it's an Error marker. Excel
uses them to mark cells that are inconsistent with their neighbors..
That said, I think you just need to adjust how you are using %editc.
Try this
evalr field=''''+%trim(%editc(numericfield:'Z'))
(try eval instead of evalr as well)

Post by Bill Howie
Hello all......
I have what I hope is a simple question. I am using CPYTOIMPF to
create a CSV file for an application I am working on. One of the
fields I have in the ISeries file that I am copying is a numeric field
that I convert to alpha with the %EDITC BIF. This field translates to
the CSV file just fine. However, we have a need for an apostrophe to
be right before the number in the cell in the CSV file that this field
occupies. In Excel, this identifies the field as a label. I can do
the CPYTOIMPF without attaching an apostrophe on that field, and then
within the CSV sheet maunally stick an apostrophe in the cell and the
cell becomes identified as a label (with the small green triangle in
the upper left corner). This is what is needed for the application.
So I thought to myself, "add the apostrophe on before you send the file
down". But if I try to put the apostrophe in there on the ISeries side
and then do the CPYTOIMPF, it comes across to the CSV file looking like
this: '3.
Is there a parameter or some sort of workaround that would allow this
field to show up in the CSV file with this cell defined as a label? Or
am I barking up the wrong tree in using the CPYTOIMPF command? Any and
all help would be greatly appreciated. Thanks!
Bill Howie
Programmer/Analyst
Coastal Pet Products, Inc.

[Bill Howie]

Post by Drew Dekreon
The green triangle is not a label marker, it's an Error marker. Excel
uses them to mark cells that are inconsistent with their neighbors..
That said, I think you just need to adjust how you are using %editc.
Try this
evalr field=''''+%trim(%editc(numericfield:'Z'))
(try eval instead of evalr as well)

I gave this a try and it did not work. The field is left-justified,
which is OK, but the apostrophe still shows up in the field itself.
I'm hoping there is some way that Excel can convert that apostrophe.
When I manually type the apostrophe in there, a small box appears to
the left of the field that says that this numeric field is either
formatted as text or preceded by an apostrophe. But that doesn't
happen unless I manually remove the apostrophe that the ISeries put in
there and type one in myself. Thanks for the reply.

[Drew Dekreon]

Try this:
eval field='"'+%trim(%editc(numericfield:'Z'))+'"'
The problem is Excel: _typing_ an apostrophe puts Excel into text entry
mode, switching off the alpha-to-numeric conversion it normally does.
The apostrophe doesn't actually become content in the cell.
A CSV file normally encloses strings in quotes, so the line above might
help.
I'm stuck on v4r5 currently and cpytopimpf doesn't really work that
well, so I usually do it manually by using a series of lines like the
eval above to build up a complete CSV line and writing it into a field
with a single 253 byte field. Then I ftp the result file to network
drive (or copy it to the IFS) where the PC can pick it up.

Post by Bill Howie

Post by Drew Dekreon
The green triangle is not a label marker, it's an Error marker. Excel
uses them to mark cells that are inconsistent with their neighbors..
That said, I think you just need to adjust how you are using %editc.
Try this
evalr field=''''+%trim(%editc(numericfield:'Z'))
(try eval instead of evalr as well)

I gave this a try and it did not work. The field is left-justified,
which is OK, but the apostrophe still shows up in the field itself.
I'm hoping there is some way that Excel can convert that apostrophe.
When I manually type the apostrophe in there, a small box appears to
the left of the field that says that this numeric field is either
formatted as text or preceded by an apostrophe. But that doesn't
happen unless I manually remove the apostrophe that the ISeries put in
there and type one in myself. Thanks for the reply.