ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Import from excel to AS400 file/table

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Comment


    • #3
      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..





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

      Comment


      • #4
        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

        Comment


        • #5
          Re: Import from excel to AS400 file/table

          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=[i]{your IP address}[/i];"
          
          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."

          Comment


          • #6
            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)

            Comment


            • #7
              Re: Import from excel to AS400 file/table

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

              Comment


              • #8
                Re: Import from excel to AS400 file/table

                thanks for your help and the code.

                Comment


                • #9
                  Hi,

                  After long time, I have to do something on VB script to perform import/export option with AS400 tables. I am using the VBA script provided here as example to extract the table data from iSeries to Excel.

                  However, the script fail due to 'Method or data member not error' in the below line.

                  it fails while executing the line - Sheets2.Offset(0, lngOffset).Value = objFields.Name

                  Please help!

                  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

                  Comment


                  • littlepd
                    littlepd commented
                    Editing a comment
                    Did you solve this? Sorry for the delay, but I haven't been here in a while.

                    In your spreadsheet, you have to add a reference to the Microsoft ActiveX Data Objects library (I usually use the 2.7 library) for any of the ADO stuff to work. In your example, objFields.Name is an ADO method, and I assume it's what is causing your problem.

                • #10
                  using the debugger what are the values of ingoffset and objfields.name
                  Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

                  Comment


                  • #11
                    You do not need to develop anything!
                    - Utility HSSFCGI features a command converting a database file to an Excel spreadsheet
                    - Utility XLPARSE2 features a command to convert an Excel spreadsheet to a database file
                    See page http://www.easy400.net/easy400p/doc.cgi

                    Comment

                    Working...
                    X