Solved

Import file problem

Posted on 2011-09-16
10
665 Views
Last Modified: 2012-05-12
Hi Experts,

I Have a found a behavior in the CPYTOIMPF.

Situation:
1. I Create a file that must be send as atachment
2. I Create a file with 1 record containing all field headers (with semicol.) Like Number;Description;Price;Qty
3. Copy the headers record to the IFS with CPYTOIMPF TOCCSID(*PCASCII)
4. Copy the other file to the same IFS file with CPYTOIMPF TOCCSID(*PCASCII)

After step 3 the IFS file contains a the headers and looks normal.
After step 4 the records are added, but the line with headers is scrambled

Example:
Ö      £  ¥  ^ÆÔâ@å     £ @    ^Ö¥        ¢£^Ò  £ Ö ¢

the "^" is probably the original ";"

CCSID *STDASCII has the same problem

I'm out of ideas :-)


Thanks
Murph
0
Comment
Question by:theo kouwenhoven
10 Comments
 

Expert Comment

by:ASmith_SBS
ID: 36548457
first thought is that it is a CCSID problem.
You can check the CCSID of files in the IFS using iseries  Navigator.  
Right click the file and look at Properties > Storage.
0
 
LVL 16

Author Comment

by:theo kouwenhoven
ID: 36548528
Hi,

Yes the CCSID was also my first thoughts, so I checked the file with option 8 in WRKLNK and see
CCSID = 1252 (after step-3 and also after step-4)
In the navigator it's not working, because of a bad installed version by my Customer.

If I need to check that also, I have to ask deskside support and that takes day's or weeks.

Regards,
Murph

0
 
LVL 16

Author Comment

by:theo kouwenhoven
ID: 36548576
More info:

First CL is executing:

CPYTOIMPF  FROMFILE(QTEMP/TMPTXT) TOSTMF(&TARGET) +    
             STMFCODPAG(*PCASCII) RCDDLM(*CRLF) +      
             STRDLM(*NONE) RMVBLANK(*BOTH) FLDDLM(';')

Second CL executes:

CPYTOIMPF  FROMFILE(QTEMP/TMPDATA) TOSTMF(&TARGET) +
             STMFCODPAG(*PCASCII) RCDDLM(*CRLF) +    
             STRDLM(*NONE) RMVBLANK(*BOTH) FLDDLM(';')

QTEMP/TMPTXT contains 1 record of 1 field:
Txt1;Txt2;Txt3;Txt4;.....Txt35

QTEMP/TMPDATA contains the real data that sould be exported







0
 
LVL 16

Author Comment

by:theo kouwenhoven
ID: 36548758
S_O_L_V_E_D :-)


The 2 inputfiles had different CCSID's

The datafile was created from DDS and had CCSID 37
While the TMPTXT file was created with a record-length (no source) and hed CCSID 65535.

Surprised me that this affected the data in the result file.
Not understanding why....

But by Creating the TMPTXT file from a sourse it's solved

Thanks for the support
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 125 total points
ID: 36549267
Murph,

Glad you got it figured out.  Here is the "Why?":

CCSID 65535 means that the data is binary, and should not be translated.  So whenever you perform an operation that requests translation FROM 65535 TO ANYTHING, the system simply copies byte for byte (in this case, I assume that the data in the header file is actually encoded in EBCDIC, based on the results you got.)

Not sure how you viewed the file at this point, but whatever tool you used clearly identified it was EBCDIC and showed it to you in that format.

Then, in step 4, you appended FROM a CCSID 37 DB file.  This time, the system performed the CCSID 37 to 1252 conversion implied by the *PCASCII, and you end up with a file containing mixed encodings: the top row is untranslated (CCSID 37, probably), and the remaining rows are ASCII (CCSID 1252).

No matter how you view that, something is going to look wrong.

The solution, as you discovered, is to make sure that your TMPTXT file is in a file that has a CCSID other than 65535 (well, it needs to be a FROM/TO CCSID pair for which a translation table exists exists on the system.)

- Gary Patterson
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 34

Expert Comment

by:Gary Patterson
ID: 36549320
Murph,

I just replicated your issue (on a V5R3 machine), and when I completed step 3 and used EDTF (WRKLNK Option 2) to view the resulting stream file, it showed me the results in EBCDIC in spite of the fact that the file was showing 1252.  It also showed diagnostic message CPDB610 :

 Message . . . . :   File CCSID incorrect.                                    
 Cause . . . . . :   The file CCSID was 01252, but the data in the file looks
   like EBCDIC. A CCSID of 00500 is being used.
                             
 Recovery  . . . :   If another CCSID is needed, use F15 to change to the    
   desired CCSID.                                                  

This explains why it originally looked right.

- Gary
         
                                                                             
0
 
LVL 16

Author Closing Comment

by:theo kouwenhoven
ID: 36549897
Not the right answer, BUT the right and understandable explenation why this was happening

Thanks Gary
0
 
LVL 16

Author Comment

by:theo kouwenhoven
ID: 36549916
Gary,

Normaly I would reclaim the points, but your explenation was clear...

Thanks
Have a great Weekend!!!!
0
 
LVL 27

Expert Comment

by:tliotta
ID: 36552788
EDTF will do it's best to figure out what CCSID should be used to display the data if it finds an inconsistency. The CPDB610 message is sent when the happens. It knew that the bytes couldn't possibly be CCSID 1252 since the conversion failed when it first tried to generate the display.

It then tried with CCSID 37 and got no conversion errors, so it said that "...the data in the file looks like EBCDIC."

For the first CPYTOIMPF command, try making this change:

CPYTOIMPF  FROMFILE(QTEMP/TMPTXT) TOSTMF(&TARGET) +
             FROMCCSID( 37 )  +
             STMFCODPAG(*PCASCII) RCDDLM(*CRLF) +      
             STRDLM(*NONE) RMVBLANK(*BOTH) FLDDLM(';')

Let the command know that it should treat the data as CCSID(37) and it should work with 65535 data.

Or, of course, you can also just create the file with the appropriate CCSID as you already did.

CCSIDs become extremely important whenever data moves from one system to another. It can be hard to see that a problem exists as long as the data is only moving to different files on the same system. Bring a second system into it all, even a PC, and problems can become visible quickly... or maybe not until much later.

An old saying goes "A man with one watch always knows what time it is. A man with two watches never knows."  We might change it like "A man with one system always knows what his data says...".

Tom
0
 
LVL 16

Author Comment

by:theo kouwenhoven
ID: 36552826
Amazing,

Working more then 20 years with the AS/400 and still learning every day :-)


0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now