Exporting AS400 to OO-Calc or MS-Excel

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

0
LandSr (7)
8/16/2006 6:50:01 AM
comp.sys.ibm.as400.misc 9109 articles. 13 followers. Post Follow

9 Replies
312 Views

Similar Articles

[PageSpeed] 42
Errata:
> 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

0
LandSr (7)
8/16/2006 7:03:36 AM
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

0
LandSr (7)
8/16/2006 7:54:34 AM
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

LandSr wrote:
> 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

0
8/16/2006 8:16:42 AM
sonu wrote:
> 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

0
LandSr (7)
8/16/2006 8:54:37 AM
LandSr wrote:
> 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

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

0
8/16/2006 9:59:25 AM
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

0
8/16/2006 11:31:50 AM
LandSr wrote:
> 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

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

0
jtemple (209)
8/16/2006 12:24:37 PM
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" )

0
8/16/2006 6:02:26 PM
"LandSr" <LandSr@gmail.com> a �crit dans le message de news:
1155714874.142389.32140@p79g2000cwp.googlegroups.com...
> 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
>


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







0
8/18/2006 9:58:40 PM
Reply:
Similar Artilces:

Setting Hot Keys in MS Access 2000
Hi, I had created a MS Access form. The form has an "Save" and a "Close" button. I would like to set short cut keys for these buttons as "Ctrl + S" and "Ctrl + C" keys. I heard that Send keys would work...But not sure How to use them in MS Access forms. Please provide me some information on How to proceed with this. Thanks Aravind. Set the captions of your buttons to &Save and &Close. They will display with the first letter underlined, and your short cut keys will work as you desire. HTH - Turtle "Aravindhan" <aravindhan_d...

How to get font file path (for PDF export)?
What I basically need to know is the name of the font file the X server picks for me, given an XLFD, e.g. for "Arial bold" I expect something like "arialbd.ttf". Details see below: We are writing an application with Qt (bear with me, the topic will quickly become X related), the text editor provides me a data structure QFont which basically provides the font family name, the style (bold, italic) and on X11 also provides the XLFD. Assume that we limit ourselves to TTF and Type1 fonts which are stored locally on hard disk in some font path directory, as to simplif...

Re: exporting files directly from SAS to Microsoft access #5
hi ... how about just using a data step and a libname just create an empty ACCESS file (here, TEST.MDB) and the data set name (here, TABLE1) becomes the table name in access libname x access 'z:\test.mdb'; data x.table1; set whatever; run; libname x clear; -- Mike Zdeb U@Albany School of Public Health One University Place Rensselaer, New York 12144-3456 P/518-402-6479 F/630-604-1475 > yes, that's possible with SAS/ACCESS to ODBC as far as I know. > But that might be a expensive way. Does ACCESS support XML? If yes, that > might be a expensive way, but that's a...

The Sims exporter
Does anyone have an 3d studio export script for "The Sims" character meshes? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.729 / Virus Database: 484 - Release Date: 27/07/2004 ...

Ubuntu Dual Boot with MS Vista
I have an existing Dual Core Intel system I would like to set up with a dual boot option of Ubuntu. Does Ubuntu support Dual Core Intel chips? How easy is it to install Ubuntu for a dual boot scenario after M$ Vista is already installed? Is this supported from the latest install version of Ubuntu (with a partitioning option and formatting available during the install), or would I need to partition and format prior to trying to install Ubuntu? I've installed and used Red Hat and Slackware in dual boot scenarios in the past with Windows (95, NT and XP), but this will be my first shot at ...

excel automation question
I have code that formats an excel report. Most of the time this code works; however, sometimes it bombs out at the statement: With XLApp.ActiveSheet.Range(rangedef) .Select ActiveSheet.Paste .Copy .PasteSpecial Paste:=xlFormulas, Operation:=xlNone, skipblanks:=False, Transpose:=False End With With the message 'Object variable or With block variable not set (Error 91)' Why would this work sometimes but fail others? Any ideas? Thanks, Looks to me like the variable that's missing sometimes is XLApp. Is that always declared and set? What's the complete procedu...

dump dir for export
hi, i create and grant directory for export db: CREATE DIRECTORY dump_dir AS 'D:\data'; but if i want to change dump_dir value directory?? CREATE DIRECTORY dump_dir AS 'F:\datadump'; it show me error ORA-00955, how can i change dump_dir value?? thanks! Andrea wrote: > i create and grant directory for export db: > > CREATE DIRECTORY dump_dir AS 'D:\data'; > > but if i want to change dump_dir value directory?? > > CREATE DIRECTORY dump_dir AS 'F:\datadump'; > > it show me error ORA-00955, how can i change dump_dir value?? C...

Excel Tagsets No Longer Available via HTTP
I believe it was Jack Hamilton who once taught the list how to automatically get the most current Excel tagsets directly from SAS. Unfortunately, one of my users this morning complained that a job was dying because http://support.sas.com:80/rnd/base/topics/odsmarkup/excltags.tpl is no longer a live link. I did a search for "excltags.tpl" at SAS, but only found a ZIP archive containing older files. It was convenient while it lasted! ...

Exporting Objects
Let me preface this by saying that I'm not a Filemaker developer or user. I've been tasked with trying to come up with a source code control and migration strategy for our Filemaker application that we inherited. I got a brief demo from the main developer of how she codes and right now it appears she pretty much recodes changes as she moves them up from development to staging. Is this the normal approach? I believe we have FileMaker Pro version 7, if that matters. Thanks for any help with this. Dave Yes and no. For small bug fixes, I'd make them in a development vers...

Re: Creating a Exported javascript .js export file using parts of #8
This program DATA have; input value $ 1-10; datalines; set(6,31) set(9,13) set(1,34) set(8,43) set(2,83) set(3,33) ; run; /* proc sql; create table count_of_have as select count (*) as num_records from have ; quit; */ proc sql noprint; select compress(nobs) into :__num from dictionary.tables where libname="WORK" and memname="HAVE" ; quit; /* data set find_num; set count_of_have; %let num = num_records; run; */ data _null_; set have end=eof; file "H:\test_x.js"; if _N_=1 then do; put '<SCRIPT LANGUAGE="JavaScript">'; put' x = ' &quo...

The single OS nature of MS SQL
Pros ------- * Can optimize the hell out of the database because it does not have to accomodate different OSes * Code is probably cleaner - not littered with 80 million #ifdefs * No need to write the code to deal with various filesystem conventions Cons -------- * Most cheap webhosts are unix-based (I think). You can use Oracle as a professional and still do your personal home hacking on oracle regardless of whether personal hosting is done on windows or unix. Not so with MS SQL. "metaperl" <metaperl@gmail.com> wrote in message news:1151152750.028122.321180@c74g2000cwc.goo...

has ProE a programmable interface to import/export file data ?
Hi All! if it has -- where can i found docs ? Thanks vasilii No - it does not - not without a bunch work and never in the format you probably need. Check out p-AdvancedBOM - http://www.buydesignautomation.com Dave C:\Ptc\proe2001\bin\pro_batch.bat and C:\Ptc\proe2001\html\usascii\proe\interfac\about_pr.htm vasa schrieb: > Hi All! > if it has -- where can i found docs ? > Thanks > vasilii ...

Append updated Value at the end of the Excel Sheet
Hello Group, I want to append the live Stock data at the end of my excel sheet each time its run, which is scheduled to run after every 10 minute. I want the updated value to be appended after prev value. Where does proc append fits in here ? ods csv file ="C:\users\&dsn1..&ext"; /* &ext=csv */ proc print data=alix.&dsn1 noobs; var ticker price day Tradetime change open hi lo volume; run; ods csv close; run; Thanks ...

Exporting Assembly using assembly coordinate
Recently I've had a problem with exporting iges (or step) from Solidworks with the "save all components of an assembly in one file" uncheckmarked. I want to export all the parts of the assembly as separate iges files, but the problem is that when exporting, SWX doesn't use the assembly orgin, but rather each part's orgin. This make re-assembling the iges parts into an assembly impossible. This doesn't seem to be "design intent", but rather I would expect (and need) to have SWX export all parts in the assembly using the assembly orgin. BTW, I've also t...

Exporting and importing arbitrary expressions efficiently
Mathematica 5.1 says it has highly optimized import and export of binary data, plus automated encoding and decoding of .gz files. If I have Mathematica-generated data that consists of complex lists of (effectively) arbitrary expressions, what is the best way to save this to a data file and read that data file back in later? The best solution I've found for *exporting* is to use Export, saving to a .m.gz file. For example, > In[1]:= > <<DiscreteMath`Combinatorica` > > In[2]:= > Timing[ > Export["~/filetest.m.gz", > Tab...

Need help compiling an Excel AddIn (xla)
Need to get an Excel AddIn compiled for my son so he can support his thesis. I can get it to compile in VB 2005 Express, but I cannot get it to generate a .xla file, I just get a binary (dll?) formatted file and a blank setup script. "Dave Bruhn" <dmbruhn@comcast.net> wrote in message news:ct-dnVhJOeFnopDanZ2dnUVZ_u6rnZ2d@comcast.com... > Need to get an Excel AddIn compiled for my son so he can support his thesis. > I can get it to compile in VB 2005 Express, but I cannot get it to generate > a .xla file, I just get a binary (dll?) formatted file and a blank setup...

Exporting text
I use inputdlg to accept user specified text into a cell array like this: case_info = inputdlg(prompt,title,numlines); I want to export the text information stored in case_info into a text file. Is this possible? I've read through the help files for save and fprintf but can't figure it out. They all seem designed for exporting numeric data. Thanks. Tobias wrote: > I want to export the text information stored in case_info into a > text file. > > Is this possible? I've read through the help files for save and > fprintf but can't figure it out. Does this do w...

export fdf from pdf through java
Hello! How can I programmatically export an fdf file from a pdf (with a filled form inside) through Java? Kathleen It's funny that I'm adding that functionality to iText. It will be available next week. If you want you can send me one of your PDF to make sure that it will work. Best Regards, Paulo Soares kathleen.kuehmel@vbl.de (Kathleen K�hmel) wrote in message news:<4e5d0a2a.0406160526.68c64fa5@posting.google.com>... > Hello! > > How can I programmatically export an fdf file from a pdf (with a > filled form inside) through Java? > > Kathleen Thank you ...

export lotus notes settings
Hi everybody, I'm working on a big project of cloning 2000 XP machines in my company. Our messaging software is Lotus domino and we are using as e-mail client Notes 6.5.1 in a big Active Directory environnement. We will clone our XP workstations with Symantec Ghost and before cloning and restoring system images, we will collect client profiles,settings,documents etc. with Symantec Client Migration. We made some tests, almost everything was ok except Lotus Notes. We can collect some files and registry settings bu we cannot restore it succesfully on a new machine. So my quest...

API to import and export XMI
Hi, Putting it simply, I want to integrate a tool with the functionality of importing and exporting XMI (with the option to draw UML diagrams as well) into my application. Is this possible ? If not, is there a work around for this one ? Thank you. XMI is an XML language, so you should be able to read and write it using standard XML tools (off-the-shelf parsers and serializers, APIs such as SAX and DOM, etc.) Integrating that with your own application is an exercise for the student, of course... I don't know if there's a support layer available which bui...

export Display to hostname
Hi all, When I want to export display to host I got the problem that I a unable to xhost + , I believe that is due to extension of hostname. in any Xterm when I issue the command hostname I only can see hostnam like this; xgui02 Wheres it has an extension like this xgui02:17 so I need to issu command to able open display in my window; >export DISPLAY=xgui02:17 (not only xgui02 ) Does anyone know that how can I find this extension, &#305;I am sorry don t even know what it is called :confused -- xram ---------------------------------------------------------------------...

Microshaft tries microshaft OO
http://dot.kde.org/1127515635/ Very interesting read in the light of Massachussetts saying no to microshaft. Microshaft's response is that Open Document is ONLY supported by OO but as usual, education or lack of it, has let them down. KDE office also supports Open Document. No doubt many other Linux projects will too. 7 wrote: > http://dot.kde.org/1127515635/ > > Very interesting read in the light of Massachussetts > saying no to microshaft. Microshaft's response > is that Open Document is ONLY supported by OO > but as usual, education or lack of it, has let them...

US-TX-Austin: Dir. of Info Systems, 10+yrs exp, SQL MS Exchange, Webrends, C++; (45337214410)
US-TX-Austin: Dir. of Info Systems, 10+yrs exp, SQL MS Exchange, Webrends, C++; (45337214410) ============================================================================================= Position: Dir. of Info Systems Reference: SMC01864 Location: Austin TX Duration: Perm Skills: 10+yrs exp in designing, implementing, and managing secure information systems supporting multiple business units within an organization. Practical working knowledge of SQL and relational databases, Microsoft Exchange, Webtrend...

the (non) joy of exporting dll functions
Hey. I'm working on a dll and I'm trying to export the Dll(Un)RegisterServer/GetClassObject/CanUnloadNow functions.... i've tried both the def file and the __declspec(dllexport) method and i still get the same result "the entry point was not found" what i need your help with is this.... here is the definition for one of the functions: #ifdef MWSIETOOLBAR4_EXPORTS #define MWSIETOOLBAR4_API __declspec(dllexport) #else #define MWSIETOOLBAR4_API __declspec(dllimport) #endif MWSIETOOLBAR4_API STDAPI DllUnregisterServer(void) { //code ...

exporting OLAP to export
Hi - Can SPSS export a OLAP table to excel as a pivot table (excel 2010)? ...