Re: csv

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:

CD '/themappeddrive/anddirectory'

CPYTOSTMF FROMMBR('/qsys.lib/the_lib.lib/the_file.file/the_mbr.mbr') 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".