CPYTOIMPF CHAR fields garbage in CSV

[natefarm]

Hello, I'm having trouble copying a file to a csv on the PC network. The file is created from a query, and all the fields (numeric and alpha) copy fine except for two. These two fields were created using the Define Result Fields option in the query (BANKID = '00001' and MATCODE = 'M'). The two columns appear as garbage in the csv file. It makes no sense to me that these two fields are garbled, when Char fields pulled from files come out just fine. They all look the same in a DSPFFD of the output file.

Here is the command I'm using:

[COLOR=#1f497d][FONT=Calibri]CPYTOIMPF FROMFILE(LIBNAME/FILENAME) TOSTMF('\groups\common\Test.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF) FLDDLM(?,?) STRDLM(*NONE) RMVBLANK(*BOTH)[/FONT][/COLOR]

If I copy the file using iAccess Data Transfer, it works fine, so there must be a problem with my parameter options, but I have tried a variety of other options with no effect. Any suggestions please?

[Terry Wincheste]

When you say they look the same on the DSPFFD output - do you mean that all of the fields in the file have the same CCSID? If the CCSID is different on those 2 fields then that is probably the problem ;-)

[natefarm]

Indeed, it's 65535 for the problem fields and 37 for the others. Still, isn't there a parameter that should translate the code correctly (as it does when using the DTF)? If not, what's the solution otherwise? Thanks!

[DeadManWalks]

I did a quick query/400 to an outfile and then did a dspffd on the table;

            Data        Field  Buffer    Buffer        Field    Column         
 Field      Type       Length  Length  Position        Usage    Heading        
 STORE      ZONED        5  0       5         1        Both     Store          
   Field text  . . . . . . . . . . . . . . . :  Store                          
 FORMAT     CHAR            6       6         6        Both     Format         
   Field text  . . . . . . . . . . . . . . . :  Format                         
   Coded Character Set Identifier  . . . . . :     37                          
 EXCLUDE    CHAR            1       1        12        Both     Exclude        
   Field text  . . . . . . . . . . . . . . . :  Exclude                        
   Coded Character Set Identifier  . . . . . :     37                          
 JUNK       CHAR            7       7        13        Both                    
   Field text  . . . . . . . . . . . . . . . :  format || exclude              
   Coded Character Set Identifier  . . . . . :     37

my new field junk is also 37 ccsid. I am on 7.1.


I guess you could create the resulting file first with the new fields all having the same CCSID , then do a replace in the query/400.
Or
Change this to a qmqry and use sql casting like this article says. http://www.itjungle.com/mpo/mpo091103-story03.html

[EmmanuelW1]

Well, the help text of the FROMCCSID field of the CPYTOIMPF command says:

Specifies the coded character set identifier (CCSID) to
use for the from-file fields.

*FILE
The data is converted from the from-file field CCSID.
If the CCSID of the from-file field is 65535, the
field is not converted and it is treated as binary
data.

1-65533
Specify the CCSID to be used when the CCSID of the
from-file field is 65535. If the CCSID of the
from-file field is not 65535, this parameter is
ignored.

Cheers,

Emmanuel

[EmmanuelW1]

Seems that specifying a CCSID for the FROMCCSID parameter of the command should take care of this - see help text for that parameter.

Cheers,

Emmanuel

[natefarm]

I tried putting 65535 in the FROMCCSID parameter, but the valid range is 1-65533, so it won't allow it.

[EmmanuelW1]

I think you'd want to try 37 or maybe 1252.

Cheers,

Emmanuel

[john.sev99]

CPYTOIMPF won't translate fields with a CCSID of 65535 unless things have changed in newer OSs (I think that doubtful as my query with IBM may years ago just said it was working as designed). You will need to somehow convert it to another CCSID before using CPYTOIMPF or create the CSV yourself by some other means.

[Terry Wincheste]

CPYTOIMPF won't translate fields with a CCSID of 65535 unless things have changed in newer OSs

This has been my experience too! I have usually opted to pre-build the physical file and use replace -- as DeadManWalks suggested.

[EmmanuelW1]

John Serverinson wrote: "CPYTOIMPF won't translate fields with a CCSID of 65535 unless things have changed in newer OSs..."

That's odd, because I tested after posting, and using 37 for the FROMCCSID parameter resulted in legible data in my CSV file from a field that was 65535 in the from file. That was done on a V7R1 system.

Cheers,

Emmanuel

[tomliotta]

Essentially, the definition of CCSID(65535) is "Binary: Do not translate." So if the fields actually contain CCSID(37) characters (i.e., they have those bit patterns), they'll have the same bit patterns on output. If you expect them to have something like ASCII bit patterns on output, you'll instead find garbage because no conversion was done.

The system doesn't know what characters are in CCSID(65535) fields. Each character can potentially have a different encoding. It's up to applications to do appropriate decoding/ecoding of those fields.

[EmmanuelW1]

Tom Liotta wrote: "If you expect them to have something like ASCII bit patterns on output, you'll instead find garbage because no conversion was done."

I'm not sure if this is in response to my post, but I can assure you that the fields defined with CCSID 65535 in the from file did not contain garbage in the CSV file when FROMCCSID(37) was used with the CPYTOIMPF command. Try it for yourself.

Cheers,

Emmanuel

[tomliotta]

the fields defined with CCSID 65535 in the from file did not contain garbage in the CSV file when FROMCCSID(37) was used with the CPYTOIMPF command.

Yes. You must specify the FROMCCSID() parameter in order to tell the system how to convert CCSID(65535) fields. That's why the parameter is available on the command.