Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Reader Poll: Do You Download to Excel?

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

  • Reader Poll: Do You Download to Excel?

    We use ODBC connection to copy AS400 files to Excel. Another way is copy file to IFS, copy IFS to DLS, and email sample.CSV CPYTOIMPF FROMFILE(Library/file member) + TOSTMF(sample.CSV) MBROPT(*REPLACE) + STMFCODPAG(*PCASCII) RCDDLM(*CRLF) CPY OBJ('/HOME/sample.CSV') + TODIR('/QDLS/Test/sample') REPLACE (*YES) SNDDST TYPE(*DOC) TOINTNET((*NONE) + (user@youremail.com *PRI) (*NONE + *PRI)) DSTD('Sample') + MSG(&MSG) LONGMSG(&MSG) DOC(SAMPLE.CSV) + FLR(SAMPLE)

  • #2
    Reader Poll: Do You Download to Excel?

    Excel is really a programming language. Unfortunately, it's a programming language being programmed by non-programmers. They don't create requirements, they don't test and they don't debug. They just take the results at face value. I believe it was Computerworld that published a study a few years back that said over 80% of all spreadsheets have errors in them. And, companies make directional decisions based upon these erroneous spreadsheets. It's a shame. Therefore, when we find users are downloading data for spreadsheets we work with them to develop online applications that present the results that they want to see so they can eliminate the use of spreadsheets. chuck Opinions expressed are not necessarily those of my employer. wrote in message news:6b36c968.0@WebX.WawyahGHajS... > We use ODBC connection to copy AS400 files to Excel. > > Another way is copy file to IFS, copy IFS to DLS, and email sample.CSV > > CPYTOIMPF FROMFILE(Library/file member) + > TOSTMF(sample.CSV) MBROPT(*REPLACE) + > STMFCODPAG(*PCASCII) RCDDLM(*CRLF) > CPY OBJ('/HOME/sample.CSV') + > TODIR('/QDLS/Test/sample') REPLACE (*YES) > SNDDST TYPE(*DOC) TOINTNET((*NONE) + > (user@youremail.com *PRI) (*NONE + > *PRI)) DSTD('Sample') + > MSG(&MSG) LONGMSG(&MSG) DOC(SAMPLE.CSV) + > FLR(SAMPLE)

    Comment


    • #3
      Reader Poll: Do You Download to Excel?

      Chuck Ackerman wrote: > I believe it was Computerworld that published a study a few years > back that said over 80% of all spreadsheets have errors in them. > And, companies make directional decisions based upon these erroneous > spreadsheets. It's a shame. > > Therefore, when we find users are downloading data for spreadsheets > we work with them to develop online applications that present the > results that they want to see so they can eliminate the use of > spreadsheets. Wow, we're just the opposite; I even give classes in downloading to Excel. User productivity is extremely high and the users also have the benefit of the freedom to re-arrange the data to whatever presentation they need for the requirement. If they had to wait for the IT department to modify reports we'd have to double our personnel to get anything done. As I've said here a couple of times before, all it takes is a demonstration of a direct download to a Pivot Table and the users are sold on the concept. That said, we also have a rule that the data in the ERP system is the final authority and thus -strongly- discourage anyone building their own little Access databases to transact into and report from. We also have a rule that no one is to spend more than 15 to 20 minutes trying to develop their own download, anything more and they should give the request to us. Bill

      Comment


      • #4
        Reader Poll: Do You Download to Excel?

        I have to agree with Chuck to a point. I would much rather have a query tool like SEQUEL or mrc-productivity being used on the host and provide the data to the end-user than allow them to play with it in Excel. But I also know that our job is to provide people (our end-users) with what they think they need, what they want, and do so with a level of control that, well has a limit. Meaning at the end of the day, if they still want database file XYZ in Excel, they're going to get it. They're the user/customer.

        Comment


        • #5
          Reader Poll: Do You Download to Excel?

          Bill, Why not give them access to RPG and PDM so they can do really powerful things? I can tell you why, because you really can't trust end users with the REAL data. Deep down you believe they'll screw it up. And you're right. You'll give them data to play with in their own sandbox but you don't trust them to use the big sandbox. With that in mind, why would one make big decisions based upon what comes out of a spreadsheet from a non-programmer knowing that they have an 80% chance of having faulty information? Is this what happened to Enron? Forgive me if I'm putting words in your mouth. Maybe you do let end users create their own RPG programs and manipulate the real data. chuck Opinions expressed are not necessarily those of my employer. "Bill" wrote in message news:F6DAD769319FCF27F223D99B096146D0@in.WebX.Wawy ahGHajS... > Chuck Ackerman wrote: >> I believe it was Computerworld that published a study a few years >> back that said over 80% of all spreadsheets have errors in them. >> And, companies make directional decisions based upon these erroneous >> spreadsheets. It's a shame. >> >> Therefore, when we find users are downloading data for spreadsheets >> we work with them to develop online applications that present the >> results that they want to see so they can eliminate the use of >> spreadsheets. > > Wow, we're just the opposite; I even give classes in downloading to Excel. > User productivity is extremely high and the users also have the benefit of > the freedom to re-arrange the data to whatever presentation they need for > the requirement. If they had to wait for the IT department to modify > reports we'd have to double our personnel to get anything done. > > As I've said here a couple of times before, all it takes is a > demonstration > of a direct download to a Pivot Table and the users are sold on the > concept. > > That said, we also have a rule that the data in the ERP system is the > final > authority and thus -strongly- discourage anyone building their own little > Access databases to transact into and report from. We also have a rule > that no one is to spend more than 15 to 20 minutes trying to develop their > own download, anything more and they should give the request to us. > > Bill > >

          Comment


          • #6
            Reader Poll: Do You Download to Excel?

            Bob, We agree to a point. My judiciary duty is to be responsible for the data. Yes, we have some "super users" that download data but every little thing they do is verified against information from the host system. Decisions made upon the data that I let leak out of the AS/400 are ultimately my responsibility. I.T. executives that let the data become available to anyone who wants to manipulate it and then wipe their hands of the decisions made from that "leaked" data are short sighted and not doing their job. chuck Opinions expressed are not necessarily those of my employer. "Bob Cozzi" wrote in message news:6b36c968.3@WebX.WawyahGHajS... >I have to agree with Chuck to a point. I would much rather have a query >tool like SEQUEL or mrc-productivity being used on the host and provide the >data to the end-user than allow them to play with it in Excel. > > But I also know that our job is to provide people (our end-users) with > what they think they need, what they want, and do so with a level of > control that, well has a limit. Meaning at the end of the day, if they > still want database file XYZ in Excel, they're going to get it. They're > the user/customer.

            Comment


            • #7
              Reader Poll: Do You Download to Excel?

              It is not the use, but rather the misuse of Excel that has been an issue for years. As I pointed out a bit back ( David Abramowitz "TechTip: MS Excel--They Gotta Have It!" 2/25/03 11:35am ) End users will use Excel for virtually everything. Not only when inappropriate, but particularly, and pervasively when inappropriate. When I have pointed out other tools that were more germaine for the task (even Word, Access, etc.) I receive stares that make me doubt my own knowledge in the number of heads I may or may not have. This is usually followed by chuckles. There are users who feel that I.T. is quite foolish for even suggesting other methods of achieving needed goals or objectives. Panic usually sets in when more than 65535 records need to be downloaded to an Office 2000 user. Empirically I believe that 85% of all downloads to Excel would better serve the needs of the users if the data were downloaded to Access instead. In most of these cases a link would be even better. There are those users who don't understand that data gets updated, and they are most perplexed by the fact that their three-week old spreadsheet was not updated automatically with the rest of the system. More to follow.......... Dave

              Comment


              • #8
                Reader Poll: Do You Download to Excel?

                David Abramowitz wrote: > Empirically I believe that 85% of all downloads to Excel would better > serve the needs of the users if the data were downloaded to Access > instead. I disagree with the percentage, (I'd go with around 20%) but not the intent of your post David. The problem is that most people just don't know Access well enough. I'm not bothered by this though, because it's the Access users that begin to create their own little databases and that's where the problems begin. Bill

                Comment


                • #9
                  Reader Poll: Do You Download to Excel?

                  On the other side of the coin, there are those who will do extensive downloads only to garner three or four lines of information. Sometimes just copying by hand is faster, more efficient, and may also be formatted better. I once gave a class on how to use ODBC to download directly from the AS/400 into an MS Word Mail merge document. All listened attentively until the end. At this point I realized that there was a great deal of misunderstanding. The entire class couldn't figure out why Excel was omitted. They wanted to download to Excel first for no other reason than performing the mail merge. They were so entrenched in the Excel status quo, that showing them a direct method that omitted one or more steps was only confusing them. Dave

                  Comment


                  • #10
                    Reader Poll: Do You Download to Excel?

                    A lot of people feel that anyone can extract information and load it into Excel. Supervisors (Management) ask people to get information and place it into Excel. Most of these people do not have the training in how to obtain the data and do not know logic. I have seen so many users that copy an existing spreadsheet and then link to several data bases. Often these links are not needed and cause a lot of over-head. Most of the spreadsheets do not reflect the true data. Business decisions are made using these spreadsheets. I get very concerned when I see this. I usually only see the ones with problems, and I can correct these. I worry about the ones I do not see.

                    Comment


                    • #11
                      Reader Poll: Do You Download to Excel?

                      I agree that spreadsheets are dangerous, but I have learned from experience that if the user wants the data in Excel, they will get it in Excel one way or the other. I had a group of users who spent 3 days per month getting a particular report into Excel, you guessed it, manually. They re-keyed the entire report into a spreadsheet every month from a printed report. Now, which is more dangerous? Executive decisions from data automatically downloaded from the iSeries tables, or data re-keyed into a spreadsheet from a report (typing errors and all)? Once I learned of this procedure, I immediately provided a download for them. Food for thought, Rich

                      Comment


                      • #12
                        Reader Poll: Do You Download to Excel?

                        Chuck Ackerman wrote: > Why not give them access to RPG and PDM so they can do really powerful > things? I can tell you why, because you really can't trust end users > with the REAL data. Deep down you believe they'll screw it up. And > you're right. No, three other reasons: 1) Which is more likely: a cost accountant that knows spreasheets, or one that knows RPG? 2) RPG is horrible for reporting when compared to a spreadsheet, database or a package such as Crystal Reports. 3) RPG offers the opportunity to create data in the master database, reporting tools do not. > You'll give them data to play with in their own sandbox but you don't > trust them to use the big sandbox. With that in mind, why would one > make big decisions based upon what comes out of a spreadsheet from a > non-programmer knowing that they have an 80% chance of having faulty > information? Is this what happened to Enron? If you'd remembered or read the source article in more depth, you'd realize how much your 80% argument does not apply to this situation and in many cases is a reason -for- downloading to Excel. One of the reasons listed for causing errors in spreadsheets? Data entry. Also, the article mentions that the 80% is just about in line with any human cognitive activity including programming. The jest of the article was to say that it's better to have programmers doing the reporting because they are more likely to catch the errors, but the errors are going to be present regardless of the medium and who has created it. So, unless you've outlawed spreadsheets in the orginization, the argument for errors on spreadsheets as a reason to disallow downloading just doesn't make sense. Bill

                        Comment


                        • #13
                          Reader Poll: Do You Download to Excel?

                          Rich asked: "Now, which is more dangerous? Executive decisions from data automatically downloaded from the iSeries tables, or data re-keyed into a spreadsheet from a report (typing errors and all)?" I think it's safe to say that it's very dangerous to have data keyed in. Did they not feel comfortable enough to come to the I.T. group and ask for the information? chuck Opinions expressed are not necessarily those of my employer.

                          Comment


                          • #14
                            Reader Poll: Do You Download to Excel?

                            This is a discussion about Reader Poll: Do You Download to Excel?.

                            Click here for the article.

                            Comment


                            • #15
                              Reader Poll: Do You Download to Excel?

                              There's a lot of food for thought in this thread but bottom line, I agree with Bob when he says "at the end of the day, if they still want database file XYZ in Excel, they're going to get it." I feel like Captain Obvious by saying this but Excel, like any other tool, can be good or bad depending on what you do with it. I have crafted 2 basic strategies for coping with user demands for data in Excel. In the process I have become appreciative of Excel's power and managed to retain some modicum of control over the data that goes into a spreadsheet. Strategy 1 uses Excel as a user interface to iSeries data. All data access is controlled by macros and stored procedures and the worksheets themselves are protected (except for the query parameters.) The user gets the data and formatting they expect from Excel and I know the results are accurate. Strategy 2 uses SQL2JXL (articles available on this site) and JAVAMAIL to create an Excel spreadsheet and email it to the user. I put control totals in the email so the user (and myself) knows what was downloaded. (Yes, the users could edit the email to CYA but I don't deem that likely. If it ever happens I'll start putting the control totals in a PDF and/or BCC a report mailbox.) The user has total control over the data but, at the end of the day, their spreadsheet has to balance to the control totals. So far this has worked well. Strategy 2 in particular saves everyone time. Users don't have to deal with report download tools or rekeying, and IT can create new reports with little more than an SQL statement.

                              Comment

                              Working...
                              X