CPYTOIMPF with delimited file and *EOR
RMK
CRTPF FILE(QTEMP/DWRECXLS) RCDLEN(258)
CPYTOIMPF FROMFILE(DWRC01P) TOFILE(QTEMP/DWRECXLS) +
MBROPT(*REPLACE) RCDDLM(*EOR) +
DTAFMT(*DLM) FLDDLM(',')
The person on the other end, with Unix, says:
>the lines in the files don't have a proper carriage return and line
feed >characters at the end of each line. If I edit the file in vi on
unix and >merely save it back to disk, vi takes care of adding the
proper character to >the end of each line. Until I do that, the
sql*loader program doesn't know >when a line ends, and it displays an
error message and will not load the >file. Can they change there file
creation process or maybe just their ftp >process to make sure that a
carriage return/line feed sequence is at the end >of every line?
After I told him the record delimiter is *EOR which means the data
after the last field will be padded with X'00'. He says:
>Looking further at the file it appears that the problem is coming
from a null
>(x'00') that is present before the CRLF that is at the end of each
line. Is >it possible to not have the nulls (x'00') in the file?
I cannot do anything about the 00. I have to CRTPF the file with at
least 1 extra character and it has the 00. I've sent FIXED data files
to other users at this company and they've been able to read them ok.
I can't change the *EOR or I'll get an error.
Is anyone familiar w/unix and the as400 that can help me ? Thanks.
Wyatt Taylor
> least 1 extra character and it has the 00. I've sent FIXED data files
> to other users at this company and they've been able to read them ok.
> I can't change the *EOR or I'll get an error.
I don't understand this statement. The CSV file you are creating, by its'
nature, creating variable length records. Both the character and numeric
data that are included in the file are of varying length. All (or almost
all) programs that import .csv files don't have a problem with this. Have
you tried using RCDDLM(*CRLF) ? It should work.
The problem with .csv files is not how short to make the record, but rather
it should be long. You should be able to make this record 512 or 1024 and it
should work just fine - any other system should be able to import it.
I think the reason that you are getting a CR/LF appended to each record is
that FTP from the AS/400 is doing that for you when you send it to the Unix
system.
Can you tell us more?
"RMK" <rkn...@valspar.com> wrote in message
news:eb583c1.01121...@posting.google.com...
RMK
a stream file can only be *CR, *CRLF, *LF, or *LFCR and for a data
base file the RCDDLM parameter can be *EOR or a valid value. 11 - Use
the valid RCDDLM value for the parameter.
I originally made the file 1024 leaving extra space at the end. The
CPYTOIMPF fills all blanks at the end with the 00 and this is what the
unix person is telling me is the problem. That is why I then tried to
make the length the exact length of the file AFTER the CPYTOIMPF. It
would not let me make it exact and I had to add one position. The
CPYTOIMPF then puts the 00 in this extra position as I did a DSPPFM
and I can see it.
I use this all the time and do not have a problem sending *fixed
files. The CRLF is OK with the unix user. He is complaining about the
00 before the end of record.
I was hoping the Unix person's message (listed again below) might shed
some light on what HE is doing wrong !
> > The person on the other end, with Unix, says:
> >
> > >the lines in the files don't have a proper carriage return and line
> > feed >characters at the end of each line. If I edit the file in vi on
> > unix and >merely save it back to disk, vi takes care of adding the
> > proper character to >the end of each line. Until I do that, the
> > sql*loader program doesn't know >when a line ends, and it displays an
> > error message and will not load the >file. Can they change there file
> > creation process or maybe just their ftp >process to make sure that a
> > carriage return/line feed sequence is at the end >of every line?
> >
> > After I told him the record delimiter is *EOR which means the data
> > after the last field will be padded with X'00'. He says:
> >
> > >Looking further at the file it appears that the problem is coming
> from a null
> > >(x'00') that is present before the CRLF that is at the end of each
> > line. Is >it possible to not have the nulls (x'00') in the file?
> I don't understand this statement. The CSV file you are creating, by its'
> nature, creating variable length records. Both the character and numeric
> data that are included in the file are of varying length. All (or almost
> all) programs that import .csv files don't have a problem with this. Have
> you tried using RCDDLM(*CRLF) ? It should work.
"Wyatt Taylor" <wwta...@swbell.net> wrote in message news:<_w9U7.854$7R3.40...@newssvr11.news.prodigy.com>...
Wyatt Taylor
LOCSITE TRIM 0
it will stop trimming characters. The dafault is TRIM 1 which is on.
Then you can make your record as long as you need to.
"RMK" <rkn...@valspar.com> wrote in message
news:eb583c1.01122...@posting.google.com...
Wyatt Taylor
case, you should have
ASCII
LOCSITE TRIM x whichever you decide
and you should not have
STRUCT R
"Wyatt Taylor" <wwta...@swbell.net> wrote in message
news:WpmU7.125$x_1.72...@newssvr12.news.prodigy.com...
lewisk...@gmail.com
For FTP : LOCSITE TRIM 0 , how about MFT? where to define to remove spaces at the end of each record? Thank you and best regards.
Alex
> I'm sending a file with the following commands:
>
> CRTPF FILE(QTEMP/DWRECXLS) RCDLEN(258)
> CPYTOIMPF FROMFILE(DWRC01P) TOFILE(QTEMP/DWRECXLS) +
> MBROPT(*REPLACE) RCDDLM(*EOR) +
> DTAFMT(*DLM) FLDDLM(',')
>
> The person on the other end, with Unix, says:
>
Why not a stream file in IFS ?
CPYTOIMPF FROMFILE(DWRC01P)
TOSTMF('\home\DWRECXLS')
MBROPT(*REPLACE)
STMFCCSID(*PCASCII)
RCDDLM(*CRLF)
DTAFMT(*DLM)
STRDLM(*NONE)
FLDDLM(',')
CRPence
• How does someone ask of MFT to drop the trailing blanks, similarly
[yet in contrast] to how the subcommand request "LOCSITE TRIM 0" effects
keeping trailing blanks in FTP?
I do not know, neither if that has properly paraphrased the inquiry
nor the answer to that paraphrased inquiry. But I have some questions
that might help me better understand the message.
What is MFT? What does the question have to do with CPYTOIMPF? Is
the desire to include or remove trailing blanks when using [whatever is]
MFT?
FWiW: If responding to an ancient topic [what might be referred to as
a zombie-posting, a message responsible for reincarnating an ancient
topic thread], then including some context from that prior conversation
is valuable to anyone reading via NNTP\USENET; a NewsServer may keep a
copy of the messages, only from the past month. Rather than
necro-posting, composing a _new message_ with all the necessary context
to describe the issue is probably better for the readers; what helps the
readers better understand the issue\scenario, helps them to respond in a
helpful manner, rather than respond solely to ask for more details.
--
Regards, Chuck
jse...@yahoo.co.nz
Please note though, DON'T use backward slashes as suggested above! Only windoze uses backward slashes (goes with the backward OS) and they can cause all sorts of issues on real systems. Unix and other OSes use proper forward slashes. Also, I'm not sure I agree with making the STRDLM(*NONE). If a field contains a comma for instance, then it will be interpreted incorrectly when imported so I would advise retaining the quotes. You could also use the RMVBLANK option to remove leading/trailing blanks if needed. Using the /home directory is just an example, you may wish to create a directory for these.
CPYTOIMPF FROMFILE(DWRC01P)
TOSTMF('/home/DWRECXLS.csv')
MBROPT(*REPLACE)
STMFCCSID(*PCASCII)
RCDDLM(*CRLF)
RMVBLANK(*TRAILING)
rashmi....@gmail.com
Jonathan Ball
> I am getting a similar issue, CRLF at the end of a file (after CPYTOIMPF) is creating a new line at the bottom which is creating an issue at the servere where it is being FTP'd. Can someone advise how to get rid of CRLF / empty line