Automatically Transfering from Excel to iSeries

RodVerity

20 pts.

Tags:

Excel to iSeries

Microsoft Excel

I see there is a Transfer Data to iSeries option but is there a way of making this run automatically daily? Can something be schedulled from iSeries to initiate that? I want to reduce user intervention so would prefer not forcing the user to daily execute the upload. Any suggestions?

Thanks, Rod

Software/Hardware used:
Windows, Excel 2002, iSeries running v5r4

Asked: November 25, 2009 5:13 PM Last updated: March 24, 2010 6:15 PM

Answer Wiki

Last updated: November 25, 2009 8:50 PM GMT

ToddN2000100,265 pts.
History
Contributors
- Ordered by most recent
- ToddN2000100,265 pts.

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

We have some jobs that do our data transfers like you mention that we have running from the windows task manager. That is probably the easiest way to do just what you are looking for.

Discuss This Question: 3 Replies

Craig Hatmaker Nov 25, 2009 10:48 PM GMT

Hi Rod, I just wanted to give you a heads up that my blog will be covering how to update data on any database, including the iSeries directly from Excel (This will make some ITIL auditors cringe). We "post" employee time sheets directly to our iSeries from Excel using just the VBA that's included free with Excel, and the "iSeries Access ODBC Driver" that came with your operating system. My users love it. Excel and Pushing Data to the AS/400 As you already know, users prefer entering data in Excel where they can model it, sort it, test it, and finally - upload it into a host application. The big problem is layering the data edits over top of the users' entries to ensure what goes in the database is clean. Traditionally, we push a flat file version of the table to the iSeries and run a batch program to check the data, and if it's clean, add it to the database. The problem with this approach is that when the data isn't clean, communicating back to the end user what went wrong becomes an IT disruption at a time when scheduling pressures might already be in play (such as making sure those time sheets are in, in time for payroll). In my opinion, the better approach is to put the edits in Excel to insure data is clean at the source - just as any mainframe application would. This gives the user immediate feedback that they like and can also be engineered to help them, through defaults, drop down list boxes, formulas, copy and paste, coded edits, etc. to make clean entries. If this is of interest to you, please check out my Blog in a few weeks (or if you're ready to start reporting on iSeries data directly within Excel - check it out today). In any case, if anyone has opinions on this potentially controversial technique, I'd appreciate comments posted to: Beyond Excel: VBA and Database Manipulation

1,860 pointsBadges:

report
AS400lubs Mar 24, 2010 6:47 AM GMT

hi, can anyone provide a example of RPG programme which actually reach excel and put it in a flat file.

10 pointsBadges:

report
Teandy Mar 24, 2010 6:15 PM GMT

Try these two. http://www.scottklement.com/presentations/Excel%20Spreadsheets%20from%20RPG%20with%20POI.pdf http://www.easy400.net/xlparse/html/page1.htm

5,860 pointsBadges:

report