Exporting AS400 to OO-Calc or MS-Excel

[LandSr]

Hi all,

I have also asked the question in the programming group. There seems to
be little activity there.

My question is:
Can someone point me to an easy way to export an AS400 Database to an
external file such as OO-Calc or MS-Excel (CVS). I come from MS and
Linux, and have very little experinece with AS400. I have an AS400
operator who can get to the tables etc. He does not know how to export
and access the AS400 from outside over TCP/IP, other than working with
the Access Client of IBM. How can I get the MS-Like NT server running
on the AS400, and how can I get ftp to run? How do I export the file
and how can I share the MS-Like files/directories?

Linux users wrote a great number of How-To's to get things done on it,
and publish it on the Linux Documentation Project website
(http://www.tldp.org/). Is there something similar for the AS400?

Any help greatly appreciated.

:-)
LandSr

[LandSr]

Post by LandSr
Can someone point me to an easy way to export an AS400 Database to an
external file such as OO-Calc or MS-Excel (CVS).

Not CVS but CSV-Export

:-/
LandSr

[LandSr]

Great, I can get on with ftp! Half way there.

The AS400 admin said that the right place for the export 'file' is in
Library QGPL (I gather in my world a Library is like a directory).

With "get QQRYOUT.QQRYOUT" I retrieved:

"Retrieving member QQRYOUT in file QQRYOUT in library QGPL "

as a binary file.

I can't make sense of the contents, but if I can now get a CSV file
exported by the AS400, I'm there.

Q: How do I create a CSV of the database?

:-)
LandSr

[Jonas Temple]

Post by LandSr
Great, I can get on with ftp! Half way there.
The AS400 admin said that the right place for the export 'file' is in
Library QGPL (I gather in my world a Library is like a directory).
"Retrieving member QQRYOUT in file QQRYOUT in library QGPL "
as a binary file.
I can't make sense of the contents, but if I can now get a CSV file
exported by the AS400, I'm there.
Q: How do I create a CSV of the database?
:-)
LandSr

If you're wanting to do this on a one-time basis then you could try my
freeware program FROG (http://www.yhti.net/~jktemple). With FROG you
can download an AS/400 database file to CSV, HTML, text and XML. If
you're looking to do this unattended then FROG may not be the solution.

HTH,

Jonas

[Jean-Claude]

Post by LandSr
Great, I can get on with ftp! Half way there.
The AS400 admin said that the right place for the export 'file' is in
Library QGPL (I gather in my world a Library is like a directory).
"Retrieving member QQRYOUT in file QQRYOUT in library QGPL "
as a binary file.
I can't make sense of the contents, but if I can now get a CSV file
exported by the AS400, I'm there.
Q: How do I create a CSV of the database?
:-)
LandSr

Command CPYTOIMPF, no ?
You give the database, the delimiter ("), the separator (,), and your result
is a csv file on AS400.
You only have to transfer it to Excel.

Jean-Claude

[sonu]

Hi,
You can use the Data Transfer from ISeries and in this dialog box give
the file name as follows.

Libname/Filename(fileName)
and in the bottom half select foutput device as File and click on
Detalis Button and from there select the File type as BIFF7(MS Excel 7)
and click ok
now on main box select the location and clcik ok
u will have ur data in the form of Excel.


Thanks,
Saurabh

Post by LandSr
Hi all,
I have also asked the question in the programming group. There seems to
be little activity there.
Can someone point me to an easy way to export an AS400 Database to an
external file such as OO-Calc or MS-Excel (CVS). I come from MS and
Linux, and have very little experinece with AS400. I have an AS400
operator who can get to the tables etc. He does not know how to export
and access the AS400 from outside over TCP/IP, other than working with
the Access Client of IBM. How can I get the MS-Like NT server running
on the AS400, and how can I get ftp to run? How do I export the file
and how can I share the MS-Like files/directories?
Linux users wrote a great number of How-To's to get things done on it,
and publish it on the Linux Documentation Project website
(http://www.tldp.org/). Is there something similar for the AS400?
Any help greatly appreciated.
:-)
LandSr

[LandSr]

Post by sonu
Hi,
You can use the Data Transfer from ISeries and in this dialog box give
the file name as follows.
Libname/Filename(fileName)
and in the bottom half select foutput device as File and click on
Detalis Button and from there select the File type as BIFF7(MS Excel 7)
and click ok
now on main box select the location and clcik ok
u will have ur data in the form of Excel.

Hi Sonu,

Thanx for the info.

I can get all the files by ftp. My problem is just to create a csv/txt
(comma delimited) file with a query or something in the AS400.

I believe you refer to the Client Access transfer option of Data
Transfer. We have a problem with the language codes between a German
user QCCSID and the French Database. The OS is a French (Swiss), the
client is French and the Database is in German (Swiss). The Client
Access cannot convert the language specific characters for some reason.


We want to just dump/copy the database into a text file and copy it by
ftp. Is there a way in the AS400 to do that, and be sure it is in an
Excel fromat?

:-)
LandSr

[a***@bigfoot.com]

Use Jonathan's suggestion CPYTOIMPF

Note that if NetServer is running on the iSeries, you can setup a
Windows share on the IFS and just copy (or open) from these.

Another option is to send the file directly to a Windows network share
using QNTC file system.

Here's a sample I have been using

CPYTOIMPF FROMFILE(*LIBL/CUSTPRFIP) TOSTMF(&CSVFILE)
MBROPT(*REPLACE) STMFCODPAG(*PCASCII)
RCDDLM(*CRLF)

where *LIBL could be QGPL in your case, and &CSVFILE is a variable - in
my case intialized as

/QNTC/floserver1/IP/IPOnTime.csv

(windows server "floserver1", windows share "IP" )

[Jonathan Bailey]

Post by LandSr
Hi all,
I have also asked the question in the programming group. There seems to
be little activity there.
Can someone point me to an easy way to export an AS400 Database to an
external file such as OO-Calc or MS-Excel (CVS). I come from MS and
Linux, and have very little experinece with AS400. I have an AS400
operator who can get to the tables etc. He does not know how to export
and access the AS400 from outside over TCP/IP, other than working with
the Access Client of IBM. How can I get the MS-Like NT server running
on the AS400, and how can I get ftp to run? How do I export the file
and how can I share the MS-Like files/directories?
Linux users wrote a great number of How-To's to get things done on it,
and publish it on the Linux Documentation Project website
(http://www.tldp.org/). Is there something similar for the AS400?
Any help greatly appreciated.
:-)
LandSr

You will most likely want to use CPYTOIMPF to generate a csv file. Try
generating a work directory in the ifs with mkdir '/home/xxx' where xxx
is your userid then chgprf HOMEDIR('/home/xxx') to set it for future
use. then you can cd to the directory.
Extract your data to csv into this dir using STMFCODPAG(*acsii) or
similar otherwise you end up with ebcdic which your pc will not like
much. Then you will have to make your 1st ftp command cd / , followed
by cd ~ to get to the directory created above.

HTH
Jonathan

[SanderP]

Have a look at the command on the following website.
Works great for me to upload db2 files to excel.
Has some nice features in it. Very easy to use.

http://www.rpgiv.org/csv.htm

SanderP