Importing as/400 file info to excel
Cole
Is it possible to populate an excel spreadsheet with file info from
an AS/400 library(s)? I need to track information like file name,
number of records, date last changed, etc. for selected files
throughout our system.
Thanks
Cole
Andi Goering
convertion to .csv can be done with command CPYTOIMPF (minimal V4R4
required).
Excel can open the csv-file, but some additional work must be done
after that.
If you are searching for a tool which generates REAL EXCEL (means
.XLS) then have a look at http://www.goering.us
Our tool iExcelGen generates perfect Excelsheets without any formating
needed after generation.
Comes also with an built-in eMail Client and an optional Spool Tool to
get AS/400 spools to Excel Columns with ease!
Regards
Andreas Goering
GOERING iSeries Solutions
cole...@yahoo.com (Cole) wrote in message news:<38a074e8.03013...@posting.google.com>...
Martin Rowe
Hi Cole
You can do a DSPFD to an outfile for the files you are interested in.
DSPFD FILE(filelib/myfile) TYPE(*MBR) OUTPUT(*OUTFILE) FILEATR(*PF *LF)
OUTFILE(mylib/file_info)
will give you the data you need in a regular AS/400 DB2 file. You can target
just a single file, or use generic criteria to pull in more. To get the
data into a PC file on the IFS for Excel, use your tool of choice: CA file
transfer, IBM's CPYTOIMPF, one of the many commercial options or even my
free CRTCSVSTMF[1] :)
Regards, Martin
[1] http://www.dbg400.net/crtcsv.html
--
mar...@dbg400.net AIM/Gaim: DBG400dotNet http://www.dbg400.net /"\
DBG/400 - DataBase Generation utilities - AS/400 / iSeries Open \ /
Source free test environment tools and others (file/spool/misc) X
Debian GNU/Linux | ASCII Ribbon Campaign against HTML mail & news / \
James Durr
If you are interested in a commercial solution check out our CVTDBFXLS
(Convert Database File To Excel)
command. The CVTDBFXLS command can convert an AS/400 file
to an Excel spreadsheet with formulas, fixed headings/columns and
multiple sheets.
Visit wwww.kemetech.com to learn more and to request a 30 day evaluation
copy.
James Durr
Technical Consultant
KemeTECH Systems Inc.
103 Vassar Avenue, Newark, NJ 07112-2249
Phone( 973) 923-2328 e-fax (978)231-5825
jd...@kemetech.com
--
James Durr
Technical Consultant
KemeTECH Systems Inc.
103 Vassar Avenue, Newark, NJ 07112-2249
Phone( 973) 923-2328 e-fax (978)231-5825
jd...@kemetech.com
Richard Schoen
can be used to provide realtime access to Excel spreadsheet data. You can
insert, update or delete records as well as read selected data from Excel
spreadsheets right from any ILE RPG program.
For more info, contact me directly since our full product release for
RPG2SQL is still a few weeks away.
Regards,
Richard Schoen
RJS Software Systems Inc.
"The AS/400 and iSeries Report and Data Delivery Experts"
Email: ric...@rjssoftware.com
Web Site: http://www.rjssoftware.com
"Cole" <cole...@yahoo.com> wrote in message
news:38a074e8.03013...@posting.google.com...
Bengt Persson
"Andi Goering" <andi_g...@yahoo.de> wrote:
<snip>
> convertion to .csv can be done with command CPYTOIMPF (minimal V4R4
> required).
> Excel can open the csv-file, but some additional work must be done
> after that.
I used to run CPYTOIMPF like this, and I never needed to do any additional
work!?
CPYTOIMPF FROMFILE(LIB/FILE) TOSTMF('/Lib/file.xls') MBROPT(*REPLACE)
STMFCODPAG(819) RCDDLM(*CRLF) FLDDLM(X'05')
Now I always use CRTCSVF (free) from http://homepage1.nifty.com/uzaemon/
CRTCSVSTMF (free) from http://www.dbg400.net/crtcsv.html is also great.
(There is absolutely no need to buy products for this!)
If you are dealing with large files and/or performance is an issue, perhaps
our
not so scientific "benchmarks" can give some hints of what to use...
File: 44 Mb. 200 000 records. 18 fields. Record length 211.
Tool Cpu used Run Time
seconds seconds
CPYTOIMPF 44 140
CRTCSVF 117 125
CRTCSVSTMF 359 819
Not that bad for good old CPYTOIMPF dont you think!
--
Bengt
Cole
I think I like your solution the best. This will make things a lot
easier for me.
Cole
Martin Rowe <ne...@dbg400.net> wrote in message news:<b1ft8s$1371ap$1...@ID-141705.news.dfncis.de>...
Alex Nubla
1. First, dump your library into an output file (i.e. replace ALEX
with your library):
DSPOBJD OBJ(ALEX/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE)
OUTFILE(ALEX/MYLIST)
2. Go to my website (http://www.as400journal.com), and in the Tips &
Technique page, download the Convert Physical file to Excel
Spreadsheet (CVTPFXLS). This is a freeware and will handle all the
new data format introduced for V5R1.
CVTPFXLS FROMFILE(ALEX/MYLIST) TOSTMF('/home/myreport/mylist.xls')
FLDDLM(*TAB) FLDHDR(*NONE) STRDLM(*NONE)
Once you've executed the commands, you will find your information in
the IFS '/home/myreport/mylist.xls'
Regards,
Alex Nubla
cole...@yahoo.com (Cole) wrote in message news:<38a074e8.03013...@posting.google.com>...
Drew Dekreon
It contains every object on the system. Syscolumns contains all the
fields, by file..
Cole
That sounds like a great idea but unfortunately I don't have
access it seems to that library. I'll have to look into that a
little.
Cole
Drew Dekreon <drew_d...@chugachelectric.com.x.com> wrote in message news:<v3u1gbk...@corp.supernews.com>...
adityab...@gmail.com
Frederick P.
> hi, i'm in a data migration project which involve As400. I need to prepare a log info of the data schema, that is the File description like Field Name, Data Type, Field Length, Start Position, Alias Field Name. I need to import this info into excel. Could you help me with this?
You can do this pretty easily with a couple of commands. Unfourtunately, you will have to write a CL program or script to loop through the files in the librarie(s).
DSPFD library/file *OUTFILE *LIBL/FILEDESCRIPTIONS (Whatever you want to call it, it will give you the create/change dates etc.)
DSPFFD library/file *OUTFILE *LIBL/FILEFIELDS (This will give you the fields, type and length)
DSPDBR library/file *OUTFILE *LIBL/DBRELATIONS (This will give you logicals attached to a physical and any Dependancies).
There's a few other tricks too. You can just download these table into Excel/CSV through Navigator for i or you can press the download button on the toolbar or the 5250 Emulator (Green Screen), the old or the new Java version.
Thanks