CODEWITHSUNDEEP
excelodbcibm-midrange
datasm
datasm

Reputation: 1

Getting data from AS400/IBM i through ODBC to excel

I've written a very simple SQL statement on system I navigator and it shows all I need, but this statement cannot work any platform except system i navigator. When I tried to connect as400/IBM I on Excel, it does successfully connect. But the schemas and tables contain nonsense information.

My SQL statement is:

SELECT *  FROM CAPPSERV.XRFFIL02.PRDMST01

I can't even find the XRFFIL02 in Excel.

I've found this "AS400 and System I Navigator" and tried to find my SQL name, but it shows my file name and SQL name are the same.

Maybe my direction was wrong.

Upvotes: 0

Views: 200

Answers (1)

stenag
stenag

Reputation: 114

In Navigator for i run sql scripts in the options menu you can select

  1. Allow save results

  2. Display results in seperate window

Then run the select

Then in the results of the select right-click and click on save results.

I just saved an example to my c: drive as a csv (there are other file types in the drop-down like excel spreadsheet) and could open it in excel no problem.

= = =

ASIDE: Be aware of log4j vulnerability with navigator See this link among many more https://www.itjungle.com/2022/10/26/new-nav-makes-progress-but-still-not-caught-up-with-old-nav/

= = =

The rest of the answer is only if you have IBm i ACS so ignore otherwise, thanks.

Do you have IBM i ACS ?

If so "run SQL scripts" can be used and the output of any SQL select can be put in aa EXCEL spreadsheet

Under File, Preferences, tick "Enable Saving of results". Under File, Results I also have "Open new results in seperate window" selected. ASIDE, what is very important is under File, Results, to have "Automatically close cursor..." set to the minimum

Upvotes: 0

Related Questions