Solved

Reading from Excel sheet that is placed in a folder (PC) and uploading into AS400

Posted on 2004-09-06
14
736 Views
Last Modified: 2011-10-03
Hello,

I have a situation like this.

we have a folder on PC that contains Excel sheet. We need to read that data from excel sheet and that has to be uploaded into AS400. This process has to be automated.
Is there any method of doing this. This is very urgent. Please respond to this question ASAP.

Thanks in Advance.
Prema
0
Comment
Question by:premavani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
14 Comments
 
LVL 3

Expert Comment

by:Mind_nl
ID: 11995542
you can use Run Remote Command (RUNRMTCMD) from the AS/400 to start a client access upload on the PC. This could also be put in the AS/400 jobscheduler. You'll need the 'Incoming Remote Command' part of client access installed on the PC
0
 
LVL 33

Accepted Solution

by:
shalomc earned 125 total points
ID: 11999683
Hey,

There are several other option available.

First, you can save the excel as a comma separated variable file (CSV), upload it into the AS400 and use CPYFRMIMPF to load it into a database file.

Second, on Windows, you can treat an Excel file like a small database: there is an ODBC driver for excel which supports simple SQL, in particular the "select" statement. This means that you can have a program on the PC which opens an ODBC connection to the excel, opens another to the AS400, and copies the contents of the Excel file into an AS400 table.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetmicrosoft_excel_driver_programming_considerations.asp

Yet another option is to maintain the Excel as an XML file (try save as...), and write some AS400 or PC code to parse it and load to the database.
The AS400 actually has its own copy of Xalan, so if you are an XML person, you will find it easy to manipulate the XML file.

ShalomC
0
 

Author Comment

by:premavani
ID: 12003338
Thanks for ur early response Mind nl and ShalomC.

But im not clear with ur solution Mind nl. Can u explain in detail.
Also can u give the syntax of RUNRMTCMD with an example like what should be the value of command parameter and etc.,
Please explain me the different steps involved to achieve this.

Thanks
Prema
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:premavani
ID: 12003439
Hi ShalomC

How to save Excel file as a CSV file?
How to upload this CSV file into AS400 and to which directory or library?
what is 'Stream file' parameter in CPYFRMIMPF command?
Can u explain in detail.
If possible explain all the steps that is required to do this with an example.
Please respond to this ASAP.

Thanks
Prema
0
 
LVL 33

Expert Comment

by:shalomc
ID: 12003504
Saving Excel as CSV:
------------------------
Open the Excel worksheet, open the "save as" dialog, select type "CSV", assign a name to the file and click the "Save" button. For this example, assume the file is called "Prema.csv" in directory E:\temp

Creating a directory on the AS400:
----------------------------------------
MKDIR '/incoming'
(or any other name you fancy.)

Uploading the CSV file to the AS400:
------------------------------------------
Using FTP which exist on any PC

Create the following batch file and place it in E:\temp, with the CSV

echo off
E:
cd \temp
echo open AS400_server > cmd.ftp
echo user >> cmd.ftp
echo password >> cmd.ftp
echo quote site namefmt 1  >> cmd.ftp
echo cd /incoming  >> cmd.ftp
echo ascii >> cmd.ftp
echo put prema.csv >> cmd.ftp
echo quote rcmd CALL PREMALIB/PROCESSCSV >> cmd.ftp
echo quit >> cmd.ftp
ftp -s:cmd.ftp
del cmd.ftp


Importing the uploaded CSV file into an AS400 table
------------------------------------------------------------
In the example, a CL program called PREMALIB/PROCESSCSV contains the CPYFRMIMPF command.

The 'Stream file' parameter should be '/incoming/prema.csv'
The database file must be constructed according to the data types and number of columns found in the Excel worksheet.


Cheers,
ShalomC
0
 
LVL 3

Expert Comment

by:Mind_nl
ID: 12003863
The runrmtcmd in your CL program yould look something like this:

RUNRMTCMD  CMD('C:\UPLOAD\UPLOAD.BAT') +
                     RMTLOCNAME(&REMOTE *IP) RMTUSER(&USER) +
                     RMTPWD(&PASSWORD)

On the PC you'll have to start the Client Access Remote Command service (under services in your control panel)

The UPLOAD.BAT would contain some checking for existence of your PC file and a client access data transfer
0
 

Author Comment

by:premavani
ID: 12005154
Hi ShalomC,

Thanks a lot...Its working perfectly fine...
Thanks u so much....

One more query...

How to Schedule this Batch file. I need to run this batch file once in a day...
How to set that....

Prema.
0
 
LVL 3

Expert Comment

by:Mind_nl
ID: 12006848
If you create a CL program with above mentioned RUNRMTCMD  you can use the AS/400's jobscheduler. Or you can try the windows task scheduler to run the batch every day
0
 
LVL 33

Expert Comment

by:shalomc
ID: 12008267
I advise against using RUNRMTCMD in this case.
First, to use this command, you need a REXEC service running on the PC.
Second, reserve RUNRMTCMD to cases when an AS400 event must trigger a PC event. In this case - a simple PC scheduler will work perfectly, without the added complexity of yet another client/server.

ShalomC
0
 
LVL 3

Expert Comment

by:Mind_nl
ID: 12016414
Good point you have there shalomc, I have used this once when the PC side program would have to start after the AS/400 side processing had finished. So in this case I guess my second sugestion, the windows task scheduler,  would be the best thing to do...
0
 
LVL 14

Expert Comment

by:daveslater
ID: 12017721
Hi
one solution I have been looking into for this whole AS/400 - PC Excel stuff is java based.

I have not had much time to develope but there is JAVA open source that can read and write to Excel workbooks

here is one

http://www.andykhan.com/jexcelapi/

Dave
0
 
LVL 14

Expert Comment

by:daveslater
ID: 12075485
Hi
Check out these babies available on the seriesnetwork
You must register first but the articals are free

http://www.iseriesnetwork.com/resources/artarchive/index.cfm?fuseaction=viewarticle&CO_ContentID=17839
http://www.iseriesnetwork.com/resources/artarchive/index.cfm?fuseaction=viewarticle&CO_ContentID=18409


The sample source code that I've written for this article demonstrates
how to call this service program, and it also demonstrates a more
complex sample workbook -- including one that has multiple sheets --
and shows you how to use larger fonts and merge cells. You can
download this article from the iSeries Network Web site at the
following link:
http://www.iseriesnetwork.com/noderesources/code/clubtechcode/ExcelCrtDemo.zip .

More information about calling Java methods from RPG programs can be
found in the "WebSphere Development Studio ILE RPG Programmer's
Guide," which is in the Information Center at the following link:
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/books/c0925074.pdf

The following is a link to the January 8, 2004, issue of this
newsletter, where I demonstrated reading an Excel file in RPG:
http://www.iseriesnetwork.com/resources/clubtech/index.cfm?fuseaction=ShowNewsletterIssue&ID=17839

Geert Van Landeghem also has a tutorial online that demonstrates how
to use these Java classes to create an Excel spreadsheet. You can read
his tutorial at the following link:
http://www.jasservices.com/articles/as400/001_genexcel.htm

Dave

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

First of all let me say that the only language that I speak is English, but in answering questions here I often come across people whose English skills are not the best and I’d like to be able to communicate better with them, and the following descr…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question