Have an Excel macro download data to the iSeries

180 pts.
Tags:
Excel macros
IFS
iSeries application development tools
iSeries job
Microsoft Excel
Is it possible to code an Excel macro to download a spread sheet to the IFS (on the iSeries) and then to start an iSeries job to process the downloaded data?

Answer Wiki

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

I cannot answer your question directly, but here is a link to a training program that promises to teach you how to do things like this.

http://www.lab400.com/product_detail.asp?prod_id=201

I hope this helps…

Regards,

Steve B

Discuss This Question: 9  Replies

 
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
  • GAC
    Aderene, What I understand in your question is that you have an MS-Excel file and you want to upload and process the information in your i5. You can follow several paths: 1. If the data structure is the same as a physical file in the i5, just define a data transfer description file and use the add-on provided in Personal Communications to upload the data to the PF. 2. If your .xls has just one sheet then save its contents to an IFS folder in a standard (non-Excel) format like CSV or DOS text and then use command CPYFRMIMPF to import it to a database file and process it. Several saves must be done if you have several sheets in your .xls. I recommend you this method if you have more experience programming on the i5. 3. Use a VBA macro from the spreadsheet itself to browse the file and save the information to the i5. Use this if you have experience developing macros in Visual Basic for Applications and the information in the excel needs previous process. Same result but it is more "attractive" to a final user to fill the excel and press a button (more complicated to us, developers, as we have to think about the process and security). 4. There is a Java API (poi.apache.org). You can use it at the client computer or at the i5 server. Again, easier to a final user but more complicated to a programmer/developer. Good luck, Gerardo
    300 pointsBadges:
    report
  • TomLiotta
    And be aware that the iSeries might be configured to reject any such actions. Accepting data and commands from a spreadsheet is risky and should not be allowed. The data transfer should happen through a compiled application. Communications should be through controlled application interfaces such as stored procedures in the database. In short, yes, what you ask about can be done. It also should not be allowed in a business environment. Tom
    125,585 pointsBadges:
    report
  • Craig Hatmaker
    Tom, you are the most knowledgeable person on this board about the iSeries. ITIL Auditors agree with your position. I don't. Think about this for a second: "The data transfer should happen through a compiled application". What exactly do you mean by that? Only RPG, C, COBOL? What about web posts that depend on validation by interpreted Java Script? What about interpreted EDI or XML streams? What about client/server apps that can use all kinds of 4G or interpreted languages? Is it 'compilation' that's important or is it something enforcing business rules that's required? I hope you really mean the latter and not the former. I submit that validation can be enforced by VBA and that VBA code can be locked down to prevent tampering. It can enforce as rigid as any rules in the best compiled application. I submit that Excel can be as robust as any Client/Server application and use exactly the same interfaces. I submit that users MUCH prefer the UI of Excel over any DSPF or HTML presentation for many applications. I also submit to Gerado that for some[/B ] (certainly not all) applications, Excel is much easier to develop rigid updates than just about any development platform out there. Consider rate tables for instance. Rate tables are often huge lists of values calculated from other values within the table. Try coding that in anything other than Excel - especially when you don't know what the basis for the calculation will be because it changes with whatever the sales and marketing divisions can dream up. And it changes frequently. Excel was made for that. With Excel I can afford the user the ability to calculate rate tables as they see fit then interrogate those values for thresholds dictated by business rules, and post validated results directly to the database for use by OLTP with ALL of the security and integrity of the most inflexible, tedious, fussy compiled language. We do it every day. I have implemented this approach for several decades without a single mishap at several companies. And I will also mention that our company was just recognized (by a huge corporation with brand names everyone knows) as being technologically superior in our industry yet we have one of the smallest IT departments among our peers. We must be doing something right. To; Aderene, If you want to learn how we do this, see: http://itknowledgeexchange.techtarget.com/beyond-excel/forward/
    1,705 pointsBadges:
    report
  • TomLiotta
    What about web posts that depend on validation by interpreted Java Script? The input should pass through a stored procedure that controls what can and cannot be allowed or through some server-side control similar in objective to a stored proc. You shouldn't have web processes that submit UPDATE statements that aren't validated by the server. Anybody could use basic sockets to send any UPDATE they wanted after requesting the web page. How would you know that it was the expected UPDATE? What about interpreted EDI or XML streams? How are those any different from any other external file? Surely you don't advocate applying data without validation? External data is applied by programming that enforces business rules. What about client/server apps that can use all kinds of 4G or interpreted languages? What about them? As long as a qualified developer puts them together according to the standards of the business, they should fit fine. But surely you wouldn't allow an app that I wrote on this PC to apply updates to your business system? You'd want to verify what I was doing. You'd want to know what was going to happen when the app runs. Is it ‘compilation’ that’s important or is it something enforcing business rules that’s required? The "compiled" element is kind of a generic term that should stand for something like 'Control'. It can be compiled, interpreted, rule-file driven, or perhaps many other technologies. But it should be created (developed, tested, approved, etc.) by those who know how things should work. It might be a table of rules that is neither compiled nor interpreted; but the authority to change that file should be controlled. Obviously, some files are more critical than others. Some files exist for the sole purpose of allowing users to enter data, but there should be programmed validation at some point. It might be compiled or it might be through strong rules defined in the database itself, various constraints, for example. The implementation is less important than the control. There are limits to exactly how far I should go because it can start spilling over to a sales pitch. But every problem of this type has at least one controlled solution. Direct updates of business databases through Excel uploads don't come with many controls. But an Excel upload to an intermediate file that is applied by the app (with validation) should be fine. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    I submit that validation can be enforced by VBA and that VBA code can be locked down to prevent tampering. Just out of curiosity, how does the server know that the transaction came from the intended VBA app? What stops any other uncontrolled VBA programming from sending a transaction that violates business rules? I can give answers, but others need to think about the implications. There are always points where users must be trusted to do their assigned jobs. Unfortunately, it can't be guaranteed. Trusts are violated every day somewhere. The more trust is given, the greater need for monitoring -- journals, audits, etc. I prefer systems where users are allowed to do as much for themselves as possible -- even Excel uploads. To make that feasible, I put a lot of effort into object authority assignments, database rules, backup/recovery and the rest. I tend to believe in the principle that the users own the data. I work at setting systems up so that that works in practice. It always comes back to forms of control on the server side. Tom
    125,585 pointsBadges:
    report
  • Craig Hatmaker
    What about client/server apps that can use all kinds of 4G or interpreted languages? What about them? As long as a qualified developer puts them together according to the standards of the business, they should fit fine.. We agree on that. And that's my point - that is also possible through Excel's VBA. Just out of curiosity, how does the server know that the transaction came from the intended VBA app? App specific user IDs, protected passwords and an appropriate exit program for QIBM_QZDA_SQL2 see:
    • http://www.itjungle.com/fhg/fhg112906-story02.html
    • http://www.as400pro.com/tipView.php?cat=iSSecurity&key=1393
    • http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzaii/rzaiimstexdb.htm
    Physical access and firewalls can further limit access to trusted machines. Gateways can also add layers of protection if systems are exposed to untrustworthy users. For the most part, all of our efforts come down to trusting users and planning for things to go wrong. As you know, good developers always strive to make our programs 'idiot' proof. Despite our efforts, we must anticipate that we may have overlooked something an 'idiot' might try (thus journaling and backup are necessary countermeasures). We must also anticipate malicious users can always find ways to corrupt things no matter what measures we put in place (again - journaling and backup are a necessary precaution). I prefer systems where users are allowed to do as much for themselves as possible — even Excel uploads. To make that feasible, I put a lot of effort into object authority assignments, database rules, backup/recovery and the rest. I tend to believe in the principle that the users own the data. I work at setting systems up so that that works in practice.Agree I'm just saying that should be no different in the Excel/VBA arena. Just because Excel is involved doesn't have to mean users are running amuck in the system.
    1,705 pointsBadges:
    report
  • TomLiotta
    Overall, it doesn't seem like we have much disagreement in practice. Just out of curiosity, how does the server know that the transaction came from the intended VBA app? App specific user IDs, protected passwords and an appropriate exit program for QIBM_QZDA_SQL2 see: The profiles and passwords are almost a given; the exit programming would be useful for elements other than the question that was asked. None of those should have any idea what originated the transaction on the remote PC. It could be the "trusted" VBA app or anything else. The exit program isn't passed anything that I'm aware of that identifies the process on the PC. The user could have simply typed an UPDATE statement in iNav Run SQL Scripts and performed any update. To add to the knowledge at this site, perhaps you could cite the IBM documentation to help the OP and others set up an exit program that can recognize the remote app. That would go a long way towards satisfying all kinds of requirements for everyone. I'd do it, but I haven't seen anything that helps. More specific to the question, though, the OP should be told how the QIBM_QZDA_SQL2 exit point relates at all to saving to the IFS and submitting a remote command, particularly without involving a stored proc. I assume that the OP is a developer. Knowing how to do it safely is important. I'm somewhat restricted in how I can discuss it due to employer conditions. Tom
    125,585 pointsBadges:
    report
  • Craig Hatmaker
    Tom:"Just out of curiosity, how does the server know that the transaction came from the intended VBA app?" Me:App specific user IDs, protected passwords and an appropriate exit program for QIBM_QZDA_SQL2 Tom: "The profiles and passwords are almost a given; the exit programming would be useful for elements other than the question that was asked. None of those should have any idea what originated the transaction on the remote PC. It could be the “trusted” VBA app or anything else." I bolded the applicable phrase "App Specific". The User ID for the app is part of the connection string to DB2. The exit program reads that App Specific User ID and allows or rejects the action. The User ID and Password, as should always be the case, is in password protected code shielded from users' eyes. So if the developer ONLY uses the App Specific User ID for approved apps, " the server (would) know that the transaction came from the intended VBA app" and not "anything else." I believe that is a direct answer to the question you asked. If it's not, please rephrase your question so I can see where I'm not answering it. Tom:To add to the knowledge at this site, perhaps you could cite the IBM documentation to help the OP and others set up an exit program that can recognize the remote app. That would go a long way towards satisfying all kinds of requirements for everyone. I’d do it, but I haven’t seen anything that helps I posted three links in my prior post:
    • The first introduces a similar concept and provides an example.
    • The second provides a second example
    • The third provides the IBM Redbook documentation for QIBM_QZDA_SQL2's exit point
    And not to be self serving, but I also posted a link to my blog in itknowledgeexhcange that is intended to provide all of this in a cookbook approach. What else is needed? Tom: More specific to the question, though, the OP should be told how the QIBM_QZDA_SQL2 exit point relates at all to saving to the IFS and submitting a remote command, I'm proposing the OP use DIRECT posting to the database instead. The "direct" part is what my contributions are about. I believe it is far more efficient for everyone. I also believe it can be just as secure.
    1,705 pointsBadges:
    report
  • aderene
    [...] Have an Excel macro download data to the iSeries [...]
    0 pointsBadges:
    report

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