RE: Automate upload of Excel data -- MIDRANGE-L

Here is the guts of it.

References used are ADODB.dll to connect to the iseries.
I also import system.io.

Dim cn As New ADODB.Connection
Dim strContents, cmd, spliter(12) As String
Dim oFile As System.IO.File
Dim oRead As System.IO.StreamReader
Dim yy As Integer

cn.Open("Provider=IBMDA400;Data Source=" & "AS400", "VBUSER", "VBUSER")

oRead = oFile.OpenText("c:\file.csv")
While oRead.Peek <> -1

strContents = Replace(oRead.ReadLine(), """", "")
spliter = strContents.Split(",")

yy += 1

If yy > 1 Then
cmd = "insert into library.file values("
cmd = Trim(cmd) & "'" & Format(spliter(3), "Short Date") & "', "
cmd = Trim(cmd) & "'" & spliter(0) & "', "
cmd = Trim(cmd) & "'" & spliter(1) & "', "
cmd = Trim(cmd) & "'" & spliter(2) & "', "
cmd = Trim(cmd) & spliter(4) & ", "
cmd = Trim(cmd) & ")"
cn_iSeries.Execute(cmd)
End If

End While

oRead.Close()

' call iseries pgm to process uploaded data
cmd = "{{CALL LIBR/PGM}}"
cn_iSeries.Execute(cmd)

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Krebs
Sent: Wednesday, June 27, 2007 12:21 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Automate upload of Excel data

Tim,

I am interested in seeing how you did the VB option. Can you post code
without giving away any secrets?

Mike (not the OP)

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of tim
Sent: Wednesday, June 27, 2007 9:23 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Automate upload of Excel data

Bryan,

There are 2 techniques I use. One is to copy the CSV file into the IFS and
then read the IFS file and parse out the fields on my own. Another one is
using VB to open excel object, read the data and write it to the iseries
using SQL insert.

If either sounds like its something you would like more info on, let me
know.

Tim

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Burns, Bryan
Sent: Wednesday, June 27, 2007 9:31 AM
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: Automate upload of Excel data

I've been asked to automate the manual process of uploading a file and I'd
like some suggestions. I've done some searching but can't find the exact
process I'm looking for. I've found a lot on loading Excel with iSeries
data but we want to do the reverse. We want to upload an Excel spreadsheet
to the iseries automatically.

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?

Thanks in advance,

Bryan Burns
IBM Certified Specialist - iSeries System Command Operations V5R2 M.I.S.
Department ECHO, Incorporated www.echo-usa.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.