On 22 Feb 2013 22:22, Billy Waters wrote:
Our timekeeping package creates a coma delimited file
How is the file created? Per later reference to using CPYTOSTMF, we
can infer the file must be a database file and created [likely without
source] using either CRTPF RCDLEN(alue_specified) CCSID(*N) or CRTSRCPF
CCSID(defaulted_or_specified_EBCDIC_value). The output from the
following CL command request would reveal the "Coded character set
identifier . . . . . . : CCSID" value:
DSPFD the_file *ATR
Per a newer message thread from the OP, the file is revealed, with
near certainty, to have been created by CRTPF RCDLEN(80). This implies
the database file has CCSID=65535 [aka CCSID=*HEX] which implies that no
conversion should occur for the data. Thus the prior paragraph is
likely now moot.
Then what was the encoding for the data that was written to that
database file.member must also be known. Probably the data written to
the file[.member] was EBCDIC data?
The following request would produce a spool file with the hex code
points revealed for each character of the string of data [as described
in the other message thread]; adjust as necessary, the from-record
number specification in the following CL request, to obtain the record
described in that other message:
CPYF the_file *PRINT OUTFMT(*HEX) FROMRCD(1) NBRRCDS(1)
I need to export this to SQL server.
As CSV data, the file data is already /exported/ from the perspective
of the IBM i server. Since export, the data must be transported to the
other system\database; transported from the member of the effective flat
file, as a either a PF-SRC or a PF-DTA database file, likely to a stream
file or logical representation of a stream file. That data then needs
to be /imported/ by the SQL Server database when\however that data is
available to the other system\database. Likely that database will want
to import via an ASCII physical stream file [e.g. as with FTP], or via a
[network\communication] interface that presents the data logically as a
/file/ with an ASCII stream of /record/ data [e.g. as with a mapped drive].
I have tried CPYTOSTMF with no success.
What specific CPYTOSTMF invocation(s)?
File is copied, only see garbage when opening with Notepad.
A STMF was opened on a mapped-drive to the IBM i server? Or after
the CPYTOSTMF, was there a record transport to generate a physical copy
of the stream file on the other server? If there was a physical
transport, then the details about how that was effected may also be
required to better assist.
Depending on what is specified on the FROMMBR, what is specified on
the other parameters of the CPYTOSTMF can be very important to ensure
the proper\desirable effects. The default values for at least the
CVTDTA and CCSID parameters are poor for a /program described/ database
flat file; those defaults are at least somewhat workable for a database
source physical file, but still requires an ASCII CCSID to be specified
for the STMFCODPAG parameter.
Given the details of a file with "1 column 80 characters" [as written
in the newer message thread], I will infer the file is a database flat
file created with CRTPF the_lib/the_file MBR(the_mbr) RCDLEN(80) with
one record of EBCDIC data described by the character string
'8,xxxxxxxxx,999.99,2013/02/18' having been previously written by the
"timekeeping package", and thus the following CD and CPYTOSTMF command
invocations might be desirable:
TOSTMF(data_to_import.csv) STMFOPT(*REPLACE) CVTDTA(*AUTO)
DBFCCSID(*FILE) STMFCODPAG(*PCASCII) ENDLINFMT(*CRLF)
After those actions above, the file data_to_import.csv opened on the
system mapping a drive to the noted IBM i directory likely will be able
to present the ASCII data with CCSID=1252 with expected glyphs instead
of being presented as "garbage".