RE: Automate upload of Excel data

Thanks, this might help us automate some of what we need to do.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of
AGlauser@xxxxxxxxxxxx
Sent: Wednesday, June 27, 2007 10:07 AM
To: Midrange Systems Technical Discussion
Subject: Re: Automate upload of Excel data


Bryan Burns wrote on 27/06/2007 09:31:11:

I've been asked to automate the manual process of uploading a file
and I'd like some suggestions.

The current process is to

1. open a Microsoft Outlook email
2. open the Excel CSV spreadsheet attachment that's part of the email
3. alter the spreadsheet by adding a column and filling the column
with a distributor ID
4. upload the CSV file data to the iSeries using a saved Client
Access Transfer function

Is there a third party application that can perform 1, 2 and 3? Can
item 4 be performed with a CLP?

I'm not sure about how to address 1, but if you can find a way to get the
CSV file on to the IFS, then you could address 2-4 fairly easily.

1. Open the CSV file in an RPG program.
2. Read the first line variable.
3. Extract each field by reading character by character until you find a
comma.
4. Assign the extracted info to the appropriate database field.
5. Assign the distributor ID based on your rules used in your step 3
6. Repeat 3-5 for each line in the CSV.

If you need more information on accessing stream files from RPG, there has
been much discussion of this in the archives for the RPG list. The
archive server is down today so if it is time sensitive, you could repost
and we can give you some links.

HTH,
Adam