View original (French)

Populate a CSV file via AS400

vp29 -  
 J.M.ARAB -
Hello,

I am a pure AS400 programmer (RPG development, DB2 database), and I need to create and populate a CSV file from an AS400 database. Can someone help me? Is it sufficient to create a file in the AS400 database with fields separated by a comma or semicolon?
Thank you
Configuration: Windows XP Internet Explorer 7.0

6 answers

Answer 1 / 6
wb
 
Hello everyone,

Thanks to VP29,
The command:
CPYTOIMPF FROMFILE(WILLIAM/WBCSV) TOSTMF('/paseo/wbcsv.CSV') MBROPT(*REPL
ACE) STMFCODPAG(*STDASCII) RCDDLM(*CRLF) STRDLM(*NONE) RMVBLANK(*BOTH) FL
DDLM(';') DECPNT(*COMMA)

works partially (I am on V5r4M0) because the removal of spaces in the different fields does not happen.
************Beginning of data**************
10;01 ;CA ;1 ;43 ;a ;25 ;1200 ;SL ;20090417;162000
************End of Data********************
In the end, I do have the result file wbcsv.csv in the IFS (automatically created by the command).
For your information, the original file must be a DB2 file with zone names.
I had mistakenly created a file without zones and only had one zone in the .CSV result.
Via the Windows Explorer, I can open an Excel file, so that's fine. No more RPG programming to do.

What I still need to do and don’t know how yet:

Copy this file from the IFS to a specified user's PC directory.
Of course automatically, like: If the file starts with WB*.*, I put it on WB's PC.
If you have any information, I would appreciate it.
Thank you in advance.
WB.
5
J.M.ARAB
 
You need to go to File Explorer, select "Map Network Drive," assign a letter to this drive, and enter the path as \\as400_name or its IP address \root\directoryIFS\ where your file is located. Then you can drag it to the directory of your PC or another one on the network.
Good luck!
0
Answer 2 / 6
VP29 Posted messages 3 Status Member 1
 
Why not update all the .csv files in a common folder on a server?
And manage user rights by file afterwards?

Finally, it depends on what you want to do.
1
Answer 3 / 6
Ludo
 
Hello,

In principle, I create the file in a "classic" way on the AS400 and then use a CL to transfer it to the IFS with the CPYTOIMPF command.

Ex: transfer of the file TOTO

CPYTOIMPF FROMFILE(library_AS/TOTO) TOSTMF('QDLS/directory/TOTO.CSV') MBROPT(*REPLACE) STMFCODPAG(*STMF) RCDDLM(*CRLF) DTAFMT(*DLM) STRDLM(*NONE) FLDDLM(';')

Best regards,
0
vp29
 
Hello
Thank you for your response.
The problem is that the CPYTOIMPF command does not convert EBCDIC to ASCII, which results in an unreadable file
Best regards
0
wb > vp29
 
Hello,
I have the same problem and I'm hesitant about whether to write a program to generate this .CSV file.
Good luck.
WB.
0
VP29 Posted messages 3 Status Member 1 > wb
 
Hello

I used the AS400 CPYTOIMPF function which works very well

Regards
0
Answer 4 / 6
othanga
 
Hello,

What platform should the CSV be used on? If it's for Windows, it might as well leave the work to Client Access and its ODBC driver.

Personally, I've been using ADO (since WinXP) to connect to the database, submit a not too complicated query, and read the result line by line to store it in a .CSV file.
Normally, the column separator in the .CSV is a comma and the strings are enclosed in quotes.
0
Answer 5 / 6
VP29 Posted messages 3 Status Member 1
 
Here is my command that I placed in a CL program AS400

CPYTOIMPF FROMFILE(IR6_1SP/ACTECR) TOSTMF('/IRIS +
ACTIM/ACTECR.CSV') MBROPT(*REPLACE) +
STMFCODPAG(*STDASCII) RCDDLM(*CRLF) +
STRDLM(*NONE) FLDDLM(';') DECPNT(*COMMA)

ACTECR is my file in the DB2 database upstream, and I create the file ACTECR.CSV under the IFS
The field separator is ;
and the decimal separator is the comma.
0
Answer 6 / 6
wbr Posted messages 1 Registration date   Status Member
 
Hello VP29,
Actually, I am now looking to copy this file (result of cpytoimpf) to a PC server.
For now, it is on the IFS and I don't know how to move it elsewhere.
Thanks and see you later.
Regards.
WBR
0