Exporting AS400 to OO-Calc or MS-Excel

  • Permalink
  • submit to reddit
  • Email
  • Follow


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
Reply LandSr (7) 8/16/2006 6:50:01 AM

See related articles to this posting


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
Reply 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
Reply 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
Reply saurabh.ims (1) 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
Reply 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
Reply jonathan.bailey (325) 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
Reply SANDERPULLENS (66) 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
Reply 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
Reply arrowcomputer (237) 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
Reply pasdepub-jean-claude.monot (4) 8/18/2006 9:58:40 PM
comp.sys.ibm.as400.misc 9054 articles. 12 followers. Post

9 Replies
240 Views

Similar Articles

[PageSpeed] 10


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

how to export from ms access into ms excel?
I need to export to a particular excel worksheet, but I am unable to specify a range? Is there another way???? Please help...? Nettie ...

Exporting to MS Excel
I would like to export a result of query to Excel sheet. Mayby somebody from you know how to do this? Thanks Wojciech Wasikiewicz In article <btg7hq$40s$1@korweta.task.gda.pl>, "wojtekW" <wojtekwa@op.pl> writes: >I would like to export a result of query to Excel sheet. Mayby somebody >from you know how to do this? > >Thanks >Wojciech Wasikiewicz If the query can simply go into Excel "as is" with no special formatting then you can use DoCmd.TransferSpreadsheet. Otherwise, you will need to use automation to "push" the data into Exc...

Exporting data to MS Excel
How to export data to xls file using PHP? Is it possible to edit a look of document (like cell size, borders etc.) ? Please help. On 20 Jun, 12:13, Dawid Pustulka <DPustu...@gmail.com> wrote: > How to export data to xls file using PHP? Is it possible to edit a > look of document (like cell size, borders etc.) ? Please help. http://www.koders.com/php/fid2163EF6648BF0A5A9D58837899B249F420EF45A5.aspx?s=mary has a simple class to create a workbook in Excel97 format. phpclasses.org has some others. Alternatively a tab delimited file with a .XLS sufix will load fine into Excel. To ...

Export to Excel in MS Access
Hi, I have one form in access application which shows various fields from the database. The fields are like No of PCs a client has,No of employees,revenue etc. Need of the user is to filter the data on the form and export the results. I am using a Qury as the base for this form.Can I do it easily or i need to write the code to do this. Secondly can I provide a separate interface to allow user to Query and extract the data. Say, PC between <SomeRange> AND/ OR Revenue Between <Some Range> whatever result comes will be exported to the Excel file. Please tell me if you ...

Export to Excel / MS Project
All What techniques or products would other VO developers have used to export data to a "timeline type viewing mechanism" for multiple individual records with possibly overlapping bookings with notes. Would color code some based on certain conditions too. This items have a START and END date, arrival times, maybe wakeup calls with notes and details and there may be hundreds/thousands of these with no overlaps and some with overlaps. The over all viewing period maybe 1-2 years and the typically bookings vary from 2 - 28 days for each booking. A file that could be expor...

How to export waveforms from SIMULINK to MS Excel?
Dear friends, I have finished my modelling and now I need to export its results to excel. I tried the possibility of write data (structure with time) to workspace from scope window. then tried it with save('filename.txt', '-struct','variable name', '-ascii') from wok space but when I call the file in excel the only thing is time and about ten individual data, while my scope is include of 5 different waveform. so it seems most of data are missed. F1 Please Thanks ...

Excel>MS Query>AS400
Setup: Windows XP, Excel 2003, OS400 v5r2 I am trying to connect to the as400 using MS Excel & MSQuery but keep getting the following error when I try to connect. SQL0204 - systables in qsys2 type *file not found I was able to do this on another AS400 in the past (don't recall OS400 version). I have Client Access installed so I think I have the approporiate ODBC drivers already installed. I read somewhere that the problem might be a missing or outdated jt400.jar file. If this is the case, where do I get it & how/where do I install it? Can anyone suggest possible solutions to ...

Exporting data from Lotus Approach to MS Excel
Hi Guys, I am trying to convert our Lotus Approach databases onto an Oracle platform. To do this, I am exporting the data into an excel csv file (using file-export data in lotus approach) and then using APEX to create the Oracle tables based on the csv that I am uploading. One problem that I am having is that when I am exporting large memo fields from Approach to excel, the fields are being to truncated to 255 characters. Can anyone please advise how I can get around this problem and export the full value into the csv file please. Many Thanks Chris As I remember Approach ...

Library to create MS Excel / OpenOffice calc files?
Is there a working library that lets me create files in MS Excel format? OpenOffice calc would be good in addition, but the primary requirement is that we can create excel documents on the fly, and on a non-Windows platform. This should work by either filling an excel template or by creating an excel file from scratch with all the required formatting and filled-in data. This is possible in perl, but since we decided to implement that web application in RoR, I would prefer to do this with Ruby. -- Posted via http://www.ruby-forum.com/. On May 17, 10:36 am, Roman Hausner <roman.haus...@g...

Is it possible to export a View to Excel in MS SQL 2000 ?
Hi all, In MS SQL Management Console I can right-click on any Table and I have the option All Tasks > Export Data where I can export the table to Excel. In a View however this isn't there. I have many views I want to simply export to Excel, but the only way I've found to do it is creating an ODBC connection to the MS SQL database from MS Access, linking the Views to Access Tables, and exporting from Access. surely there's someway to export a View to Excel within MS SQL easily like exporting a table... Thanks --- Alex If this is a one-off task, the Import/Export Wizard ca...

export ms access 2003 db schema to excel
Does anyone know how to export access db schema (tables and column names) to excel? Thanks, - Parth. On 16 Apr 2007 08:57:48 -0700, "parth" <Parth.M@gmail.com> wrote: You'll have to write some VBA code to do that. Unless you'd consider dumping some records from the MSys* system tables. -Tom. >Does anyone know how to export access db schema (tables and column >names) to excel? > >Thanks, > - Parth. ...

Problem with exporting expression fields from MS Access report to Excel
Hi, I have a report in MS Access which has a couple of calculated fields at the group footer ( based on the query associated) of the report. When viewing the report it shows up perfectly, but when I try to export the data to excel it does not export these two calculated fields. The important factor to note is that these two fields are complex expressions which are not compatible with excel. I was just trying to look for any workaround for this problem.Could anyone please throw some light on this matter and also alternatively is there any possiblity of exporting these fields as some text fiel...

Exporting Notes 6.5 calendar into Excel/MS Access
Hi, I would like to export Lotus Notes (v 6.5) calendar/meeting entries into either Excel or MS Access. I am trying to create a tool that will keep track of the time that my group people have allocated to each project (based on their meeting entries in calendar). Would like to store the info in Access database and create reports/queries etc. I am not sure if this can be done and if its possible how? I am assuming that some kind of script needs to be used - can it be sql scripts? I think in version 7, there is a feature to export calendar entries. What about v 6.5? Would greatly...

What Level of TRANSFINITY are you? oo^(oo^(oo^(oo^...oo)))
Moreover, the existential quantifier is interpreted only with respect to its status as a derivative concept relative to the universal quantifier. These are the underlying assumptions of construction that allow the self-inconsistency of a specific syntactic form to be extended to a metaphysical assertion of reality. ~ MITCH (sci.logic) - - - - - - - - - - - - - - - - - - - - - - - - - - - LEVEL oo^(oo^(oo^(oo^...oo))) The True Believer! "There are SOOO.. MANY more Reals than Naturals!" - - - - - - - - - - - - - - - - - - - - - - - - - - LEVEL ((((oo^oo)^oo)^o...

OO Calc 2008 crapware can't compete with Excel 2000
OO 3.0 for Windows Calc vs Excel 2000, both running on my WinServer 2003 SP1 system, P4 3.0ghz, 2gbRAM system Test 1) create large Excel 2000 .xls file (99mb native, 1.2mb zipped). Simple structure: filled columns A-IV with 1-65536. No formulas whatsoever. Saved it with cursor in IV65536. Excel requires 2 seconds to open it, but the OpenOffice crapware literally takes 3-4 minutes to bring it on screen. Microsoft is doomed. Test 2) created an identical file in OO, saved as .ods. Impressively small - 395kb zipped, 13mb unzipped. It took 20 seconds for the poorly-coded OpenOffice sl...

A97
A97 has menu options that support exporting table data to ms excel data file format. Is this easily implemented from within code? Any examples? I looked in A97 HELP for the TransferSpreadsheet Action but there was no example code (Example appeared at the top next to 'See Also' but it was grayed out). Oops... I see now that its the TransferSpreadsheet method I should have been searching for. It has an excellent example in HELP section. However, implementing it, I get an error complaining that no installable ISAM is available. Anybody know what this means? What is an ISAM? Searching M...

about conversion of MS word to MS excel
hi, actually my job is to convert MS WORD tables into MS excel directly.......i.e. contents of 1 cell in word must come in 1 cell of excel......... but it does not happen by the conventional copy paste method..... i want to convert 4000 such pages........... can anyone help me out to do this thing with effective approach. chetan wrote: > hi, > actually my job is to convert MS WORD tables into MS excel > directly.......i.e. contents of 1 cell in word must come in 1 cell of > excel......... but it does not happen by the conventional copy paste > method..... i want to co...

Export to Excel Problem- not values exported
Hi I have been using Access 2003 to Export Values to produce time sheets for Invoices as raw data. Some time sheets are produced OK as Reports but having values on a spreadsheet is needed. When Exporting from Access 2003 the spreadsheet produced has values or text- great When Exporting from Access 2007 the spreadsheet produced has a strange linked hybrid that no Val function or anything similar can sort out. Any help Appreciated Chris ...

Not able to open MS Excel 2007 format file when I do proc export using pcfiles server
Hi, I'm able to do proc export procedure successfully using pcfiles server. code executed without errors, but not able to open MS Excel 2007. proc export data=mylib.cars dbms=excelcs outfile="Z:\File_MS_Excel_2007.xlsx" replace; server_name="&servername"; port=8621; run; Citrix Environment Client is SAS EG 5.1 and SAS 9.2 Enterprise Server Help would be much appreciated. Thanks. On Wednesday, December 12, 2012 10:37:38 AM UTC-7, Ganesh ChalamalaSetty wrote: > Hi, > > > > I'm able to do proc export procedure success...

Exporting Matlab Data to Excel and Graphing in Excel
I am trying to make a function that exports data given in matlab to a spreadsheet in excel and then plots the data in excel. I am able to export the data into the spreadsheet fine, but I can only plot the data if I have graphed previous data in the file, and it limits me to the amount I plotted previously. For example: I set up a dummy spreadsheet in excel with two different dependent variables and 10 data points each. If I export any data within that range it is fine but if I for example try to plot 3 different dependent variables and 100 data points only the first 10 data points ...

exporting data from ms access to ms project
Im wondering if any on can help me to export data out of an ms access database into ms project 2002. I want to export a start date and completion data of different projects, which is stored in the database. I think this process can be done invisible to the user, so that when they enter the data into access and save it, it will automatically create a project schedule in ms project, which they can refer to at a later date in the application. I would appreciate if any one can give me any advice on this matter, i.e. VBA code etc or good web sites etc that may be of use. Regards Rob McC, England ...

Linking MS Word to MS Excel ... DDE?
Hi All, I am working in the pharmaceutical world and am responsible for the production of the usual tables, figures and listings. Currently we use an Excel spreadsheet to house information such as program names, titles and footnotes, whether the program is finished, whether it is QC'd etc. In general I find it a neat way of documenting the progress of a project as well as providing a centralised location for titles and footnotes. Each individual program accesses the spreadsheet to retrieve the relevant titles and footnotes for its output. The problem is that, in my experience, Statistica...

Datafeed/import/export/Excel/inport/export
Hi All, I am completely new to Matlab and so im looking for a little guidance/help on developing my ideas. So any help would be greatly appreciated.. I have an excel spreadsheet with a number of financial tickers on it, I would like to first create a function so that I can read the spreadsheet, look up the tickers and then retrieve the information on the stocks via Yahoo. However I understand that Yahoo doesn&#8217;t allow multiple requests for data at the same time, so I am not sure how to get around this, and to code this in an efficient manner. Moving on from this problem I am lookin...

US-TX-Austin: Tech Support Specialist, MS Word, MS Excel, MS PowerPoint; 960 hrs (45359732406)
US-TX-Austin: Tech Support Specialist, MS Word, MS Excel, MS PowerPoint; 960 hrs (45359732406) ============================================================================================== Position: Tech Support Specialist Reference: ZYD00196 Location: Austin TX Duration: 960 hr Skills: Microsoft Word, Microsoft Excel, & Microsoft PowerPoint. Power user in formatting with Microsoft Word & Microsoft Excel working with documents that contain features such as multiple fonts, headers, footers, footnotes, table of ...