Results 1 to 8 of 8

Thread: Import from excel to AS400 file/table

  1. #1
    Analyst
    Join Date
    Jul 2006
    Location
    USA
    Posts
    173
    Rep Power
    772

    Import from excel to AS400 file/table

    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??

  2. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

  3. #2
    Analyst giri's Avatar
    Join Date
    May 2005
    Location
    Salt lake City, US
    Age
    33
    Posts
    247
    Rep Power
    1655

    Re: Import from excel to AS400 file/table

    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.
    Thanks,
    Giri

  4. #3
    Analyst
    Join Date
    Jul 2006
    Location
    USA
    Posts
    173
    Rep Power
    772

    Re: Import from excel to AS400 file/table

    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).

  5. #4
    Analyst giri's Avatar
    Join Date
    May 2005
    Location
    Salt lake City, US
    Age
    33
    Posts
    247
    Rep Power
    1655

    Re: Import from excel to AS400 file/table

    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 ....
    Thanks,
    Giri

  6. #5
    Moderator littlepd's Avatar
    Join Date
    Mar 2005
    Location
    Lewisville, TX
    Age
    50
    Posts
    1,111
    Rep Power
    3340

    Re: Import from excel to AS400 file/table

    Quote Originally Posted by ermagarden View Post
    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.

    Code:
    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.
    "Time passes, but sometimes it beats the <crap> out of you as it goes."

  7. #6
    Analyst
    Join Date
    Jul 2006
    Location
    USA
    Posts
    173
    Rep Power
    772

    Re: Import from excel to AS400 file/table

    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)

  8. #7
    Moderator littlepd's Avatar
    Join Date
    Mar 2005
    Location
    Lewisville, TX
    Age
    50
    Posts
    1,111
    Rep Power
    3340

    Re: Import from excel to AS400 file/table

    Quote Originally Posted by ermagarden View Post
    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:
    Code:
    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?
    "Time passes, but sometimes it beats the <crap> out of you as it goes."

  9. #8
    Analyst
    Join Date
    Jul 2006
    Location
    USA
    Posts
    173
    Rep Power
    772

    Re: Import from excel to AS400 file/table

    thanks for your help and the code.

  10. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

Facebook Comments


Similar Threads

  1. Add-in for Excel to pull data from AS400
    By jamief in forum Tips for the Iseries/AS400
    Replies: 2
    Last Post: February 3rd, 2012, 09:59 AM
  2. Replies: 12
    Last Post: April 26th, 2008, 01:53 AM
  3. Question about JDBC?
    By Jackie in forum JAVA
    Replies: 21
    Last Post: June 29th, 2006, 12:47 PM
  4. Transfer Excel Sheet To As400 file
    By Kevin in forum PC programming as it relates to Iseries
    Replies: 3
    Last Post: November 16th, 2005, 01:19 PM
  5. Replies: 1
    Last Post: April 27th, 2005, 06:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •