Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

TechTip: MS Excel--They Gotta Have It!

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • TechTip: MS Excel--They Gotta Have It!

    ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
    ** This thread discusses the Content article: TechTip: MS Excel--They Gotta Have It! **
    0

  • #2
    TechTip: MS Excel--They Gotta Have It!

    ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
    In V5R2 the EBCDIC to ASCII problem has been corrected. There is now a parameter which will allow this process to work in 1 step intead of the method used in prior versions...you can tell I had the same problem. The parameter is: Stream file code page . . . . . STMFCODPAG *pcascii

    Comment


    • #3
      TechTip: MS Excel--They Gotta Have It!

      ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
      This has actually been available back to at least V4R5 via a PTF. Scott Mildenberger

      Comment


      • #4
        TechTip: MS Excel--They Gotta Have It!

        ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
        MC WebMaster wrote: > This is a discussion about TechTip: MS Excel--They Gotta Have It!. Ick, this puts a lot of work on the user and reduces the reusability of a spreadsheet. I have found great success by creating MS Query's into the spreadsheet. This way the data will already be formatted for the user and, for many queries, they only have to refresh the data to get the current statistics. Parameterized queries add even more power to the user. If you were to show a group of users the right query and then show them the results of processing this data through a pivot table, you could experience your first standing ovation. Bill

        Comment


        • #5
          TechTip: MS Excel--They Gotta Have It!

          ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
          The article is bang on concerning Excel. We have implemented a similar method as described in the article and it is the preferred method for reports. It's come to the point where the users will demand an option to email the CSV to them when we create a new report. Most of our reports (i.e ATB's etc) are run during the evening, zipped and emailed to the recipient for the next morning. We also use this technique for outside clients, vendors etc. Most don't believe it comes from the old green screen AS400 (actually the zip and encryption comes from the PC, but I give credit to the AS400). For physical files, we run a few commands to insert the file fields into a work file, then copy the file to the work file. This way headings will also be sent. For spooled files, we format the output with commas (creating a CSV file in the PRTF), copy it and email the results. The spooled file method gives you complete control over the layout, but can be messy. Except for my PC, I've stayed away from MS Query, we would have a difficult time installing it on each PC (the add on is not standard), and I believe you must also configure ODBC (may be wrong there). But Bill is correct in that it is a great way to present data. Great article in reminding everyone of what is a very powerful business tool, I mean the AS400 of course, but so is Excel! Thanks

          Comment


          • #6
            TechTip: MS Excel--They Gotta Have It!

            ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
            As I stated in the article, there are many ways to accomplish the goal of creating the spreadsheet, but fewer ways of automating the process. There is nothing wrong with using MS Query, or Crystal Reports, or other means to obtain the data when the user is there, but delivering the spreadsheet on an automated basis, i.e. without any user interaction, is a different issue. Dave

            Comment


            • #7
              TechTip: MS Excel--They Gotta Have It!

              ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
              David Abramowitz wrote: > As I stated in the article, there are many ways to accomplish the > goal of creating the spreadsheet, but fewer ways of automating the > process. Agree somewhat. > There is nothing wrong with using MS Query, or Crystal Reports, or > other means to obtain the data when the user is there, but delivering > the spreadsheet on an automated basis, i.e. without any user > interaction, is a different issue. But sending a delimited file is not sending a spreadsheet, it's just sending a data file - there aren't any column headers, the print page isn't formatted, the data columns might be of the wrong format and no further data manipulation can occur without human intervention - unless you are coding these things in a Visual Basic for Applications macro. Spreadsheets can be automated to do just about anything through the use of VBA. You could even build a spreadsheet that would automatically download the data and then have it email itself without any intervention. Bill

              Comment


              • #8
                TechTip: MS Excel--They Gotta Have It!

                ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
                David, You mention the time required to set up MS Query on each desktop and what is necessary to get the ODBC driver setup as well. Compare this to the time required to create and email the CSV to each user that desires it. With Query set up and the ODBC set up, they could then refresh the data themselves without any involvement from you. If your users are "addicted" to the CSV format, I could just about guarantee they'll drop that addiction for the MS Query method. Try it out on just a couple at first and see what they think. Bill David Christie wrote: > The article is bang on concerning Excel. We have implemented a > similar method as described in the article and it is the preferred > method for reports. It's come to the point where the users will > demand an option to email the CSV to them when we create a new > report. > > Most of our reports (i.e ATB's etc) are run during the evening, > zipped and emailed to the recipient for the next morning. We also use > this technique for outside clients, vendors etc. Most don't believe > it comes from the old green screen AS400 (actually the zip and > encryption comes from the PC, but I give credit to the AS400). > > For physical files, we run a few commands to insert the file fields > into a work file, then copy the file to the work file. This way > headings will also be sent. > > For spooled files, we format the output with commas (creating a CSV > file in the PRTF), copy it and email the results. The spooled file > method gives you complete control over the layout, but can be messy. > > Except for my PC, I've stayed away from MS Query, we would have a > difficult time installing it on each PC (the add on is not standard), > and I believe you must also configure ODBC (may be wrong there). But > Bill is correct in that it is a great way to present data. > > Great article in reminding everyone of what is a very powerful > business tool, I mean the AS400 of course, but so is Excel! > > Thanks

                Comment


                • #9
                  TechTip: MS Excel--They Gotta Have It!

                  ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
                  The situation here, is that there are regular overnight production runs that produce a variety of updates, and reports. These are long convoluted job streams that massage data into temporary work files, and are often six processing steps or greater. The users desired to have the reports in spreadsheet format. ergo: The users are home in bed sleeping when the jobs are run, they can not be at their desks starting a VBA macro. The CSV files are produced and e-mailed without user intervention. To reiterate, these are production runs. The user is not in the picture until the e-mail is received. I am familiar with VBA, and use it for a variety of purposes. VBA is an excellent tool, but does not provide the kind of full automation I described in the article. You are correct that headings, and full formatting does not occur (certain fields do format). There are third party tools that will accomplish these goals from the host side, and certainly you could create a more sophisticated automatic download given enough time and budget. Once again this was not the purpose of the "tip". If your shop has no need of this sort of functionality, then you should take note of the technique just in case the need arises. Dave

                  Comment


                  • #10
                    TechTip: MS Excel--They Gotta Have It!

                    ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
                    Compare this to the time required to create and email the CSV to each user that desires it. FYI, this may be done in batch. For my purposes, it is done in batch. Dave

                    Comment


                    • #11
                      TechTip: MS Excel--They Gotta Have It!

                      ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
                      I copied a PF to a directory in the IFS file in '.csv' format and it works fine and I am able to open it in EXCEL, but I am not able to copy to a folder in 'QDLS/myfolder/myworksheet.csv'. How to copy a PF directly to a folder. I have a batch program that converts PF to Excel and I want it to send using SNDDST but it prompts for folder not directory. Any help will be greatly appreciated. Sky

                      Comment


                      • #12
                        TechTip: MS Excel--They Gotta Have It!

                        ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
                        QDLS only supports the 8.3 naming standard, shorten your name to fit in this and it should work. Scott Mildenberger

                        Comment


                        • #13
                          TechTip: MS Excel--They Gotta Have It!

                          ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
                          We use the CPYTOPCD or CPYTOIMPF commands. If you copy to QDLS, the file name must be a total of 12 characters. V5R1 example
                           Chgvar &DOCNAME ('PR' |< &JOBNBR |< '.XML') Chgvar &TOSTMF ('/QDLS/TEMP/' |< &DOCNAME) CPYTOIMPF FROMFILE(QTEMP/FILE) TOSTMF(&TOSTMF) + MBROPT(*ADD) STMFCODPAG(*PCASCII) + RCDDLM(*CRLF) STRDLM(*NONE) 
                          or
                           Chgvar &DOCNAME ('SF' |< &JOBNBR |< '.CSV') CPYTOPCD FROMFILE(QTEMP/SPLF) TOFLR(TEMP) + TODOC(&DOCNAME) REPLACE(*YES) 

                          Comment


                          • #14
                            TechTip: MS Excel--They Gotta Have It!

                            ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
                            Hello, the tools discussed here are very fine anyway. I'm sure they are useful to many people of this community. For those who want more we invite to have a look to our site http://www.goering.us Our tool "iExcelGen" generates real Excelfiles native at the lovely AS/400 (sorry: IBM eServer iSeries). CSV was not enough for our customers.... Kind regards Andreas Goering GOERING iSeries Solutions

                            Comment


                            • #15
                              TechTip: MS Excel--They Gotta Have It!

                              ** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
                              As I stated in my article, it is possible to accomplish the end goals in a variety of ways. The method I chose was a simplified way of automating the means. With a bit of work, one could put in Excel headers, and Column headers for any file. That was not the purpose of the article. Perhaps you would like to discern your methods in a form of a tech tip, so that all readers could share your technique. Dave

                              Comment

                              Working...
                              X