import the data from iseries to excel sheet

5 pts.
Tags:
AS/400
iSeries
Microsoft Excel

i read some instruction of Thierry Schmitz using timestamp. i am very much interested on this.

please provide me any example of this.

i am posting the process also.

I prefer the following solution, working on request from the user :
1) the main excel sheet shows Timestamp of both Available DB from I5 and timestamp of implemented data from i5, int the following form :
Check new Update Available DB dated : 2008-02-06 13:44 Finished
Update Data Implemented DB dated : 2008-02-06 13:44 
“Check New update” and “Update data” are action buttons inside excel.
“Check New update” data use a MS Query inside excel to control that :
1) I5 process is not running (if yes, update is not allowed and a pop-up message notify user to wait a bit)
2) TimeStamp of available DB is more recent than implemented one. If yes, update Timstamp of available data inside excel and visualy shows that with color.
Update data run the following process(es).
1) Clear actual data inside Excel
2) Remove Autofilters
3) Import new data
4) Synchronize Available and imported Timstamp
5) optional treament… like Pivot table regenaration…
If you put the full name of the 2 required MS Query variable inside one sheet of your excel file, you may reuse this excel file to import and make some basic B.O. application very quick.
On i5 job (assuming the data to be used in excel is generated in a batch process), the requirements are :
1)When Batch job start, flag the application with ACTIVE STATUS using Timestamp control file (will lock excel import for a while)
2) Let your update run
3) When batch stops, update the TImestamp control file and release status.
If somebody has some interest using this technique, send me a private message and i”ll prepare a example of both I5 and Excel side.
Prerequesites are ODBC link available on the machine running the excel import.
ASKED: April 17, 2008  6:32 AM
UPDATED: April 18, 2008  7:18 AM

Answer Wiki

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

Mup1234,

I’ve never seen TimeStamp but I know my users have always used Winspool to import any information genereated by AS400 into Excel.
They usually will run a query and dump it to an outcue on the 400.
Then Winspoll will let you import from the particular library and cue.
Once Winspool has it you can import the file into an Excel spreadsheet.
This app does require ODBC be installed but it’s been very useful for my users for years.
It’s an older app but runs on XP Pro SP2 without issue.

——————————————————————————————————-
Hi,
Timestamps are nothing else but date & time in this kind of format:
2007-06-27-15.15.12.431000
I use that to trigger updates or DB2 master files, stored in one specific control file, to check inside the excel application if the excel DB needs refresh (Is any update available).

This is the way i control updates availability.

If you need one full example of the interfacing type, just provide me your email.

BY.

TSchmitz

Discuss This Question:  

 
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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following