RE: Automate upload of Excel data

["Burns, Bryan" <Bryan_Burns@xxxxxxxxxxxx>]

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