5 Replies Latest reply on Nov 24, 2014 3:37 AM by robert_davies

    Leading zeros with Part Lister to Excel

    aaron.cramer

      Hello,

       

      Is there a way to retain leading zeros when exporting a BOM using the Part Lister to an xls file?

       

      One of my columns is PKG_TYPE which contains the value "0603" among others.  When I export the BOM to an .xls file, I lose the leading zero and am left with "603".  If I export to text, then import to Excel, I can define that column to be text on import, in which case I get my leading zero.  I would like to eliminate this intermediate step, if possible. 

       

      Thoughts?

       

      Thanks!

        • 1. Re: Leading zeros with Part Lister to Excel
          David Ricketts

          I'm pretty sure that's an Excel problem, not PADS. I'm also sure there's a way to create a formatted template in Excel, so when you start with that and import the data, all the columns will format as you want.

          • 2. Re: Leading zeros with Part Lister to Excel
            jduquette

            I don't use PartLister much, so it would be nice to have it format that correctly (i.e. text, not a number).

             

            Potential workarounds for Excel:

            - change 0603 to '0603 for your entire library so Excel recognizes it as a text entry

            - Use "0###" formatting in Excel, so 603 shows up as 0603, 805 shows up as 0805, and 12 shows up as 0012

            • 3. Re: Leading zeros with Part Lister to Excel
              MENTOR_Shannonk

              htmlANDexcel.PNG

              Use the HTML output choice. Top is HTML and the bottom is the Excel output.

               

              If you need to manipulate the partlist you can open it in Excel with no formatting needed.

              • 4. Re: Leading zeros with Part Lister to Excel
                phil.lindberg

                Sorry to bring this topic back to life..

                 

                Is there yet a full solution to this problem?

                 

                My engineers want to get an Excel BOM directly out of DX. Manual translation from HTML (or text) to Excel is yet another task - and a source of error.

                 

                I see that part numbers with leading zeroes are correctly formatted in Excel (i.e., part numbers like "0502-1R2KBZ" aren't truncated by Part Lister).

                 

                Why can't I apply the same kind of logic and formatting that's used for Part Number to Package Type?

                 

                Thanks!

                • 5. Re: Leading zeros with Part Lister to Excel
                  robert_davies

                  The problem lies with Excel not the Part Lister, if you output a CSV file and open it in a text editor you will see it contains the leading zero. If you then import this or open it directly in Excel the leading zero is stripped out. The reason other items are seemingly showing correctly is because they contain a mixture of letters and numbers which Excel interprets as text. One option would be to output a text file with the | as the delimiter then import it into Excel with the correct settings for each column (you can probably use a standard template or a macro)