CODEWITHSUNDEEP
sqlexcelibm-midrange
mikjall77
mikjall77

Reputation: 35

Excel query using Transfer Data from System i

I am looking for information on how I can use the Transfer Data from System i Add-In for Excel to only get data that equals the data in one of my columns (both sets of data are strings). Unfortunately, I cannot just get the entire contents of the System i table since it contains more than the maximum allowed in Excel. Thank you!

Additional information for clarification purposes:

I'm trying to get specific data from the iSeries table. If the field in the iSeries = column A, I want that data placed in column c.

COLUMN A   COLUMN B     COLUMN C 
  100                     xxxxx  

on the iSeries table 

FIELD 1 = 40   FIELD 2 = ITEMDESC1  
FIELD 1 = 100  FIELD 2 = ITEMDESC2 
FIELD 1 = 500  FIELD 2 = ITEMDESC3

In this case it would place ITEMDESC2 into column C since FIELD 1 = 100 I just need to know if there is a way within the WHERE clause using the Data Transfer for System i to do this?

I hope this is clearer

Upvotes: 1

Views: 740

Answers (3)

WarrenT
WarrenT

Reputation: 4532

Alternate Approach:

You could write whatever query you like in an iNavigator RunSqlStm window. Set the option to save results, before you run the query. Once you run the query, you can save results in several formats, including Excel. If there are too many rows for your version of Excel, then you can save it as a .CSV file.

Upvotes: 1

Buck Calabro
Buck Calabro

Reputation: 7648

Data Transfer is a simple file transfer. It isn't intended to make decisions on how to select DB2 data based on the contents of various cells in an existing spreadsheet. As a workaround, you can upload the existing spreadsheet to DB2 and then use IBM i Navigator's SQL Script function, join the uploaded Excel table to your DB2 table and use CASE to put the proper field into the proper column.

select columna, columnb, case when field1 = columna then field2 else ' ' end
from excelupload join db2table on some_join_criteria
where some_record_selection_criteria

It seems easier to transfer the file to the PC and do the column manipulation in Excel.

Upvotes: 1

Buck Calabro
Buck Calabro

Reputation: 7648

  1. Create a new request
  2. Fill in system name (Next)
  3. Fill in table name (Next)
  4. Click "Data Options..."

On this panel is a SELECT and a WHERE clause. Put the cursor in the Where box, click "Details..." and create your query. Click "Apply" and then "OK".

Upvotes: 0

Related Questions