Convert AS/400 file to Excel and PDF

AS/400 import/export
Physical File
I would like to transfer a PF data into Excel but my add-ins not working. Can you provide me any other ways to do that?

Software/Hardware used:

Answer Wiki

Thanks. We'll let you know when a new response is added.

Use the CPYFRMIMPF command to create a CSV file on the IFS. The open it with excel. You can also look into using the Apache POI APIs to create the sheet in excel directly from your program.

Check the archives for examples of how to do both of these. 

You can also use ODBC to download directly from your AS/400 to a spreadsheet… here is a step by step tutorial for downloading AS/400 to Excel.

If you have Client Access installed on your system then you should have the ODBC driver to do this.

There are also lots of third party software packages that allow you to query into spreadsheets.

For a quick one time job use the IBM i Access Emulator option to download straight into an active Excel worksheet.

Discuss This Question: 8  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Yorkshireman
    You say ' my Add ins not working' If you have the tools already there, why not fix whatever is stopping it from working?
    6,085 pointsBadges:
  • phytlisa
    I use AS/400 during my work with an apparel company. Our programers say that it is impossible for AS/400 to 'export' color managed PDFs. They also tell me that it is impossible to change the 'export' quality of the PDF. Currently, my PDFs are 50dpi and not color managed so that all of the work done to obtain color precision is lost.
    Is what they tell me true? Is there an 'add on' that could help to get me at the very least a 300dpi, sRGB file?
    Thank you, Lisa
    10 pointsBadges:
  • TheRealRaven
    @phytlisa I've only used the basic, built-in .PDF support, and I haven't needed high quality in output; so I never paid attention before. The 'free' base output is pretty basic now that I've done some looking at it.

    If you want high-res full-color control, it apparently comes with the InfoPrint Server product. If your system has it installed, you should look to IBM Support. You should have no problem with 300dpi for example.
    37,065 pointsBadges:
  • Ashrafmorsy
    My company works with AS/400 environment & I need to convert the tables I have to an Excel file. How can I do that?
    10 pointsBadges:
  • jpanzenhagen
    For a one-off option, I use the download option of the IBM i Access Emulator. It will download any file into an open Excel workbook.
    95 pointsBadges:
  • ToddN2000
    Another option is write a web app to do the extract of the data from the  DB2 database. I wrote one to access any of our 4 I-series machines and using a textbox to enter a SQL statement returns a data grid you can copy  into Excel. I was write years ago when I was just starting my .NET career.

    Here's the vb code  
    Imports System
    Imports System.Data
    Imports System.IO
    Imports IBM.Data.DB2.iSeries
    Partial Public Class _Default
        Inherits System.Web.UI.Page
        Public mySQL As String = " "
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not Page.IsPostBack Then
                lblError.Visible = False
            End If
        End Sub
        Private Sub btnRunSQL_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRunSQL.Click
            Dim mySQL As String = Request.Form("tbxSQL")
            Dim strConnection As String = ""
            If cbDATA10.Checked Then
                'DATA10 box
                strConnection = "DataSource=;userid=live;password=live1;DefaultCollection=DTA010;Pooling=TRUE;Connect Timeout=30;"
            End If
            If cbDATA20.Checked Then
                'DATA20 box
                strConnection = "DataSource=;userid=live;password=live1;DefaultCollection=DTA020;Pooling=False;Connect Timeout=30;"
            End If
            If cbDATA40.Checked Then
                'DATA40 box
                strConnection = "DataSource=;userid=live;password=live1;DefaultCollection=DTA040;Pooling=False;Connect Timeout=30;"
            End If
            If cbDATA50.Checked Then
                'DATA50 box
                strConnection = "DataSource=;userid=live;password=live1;DefaultCollection=DTA050;Pooling=False;Connect Timeout=30;"
            End If
            Dim myConnection As New iDB2Connection(strConnection)
            Dim myCommand As New iDB2Command(mySQL, myConnection)
            '' using data reader it works read only 
            myCommand.CommandTimeout = 0
            Dim myReader As iDB2DataReader
                myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
                If myReader.HasRows = False Then
                    lblError.Text = "No rows returned for SQL statement"
                    lblError.Visible = True
                    dgResults.DataSource = Nothing
                    Dim fields As Integer = myReader.FieldCount
                    dgResults.DataSource = myReader
                    lblError.Text = " "
                    lblError.Visible = False
                End If
            Catch ex As Exception
                lblError.Text = ex.Message
                lblError.Visible = True
            End Try
        End Sub
        Protected Sub dgResults_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
            For i As Integer = 0 To e.Row.Cells.Count - 1
                e.Row.Cells(i).Attributes.Add("style", "white-space: nowrap;")
        End Sub
    End Class

    136,580 pointsBadges:
  • ej
    We ran across a weird issue with the Excel add-in recently. I don't recall the specifics, but if you are using the Excel add-in to download your file, it must be the first instance of Excel. You may need to close out other open Excel files before trying to download using the add-in. Not sure as to why.
    70 pointsBadges:
  • ToddN2000
    @ej: Seemed like a strange issue; so I tried it. You're right the second instance will freeze up with "Not Responding". Waited a bit and nothing. Closed the first instance and the second transfer that was "Not Responding" finished fine.. Thanks for the heads up on the issue.
    136,580 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: