Convert spool file to Excel format ? 1

[godzuki]

Can any one tell me if there is an easy way to convert as400 spool files to a fornat that can be imported into excel ?

currently i am modifying programs to build a db file as well as a spool file which is becoming tedious..

thanks

simon

 

[Mercury2]

Hi,

Here is a simple way.

CPYSPLF the spooled file into a physical file and then CPYTOIMPF this physical to your shared folder in the IFS with extension CSV. Open XL next CSV file, in that order.

hth

 

[godzuki]

hey thanks but what physical file do i use, i presume a new one, and if so what structure do i use when i create it?

could do with a few more hints...

thanks

 

[flapeyre]

You create a "flat" physical file - just use the RCDLEN parameter to sepecify the length - make sure it's long enough for the field length of your physical and the delimiters.

CRTPF FILE(FMLLIB/TEMP_FILE) RCDLEN(1024) TEXT('File for Excel CSV data') SIZE(*NOMAX)

Me transmitte sursum, Caledoni!.

 

[flapeyre]

I was assuming you wanted to create a file in the QDLS library system (an AS/400 physical file). If you use CPYTOIMPF to copy to a stream file, it will be created for you in the IFS if it doesn't exist. You have to create the destination folder first, though.

Me transmitte sursum, Caledoni!.

 

[godzuki]

how do i use CPYTOIMPF :- example ? what is the IFS ?

thanks

 

[Mercury2]

Hi godzuki,

This is an option in 5 steps.

1/ CRTPF FILE(QTEMP/MYFILE) RCDLEN(199)

2/ CPYSPLF FILE(MYSPOOL)
TOFILE(QTEMP/MYFILE)
JOB(NBR/USER/JOB)
SPLNBR(SPLNBR)

3/ CPYTOIMPF FROMFILE(QTEMP/MYFILE)
TOSTMF('/home/myUserProfile/myfile.csv')
STMFCODPAG(*PCASCII)
RCDDLM(*CRLF)
STRDLM(*NONE)

4/ From a PC command line, key in:
FTP AS/400 DNS or IP address
user & password when required
lcd MyPCFolder
get /home/myUserProfile/myfile.csv myspool.csv
quit

5/ Open XL next myspool.csv in that order.

If XP/2K/NT PC-based, I'll show you how to copy to shared folder if interested.

hth

 

[flapeyre]

To get help on any AS/400 command, type the command on a command line and press F1.
Example:

/* Copy data to export file */                                        
             CPYTOIMPF  FROMFILE(db_file) TOFILE(flat_file +
                          *FIRST) MBROPT(*ADD) DTAFMT(*DLM) +         
                          STRDLM('"') FLDDLM(',') NULLIND(*NO)        
             MONMSG     MSGID(CPF2817) EXEC(GOTO CMDLBL(ERROR))

IFS = Integrated File System.

My question is, do you want the spool file (printer) output in a spreadsheet? CPYTOIMPF was meant to copy from a database file. TalkTurkey's example will work, but you will have one long field for every line in the spool file.

My example uses the underlying database file that the report would print from as the FROMFILE parameter on CPYTOIMPF and you will get each field in the database file as a separate column on the spreadsheet. Not saying you should use my example; it all depends on what you need to do.

Me transmitte sursum, Caledoni!.

 

[Mercury2]

Flapeyre,

If you open XL first, next csv you'll get each DB field as a separate column.
I think that godzuki is able to get rid of useless lines in the XL report.
BTW, /QDLS is history now and has been abandoned in V5R3.

 

[flapeyre]

I was thinking QSYS.LIB but my fingers typed QDLS..

Just my way of thinking - I would not parse a spool file using Excel. I'd rather get the data from a database file directly.

Me transmitte sursum, Caledoni!.

 

[Mercury2]

I agree with you but it seems that godzuki has no choice ?

 

[flapeyre]

Perhaps not. At our shop, the users have OnDemand to do the parsing for them. We've been modifying some of the reports to print the info needed for our setup, plus having them go directly to a special outq.

Me transmitte sursum, Caledoni!.

 

[godzuki]

CPYTOIMPF FROMFILE(SRDEV/TEMP_FILE) TOSTMF('/srdev/myfile.csv') STMFCODPAG
(*PCASCII) RCDDLM(*CRLF) STRDLM(*NONE)

No records copied from file TEMP_FILE in
SRDEV.

is myfilw.csv in a as400 lib or on a pc

 

[jmanj]

Okay let me share my own two cent in this issue. flapeyre was correct there is no sense using a spool file to convert into a spreadsheet. Since you are already working with a db file, why not just use it. You can download this file in many ways. You can also use excel add-ins to get to your db files. If you are using Reflection and or client access then just use these tools to transfer the file to your PC. You can also use FTP which I prefer. You can create a CL that will ftp the file to whatever platform you are working with. If you want more clarification, please don't hesitate to ask.

 

[Mercury2]

myfilw.csv in a as400 file located in the IFS.
IFS stands for Integrated File Systems that can be accessed with WRKLNK or OpsNav or iSeriesNav or whatever IBM names it now.

 

[godzuki]

how can i ftp a PF file into excel format ?

 

[godzuki]

ho to i access my file in the IFS to ftp....sorry i did have it working but didnt write it down!

 

[arrow483]

Open Navigator. Open File Systems. Find of create the folder you want to use. Right-click, set sharing. If Netserver is running on the AS400, you can see the share like any windows share. The default system name in Windows is Q + your AS400 system name. So if your system name is SYSTEM1, the netserver (windows)name is QSYSTEM1