Using CPYFRMIMPF to copy data from a TXT file to DB file

70 pts.
Tags:
CPYFRMIMPF
iSeries database
OS/400
V5R4
I am using the CPYFRMIMPF to copy data from a TXT file into a DB file.  The delimiter is a ']' character.  The command runs but no records are copied.  The error says "0 records copied from member TXT.  Copy command ended because of error".   Upon further review I found "The copy did not complete for reason code 7. "   The error "  7 - The FROMFILE numeric field *N contain blank characters, or other characters that are not valid for a numeric field." I don't understand how an inbound field can be defined as numeric when it is a TXT file.

Software/Hardware used:
i-Series os400 V5R4
ASKED: July 6, 2011  9:16 PM
UPDATED: August 28, 2013  2:58 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I believe the message is stating that one of the ‘columns’ in your input text file contains blanks or alpha characters that you are trying to feed into a DB2 file that has that the field defined as numeric which is not allowed.

Discuss This Question: 15  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BigKat
    what it means is that the data in the from file element that is to be mapped to a numeric field is invalid for a numeric field. You might try adding RPLNULLVAL(*FLDDFT) to your CPYFRMIMPF command.
    7,565 pointsBadges:
    report
  • AnchorS
    You might also have a header record in the .txt file. Check the input file for the header record (column definitions) and delete if present. For eample, a colum heading of "Number of widgets".
    380 pointsBadges:
    report
  • AnchorS
    ...and I forgot, once you've determined that there is a header record....you can skip it using the parameter FROMRCD to start a record number 2. I always like to prompt the command (f4) and see what my options are.
    380 pointsBadges:
    report
  • Cunninjoe
    Since every record is being dropped, it may be that you have numeric fields in physical files that are not numeric in all records, or you may have .txt data larger than the PF field. Probably the same thing is wrong for all records. Having one bad record should not abort the job. Good luck,
    320 pointsBadges:
    report
  • TomLiotta
    Four items would help us give answers:
    1. The CPYFRMIMPF command.
    2. The message ID of the error (probably CPF2845).
    3. A line from the input file.
    4. The file field descriptions for the output file.
    The more of those you can supply, the better. Comments so far are all valid, but it might just be that the incoming file doesn't match the database file at all or that one or more parameters for CPYFRMIMPF need to be changed. Tom
    125,585 pointsBadges:
    report
  • SteveLane
    Used the RPLNULLVAL(*FLDDFT) option in my command and while the command is copying the records to the "To File", I only get the first field from the TXT file.
    70 pointsBadges:
    report
  • SteveLane
    Should have added, there is no header record and all fields in the DB2 file are defined as Alpha fields.
    70 pointsBadges:
    report
  • TomLiotta
    ...all fields in the DB2 file are defined as Alpha fields. That's a good start. Now we only need to see the CPYFRMIMPF command and at least one line from the import file. Those might be enough to suggest an answer, or they might indicate what additional info is needed. Tom
    125,585 pointsBadges:
    report
  • SteveLane
    Here is the data file. *...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9.... S0100113]5]1]1R4]62-315-8789]30]5]1]500000]125325]N]N]29]0]0]3469]Y]8]62.5]21 S0100121]1]100]5A1]00-538-1447]1750]2]91]5000000]54405]N]N]78]250000]8.83]5051]Y]428]21.7]63 S0100130]2]86]3A3]00-863-1434]100]3]57]300000]10421]N]N]67]100000]5.67]3499]N]70]30.3]14 S0100144]1]99]1R2]11-878-6813]52]2]91]75000]3671]N]N]80]120000]9.49]3599]N]39]5.13]26 S0100147]1]98]3A1]00-403-8220]70]2]88]55000]4120]N]N]72]55000]7.83]3444]N]62]25.81]60 S0100150]1]92]3A2]86-142-5726]150]2]88]75000]6781]N]N]77]112500]8.83]3444]Y]50]28.57]17 Here is the command; CPYFRMIMPF FROMFILE(INPUTFILE) TOFILE(OUTFILE) MBROPT(*REPLACE) FLDDLM(']') ERRRCDOPT(*REPLACE) RPLNULLVAL(*FLDDFT) All I get from the input file is the first field of every record.
    70 pointsBadges:
    report
  • TomLiotta
    Try adding STRDLM(*NONE) to your CPYFRMIMPF command. The default is STRDLM(*DBLQUOTE) and there are no quoted columns in your sample. I would first guess that the command looks at the first portion of the first row and sees [S0100113]. Since it doesn't find any quotes, it tries to generate a numeric value. I don't recall a delimited file that didn't include any indication of which columns were strings and which ones weren't, so this might take some experimentation. It's possible that this format is outside of what the basic CPYFRMIMPF function can handle. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    All I get from the input file is the first field of every record. I keep missing that piece, though it's hard to tell without experimenting. CPYFRMIMPF might be trying to interpret each column based on content since you allowed it to default to STRDLM(*DBLQUOTE) and don't have any quotes. It figures that the first column must be a string because it found a character that wasn't a digit. Then it tries to handle the next column and finds only digits in each row. It gets a numeric value and attempts to put it into a character column in the table -- hits an error. Let us know if STRDLM(*NONE) makes any difference. I won't be surprised whether it works or fails, but it's worth a try. Tom
    125,585 pointsBadges:
    report
  • SteveLane
    Tom, changed the parm to *NONE for STRDLM but unfortunately no change. I tried several other options but have had no success. The data is coming from D&B, I think I'll go back and ask for a CSV file. Maybe I'll have better luck with that.
    70 pointsBadges:
    report
  • TomLiotta
    Using the sample you provided and defining a To-File that only has character fields, this is what I see from RUNQRY after I execute CPYFRMIMPF:
    Line   ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10....+...11....+...1 
           F1        F2  F3   F4   F5           F6    F7  F8       F9      F10  F11  F12  F13     F14   F15   F16  F17  F18    F19 
    000001 S0100113  5   1    1R4  62-315-8789  30    5   1        500000   1    N   N    29      0     0      3   Y    8      62  
    000002 S0100121  1   100  5A1  00-538-1447  1750  2   91       500000   5    N   N    78      2500  8.83   5   Y    428    21  
    000003 S0100130  2   86   3A3  00-863-1434  100   3   57       300000   1    N   N    67      1000  5.67   3   N    70     30  
    000004 S0100144  1   99   1R2  11-878-6813  52    2   91       75000    3    N   N    80      1200  9.49   3   N    39     5.  
    000005 S0100147  1   98   3A1  00-403-8220  70    2   88       55000    4    N   N    72      5500  7.83   3   N    62     25  
    000006 S0100150  1   92   3A2  86-142-5726  150   2   88       75000    6    N   N    77      1125  8.83   3   Y    50     28  
    The command that I used was:
    CPYFRMIMPF FROMSTMF('Source/impf.txt')
               TOFILE(IMPF)
               RCDDLM(*CRLF)
               STRDLM(*NONE)
               FLDDLM(']')
    That is, I ran just about the minimum necessary. One obvious difference is that I used a streamfile as input rather than a physical file. Do you have a reason for using a physical file? That seems to me to add complexity that isn't needed. One specific complexity would be that you have fixed-length records in your physical file that don't seem to match up with the varying lengths of your sample data. I can't tell what characters might be present at the end of your records. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    I can’t tell what characters might be present at the end of your records. Display your physical file member again. Then switch the display to hex mode with F10='Display hexadecimal'. Look at the end of the records to see if anything but x'40' characters are there. Your data might have CR/LF characters embedded at the ends of the data or anything anywhere out to the ends of the lines. Tom
    125,585 pointsBadges:
    report
  • dinuas400
    In the CPYFRMIMPF give this field value RPLNULLVAL(*FLDDFT) Then that error will be removed...
    510 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following