We help IT Professionals succeed at work.

Ruby and iSeries

Last Modified: 2012-08-14
We currently run an IBM AS400 / iSeries. We use the cwbtfxla.xll excel plugin ("Transfer Data from iSeries") I was wondering is there any way to automate this using ruby and win32ole? Can anyone point me towards any resources that use ruby with the iSeries access for windows software??
Watch Question

>  We use the cwbtfxla.xll excel plugin ("Transfer Data from iSeries")...

You use it to do what? Normally, I'd say that some person uses it to populate columns in rows of a spreadsheet. That usually means that a spreadsheet exists, and all rows/columns of the spreadsheet are defined, including any formulas or macros. Then, the person activates the plugin to load data which get manipulated into the defined column formats.

So, is there a spreadsheet 'template' that you want to fill in? Or do you simply want data transferred in a spreadsheet compatible format? Is this supposed to be scheduled for periodic execution? Would an existing spreadsheet get written over or should a new one be generated?

Gary Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI

Ruby is great, but I'm not sure it is my first-choice tool for this particular task (I'm not really clear what the task is, though, so this may be moot).

Why not just automate it with a VBA macro?  VBA is built right into Excel, and for simple operations you can just record a quick macro (and touch it up in the VBA editor if needed).

Anyway, Tom is right - if you'll describe what you're tying to do in more detail, we can probably offer better guidance.

- Gary Patterson


I am trying to automate the gathering of fax logs. Currently we have to open excel click Data => Transfer data from iSeries. We have to walk through a wizard where the important choices are the library "qusrsys/qafftlog", Convert CCSID 65535, and under the data options we enter the relevant criteria for what numbers or dates we are looking for. This creates an excel sheet of data.

Once the data is in excel it requires further processing. There are a few blank columns that need to be deleted. The column headers need to be changed to more meaningful names. The time and date stamps need to be converted to an excel recognizable format and the error code needs to be changed to a more meaningful message.

I have had the following idea

  • create a gui for ruby that asks for the phone number or dates.
  • the ruby app would then modify 2 lines in a dtf file (that I was able to create using the "Transfer data from the iSeries" in the start menu)
  • the ruby app executes the dtf file. the dtf file creates a .xls file.
  • the ruby app then alters the existing excel data to create the desired output.
The end result would be an excel sheet that contains relevant fax log info subtable for sharing with 3rd parties. If there is a better way I am open to suggestions. I would strongly prefer to use ruby for this task.
I would ado here is a quick guide

1. Start excel
2. Press F11 - enter VBA
3. menu Tools/references
   a. select any Microsoft activex Data Objects 2.x library
4. Menu Insert/module
5. select the module and inser these definitions

Global con As New ADODB.Connection
Global rs As New ADODB.Recordset
Global cmd As New ADODB.Command
Global prm1 As ADODB.Parameter
Global prm2 As ADODB.Parameter
Global No400 As Boolean
Global Userid As String
Global Const AS400 = "999.999.999.999"

Public Sub AS400()

On Error GoTo OhEck
Set con = Nothing
Set cmd = Nothing

con.Open "PROVIDER=IBMDA400;Data Source=" & AS400 & ";USER ID= ;PASSWORD= ;"
Set cmd.ActiveConnection = con
Set rs.ActiveConnection = con

'get user id
Dim i As Integer
i = InStr(UCase(con.ConnectionString), "USER ID")
i = InStr(i, con.ConnectionString, "=")
Userid = Mid(con.ConnectionString, i + 1, 10)
i = InStr(Userid, ";")
If i <> 0 Then
    Mid(Userid, i) = Space(11 - i)
End If

No400 = False

Exit Sub


MsgBox "Cann not connect to the AS/400 - Downloads are not possible", vbInformation, cHeading
No400 = True

End Sub

to access you can then use

Sql = "Select cast(f00001 as char(200) ccsid 285) from mylib.myfilei"
rs.Open Sql
sheet1.Range("A1").CopyFromRecordset rs


then use a standard loop or

Gary Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI

Disclaimer:  I'm not a Ruby on Windows expert, but I'll try to help.  

Maybe one of the Ruby experts can offer a better approach since the real underlying task here is to just access a database file on the AS/400, extract some data, and format it into an Excel spreadsheet.  Rather than using File Transfer function and the Client Access Excel plug-in, I'd personally prefer to see you use a more "standard" type of approach for the task:  using some sort of data provider to open up a connection to the AS/400, build SQL to get your data and format it, and using a common library or utility to generate your output document.

You can use the "spreadsheet-excel" gem to directly create Excel documents from Ruby.  You don't even need to have Excel installed on the machine to do it.  I've written code (.NET) that uses Excel automation, and it is clunky at best.  This approach looks much better to me:


There are a number of ways that you can interoperate with the AS/400 database from Ruby, for example, you can use with ADO via OLE, which means that you can basically follow daveslash's VBA example above:


Now, since we have methods of accessing the AS/400 database, and a way to create an Excel spreadsheet directly, you can just create a Ruby program that:
  • Provides your GUI prompt;
  • Dynamically builds an SQL statement that formats your data any way you like it, selecting columns, selecting records, casting data into the correct format, building result firnds, and defining result fields names that suit you.  If you use ADO, for example, you can tell the ADO provider to automatically convert CCSID 65535 columns for you;
  • Executes the query, creating a result set;
  • Writes the results to a new Excel spreadsheet (or HTML page, or whatever) formatting the new document any way you like.
Regardless of the approach you choose, I'd be interested in seeing your final product, if you are willing to post it back here.  I'm sure others would find it useful, too.

- Gary Patterson

Check out my EE profile at: https://www.experts-exchange.com/M_4382324.html

P.S.  You can call functions in Windows DLLs from Ruby for Windows using the DL library.  Here's an example:


You can interoperate with Excel via OLE in Ruby:


Gary Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI

I forgot to mention that IBM ships a variety of database providers with the Windows version of Client Access (iSeries Access, System i Access, etc.), including ODBC, two OLE DB providers (IBMDA400 and IBMDASQL), and an ADO.NET provider.

- Gary Patterson
Application Consultant
This one is on us!
(Get your first solution completely free - no credit card required)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.