Import from excel to AS400 file/table

[ermagarden]

we're doing an application to export from AS400 table/file to excel and then import from excel to the same table...

Now, we're using VB and "client access" provider (i'm guessing this is an old version of AS400 provider) . Is it true that the import has to be done row by row....so read the excel file row by row and then insert into AS400 table row by row??

[giri]

need not be ......

if you are performing the import/export on AS400 side ....
and if you are using Client access,
then u can do this by Client access data transfer file.

C:\Program Files\IBM\Client Access\rfrompcb.exe
C:\Program Files\IBM\Client Access\rtopcb.exe

These are exe files for files transfer between AS400 and PC.

Using STRPCCMD,
execute these exe files and pass the necessary parameters.

[ermagarden]

anyway to run this thru iSeries navigator?

And I found this link regarding what you sent..seems like this is like SQL Server's "export" functionality..

http://www-912.ibm.com/s_dir/slkbase...d?OpenDocument



However, my manager wants this export/import done via an application (VB).

[giri]

Because the two files rfrompcb.exe and rtopcb.exe
are exe files provided by IBM client access for the data transfer between PC and AS400, I guess you still can use them in your VB program.

I tried in my AS400 programs but i never tried using VB applications.
But i guess it will work ....

[littlepd]

Is it true that the import has to be done row by row....so read the excel file row by row and then insert into AS400 table row by row??

No, this is not true. The iSeries Access OLEDB provider allows you to retrieve data from the i5 as an ADODB.recordset.

This code utilizes the OLE DB provider that comes with iSeries Access for Windows. If you don't supply user id and password, you will be prompted for them when the "objConn.Open" statement is executed.

Set objConn = New ADODB.Connection
objConn.Open "Provider=IBMDA400;" & _
             "Data Source={your IP address};"

Set objRS = New ADODB.Recordset
With objRS
    .ActiveConnection = objConn
    .Source = "SELECT ITM05 FROM MYLIB.ITEMSTEP"
    .Open

    lngOffset = 0
    If Not .EOF Then
       'add field headers
        For Each objFields In .Fields
            Sheets2.Offset(0, lngOffset).Value = objFields.Name
            lngOffset = lngOffset + 1
        Next objFields
        Sheet2.Resize(1, .Fields.Count).Font.Bold = True

       'dump the recordset to the spreadsheet
        Sheet2.Offset(1, 0).CopyFromRecordset objRS
        Sheet2.UsedRange.EntireColumn.AutoFit
    Else
        MsgBox "Error: no records returned.", vbCritical
    End If
    .Close
End With

Or, if you like, you can read through the recordset one record at a time.

[ermagarden]

This is reading from the database into spreadsheet (not row by row) but as recordset...

so the reverse :reading from the spreadsheet and dupming into the table can be done as well without doing it row by row..correct?

"The iSeries Access OLEDB provider allows you to retrieve data from the i5 as an ADODB.recordset" ...can it read from excel as recordset (not row by row)

[littlepd]

This is reading from the database into spreadsheet (not row by row) but as recordset...

so the reverse :reading from the spreadsheet and dupming into the table can be done as well without doing it row by row..correct?

"The iSeries Access OLEDB provider allows you to retrieve data from the i5 as an ADODB.recordset" ...can it read from excel as recordset (not row by row)

Creating a recordset with the Excel worksheet as the source:

Sub Main()

    Dim conn As ADODB.Connection
    Dim rec As ADODB.Recordset

    Const strSQL As String = "SELECT * FROM [Sheet1$]"
    Const strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                            & "Data Source=c:\TextA.xls;" _
                            & "Extended Properties=""Excel 8.0;HDR=YES"";"
    
    Set conn = New ADODB.Connection
    Set rec = New ADODB.Recordset
    conn.Open strConn
    rec.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly
    
    'process the recordset - put it in an array with GetRows or whatever
    
    rec.Close
    Set rec = Nothing
    conn.Close
    Set conn = Nothing

End Sub

However, there is no CopyToRecordset function that would allow you to create a recordset with a DB2 table as the source, then automatically copy all the records from a worksheet into that recordset, thus updating the data in the table. This does not exist, for one reason, because the database table is much more strict about data typing than Excel is. And there would be no way for the function to know whether the cells being copied into the table have the correct attributes.

There are probably many other reasons, too.

So the way you would do this is to create a recordset that points to a table in the database, then read through a range in the worksheet, and add records to the recordset one at a time. Then you could use either the .Update method of the recordset object to update each record into the table, one at a time, or the .UpdateBatch method to update the entire recordset at once.

Does that clear things up?

[ermagarden]

thanks for your help and the code.