Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Download to Excel drops leading zeros from alpha fields

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

  • Download to Excel drops leading zeros from alpha fields

    Lenore Emhoff wrote: > Is there any way to keep the leading zero when opened in excel? The > user needs the file in excel to be used elsewhere. If you download directly to Excel via the MSQuery add-in and utilize the iSeries Access ODBC driver, it will solve this problem. One option might be to code a macro for the importation of the data instead of letting Excel do it automatically. Bill

  • #2
    Download to Excel drops leading zeros from alpha fields

    Lenore, I know and feel your pain. I just love the way Excel seems to think it knows best. I get around this issue by building my own .csv file within the RPG program. I do most of my reports this way as it's very easy to do and there is way less code to write for us. Using this technique, I never have to design or code a report. I don't deal with level breaks or differeing sort requests. The users love it because they can slice and dice the report and format it anyway they like using readily available Excel skills. Each user can do their own thing, I deliver the data. It's a real easy way to modernize report delivery without anything fancy. RPG /FREE simplifies the coding of the huge concatenation. Notice the inserted "=" in front of the very first field in the output record. That tells Excel, that it doesn't know best and NOT to reformat the data based on it's best idea of what to do. In my case of this program, Excel was converting that very long item number (which is alpha in the database, but contains all numbers) into an exponential number. Gag. Another neat thing to note is that I use the %XLATE BIF to remove characters that may affect the way excel parses the data into columns. ( i.e double quotes) Once I get the flat file that I've built as a .csv, I copy it to the IFS like so.... CPYTOSTMF + FROMMBR('/qsys.lib/qtemp.lib/actcost.file/a+ ctcost.mbr') TOSTMF(&TOSTMF) + STMFOPT(*REPLACE) STMFCODPAG(*PCASCII) CHGAUT OBJ(&TOSTMF) USER(*PUBLIC) DTAAUT(*RWX) + OBJAUT(*ALL) MONMSG MSGID(CPF0000) I create a main IFS directory that I use for all user downloads. Under this directory is a sub-directory for each user. The .csv file lands in the user sub-directory and there is a mapped drive on the users' desktop to this location. As part of the CL which runs this, there is a command front-end which asks the user for their file name and other selection information. Done. Good Luck, Stan
    Code

    Comment


    • #3
      Download to Excel drops leading zeros from alpha fields

      The problem is Excel in this instance. Normally smart software is good, but in this case it happens to be bad. Excel is an example of smart software. Here's an alternative solution, albeit a bit lengthy. Change the file extension to ".TXT". It can still contain comma separated values so just the name changes. Then Excel will open up the file and place everything in the first column. Next you use the command "Data | Text to Columns" and allow the wizard to parse out the data based on the commas (you may have to explicitly tell Excel this, as sometimes it guesses wrong). Now you come to the crucial step. Excel will preview each column and mark it as "General". This allows Excel to individually evaluate each cell and decide if it's a Date, a Number, or a Character. Change the relevant columns to Text and click Finish. Now Excel will leave your numbers alone. You can also save it back as a .CSV file with Excel, but beware if you have to re-edit the file--it will all happen over again! The reason that Excel removed those leading zeros is that this logic ran transparently when you opened the .CSV file. It decided that those values were numbers and leading zeros on a number aren't significant.

      Comment


      • #4
        Download to Excel drops leading zeros from alpha fields

        Have your user try this cell formula in Excel: =RIGHT("000"&TEXT(A1,0),3) A1 B1 30 030

        Comment


        • #5
          Download to Excel drops leading zeros from alpha fields

          If the user can wait and clean it up in Excel, as an alternate to coding a formula, they can click on the column heading, choose Format, Cells, then Custom and enter 000 as the Type to make all rows have the full 3 digits

          Comment


          • #6
            Download to Excel drops leading zeros from alpha fields

            When you put the file in the folder using the CPYTOPCD command, try naming the document without the .CSV extension, or if you need to specify an extension, use .txt instead. Then, email the file without the extension .csv using your SNDDST. Excel will prompt the user for field definition, on which the user can specify "text" for the field with leading zeroes . -- ting

            Comment


            • #7
              Download to Excel drops leading zeros from alpha fields

              Hi, I am looking at your sample and I was wondering where do you write the records, is it on the Physical File. From your sample code, it seems that your writing on the Database. And how do you write it on the .CSV file. I am just confused with CPYTOSTMF command. Thanks, TIKI

              Comment


              • #8
                Download to Excel drops leading zeros from alpha fields

                STANK1964 could have packaged his solution and sold it for a fortune. I have been fighting this battle for years and looked on many forums for a solution that worked. I cannot believe that the solution is as simple as the'=' in front of the record. I tried it and it worked. I have now shared this with everyone I know who has had the same problem and there are many. Thank you for posting this jewell. Ed

                Comment


                • #9
                  Download to Excel drops leading zeros from alpha fields

                  I have a program that uses a CPYTOIMPF to convert a data base file to a comma delimited file using FLDDLM(,). The one field in question is an alpha field that contains 3 numeric characters with a leading zero and does convert with the leading zero as in this example: 030 . Then the program uses CPYTOPCD to put the file as a .csv in the AS/400 folder using TRNTBL(*DFT) and TRNFMT(*TEXT). If I look at the data, it still contains the leading zero as 030 . Then the program uses the SNDDST to email the .csv file to the user, which attaches as an excel document. When you open it, this particular field drops the leading zero and just gives the user 30 instead of 030. Excel does not realize this is an alpha field, and shows it right adjusted like any other numeric field. I even tried putting a dummy record in the file as the first record containing alpha characters in that field hoping excel would then determine the field is alpha. Is there any way to keep the leading zero when opened in excel? The user needs the file in excel to be used elsewhere.

                  Comment


                  • #10
                    Download to Excel drops leading zeros from alpha fields

                    hi tried stank1964's idea in a query as follows '=' || SUBSTR(DACCT#, 1,20) but didn't work help! thanks

                    Comment

                    Working...
                    X