Convert a BOM from drawing to excel spreadsheet

[ptc-1935566]

It's been along time since I last did this, but how do you convert a BOM
table in drawing to an excel spreadsheet in WF2.0?



Brian A. Enlow

Product Designer

Delta Faucet Company


This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

This thread is inactive and closed by the PTC Community Management Team. If you would like to provide a reply and re-open this thread, please notify the moderator and reference the thread. You may also use "Start a topic" button to ask a new question. Please be sure to include what version of the PTC product you are using so another community member knowledgeable about your version may be able to assist.

[ptc-1935566]

Also I know there is a way that you can modify the table in excel and
the table will update as well, but again it's been awhile and I forget
the steps?





Brian A. Enlow

Product Designer

Delta Faucet Company

(P) 317.848.0787

(F) 317.848.0750

brian.enlow@deltafaucet.com <">mailto:dab@deltafaucet.com>

[davidlawrence]

Brian,
If you change info_output_format from the default HTML to text in your
config.pro a copy of the BOM, filename.BOM.1, will be saved to your working
directory. You can then open it from Excel.

David Lawrence
Mechanical Engineer
Sigma Space Inc.

[MarkRafn]

also, you can format this output exactly how you want it. see
bom_format in the config options...
(Info > Bill of Materials > File)
-mark

[sneg3691]

After saving as a text and opening in M/S excel, I notice the text is all
over the place. Looks to me that you have to manually clean this up to make
it look presentable. Is this what others are doing? Or does the repeat
region in the table have a play in this?



Thanks

Kelly



_____

[davidlawrence]

MessageKelly,
When you open the BOM in Excel it should give you a chance format the
columns, widths, decimals, etc. I manually adjust the width to fit the
column headings, see attachment. You can adjust the column widths in a
custom BOM. We use the same one that is used in the drawing and keep the P/N
and descriptions lengths to fit in the drawing BOM. But you could use
multiple BOMs, one for export one for drawings. If the text length is larger
than the column width it will truncate the text and insert a new line so you
will have to play with those. As we're careful about the name lengths this
happens mainly on imported parts. You will have to manually handle the
assembly and sub assembly headings. I usually paste in a formula to
concatenate the headings back into one cell. Most times I only use parts
list portion of the BOM as this gives parts totals, especially useful for
fasteners and parts used multiple times.

David

[sneg3691]

Just tried it again, I am not offered the option to format the columns,
widths etc, Excel opens the txt file with no problems, however, I do not get
the options as seen in the attached file that was sent, text is all over
the place and seems to all run together. I did make my repeat region widths
a lot wider but that did not help any. Visually, the BOM in Pro Drawing
looks perfect. I do not have something set just right yet



Kelly



_____

[DarrinHiebert]

Make sure that your using Fixed width, and not Delimited on the first
screen:



Also, I've found that you have to do a File/Open from inside of Excel,
and not just use Excel to open the file on disk (like drag and drop). I
don't know why that changes things, but it does sometimes...

--

[davidlawrence]

MessageKelly,
The BOM is stored as <file_name>.BOM.1. Open Excel then File-Open- All
Files and navigate to where the file was saved.You should see the formatting
dialog described in the previous email.

David

[dgschaefer]

Sometimes, Excel just opens a text file, sometimes it tries to convert
it. Why? I dunno, but here's what to do.

In Excel select the column containing the data (almost certainly the
first column) and then select Data -> text to columns. That will launch
the conversion wizard.

Doug Schaefer

[cncwhiz]

This sounds like the same column issue that I have in pro manufacturing
opening up a pro process table in excel. The widths are all over the place?



_____

[DarrinHiebert]

That's a slick trick there. I didn't know that existed, so I was always
killing Excel, and then going File/Open in Excel. That method will
always do the "text to column" operation, but it's somewhat of a pain,
because you can't use things like drag-and-drop then...

--

[eslotty]

If the table is not shown on the drawing, sneak a column filled with a
comma's between the fields before exporting....

Works every time!

Regards,




GE Healthcare Technologies
Clinical Systems
Monitoring Solutions
Eric R. Slotty
Mechanical Designer
8200 W. Tower Avenue
Milwaukee, WI 53223
Phone: (414) 362-2552
Fax: (414) 362-2880
e-mail: -
Visit Us On The Internet:

[sneg3691]

Thank you all !!!!

Sometimes I need to be hit over the head with a brick bat I swear.

I am getting real close now, one more question and I think I'll have it
licked.

Excel is creating an empty space every other line item, (see below) how can
I quickly fix this, other than selecting every empty line item and deleting?



Thanks

Kelly







_____

[davidlawrence]

MessageKelly,
The probable cause is that one of your data fields is exceeding the BOM
column width and spilling over on to an extra line. Using a custom BOM would
solve that. Although it looks like you could sort by column A and get rid of
the extra lines.

David

[sneg3691]

Thank you to; David, Tony, Darrin, Chris and all others who chimed in on
this thread!!!!!

Finally. drilled down to what I was doing wrong, and got issues corrected.



Thanks again, what huge helping hands you all are.

Kelly

.









_____

[MarkRafn]

~hey all

Not sure if we're talking now about the drawing > Table > Save as Text
-or- using Info > Bill of Materials > File method...

But if using Info > BOM method, you can format the output as fixed
lengths. From there when opening in Excel, you can easily see the
column spacing

here's an example of one file we use to create a simple BOM:

.breakdown
Parent
%PART_NUMBER[-7.7s]
.titles
.row 1 ;%PART_NUMBER[-7.7s]; %DESCRIPTION2[-30.30s]; %$QUANTITY[-3d]

(obviously it is using User Parameters)

A better way though is the JavaScript I've seen posted or write your own
JLink to export the table. I've got a JLink routine that copies to the
clipboard and you can paste it fro there to Excel; select the drop-down
formatting and use the Text Import filter.

-mark

[CBenner]

I hate to reopen an old thread like this, but it is a related question.....

Can Pro-E be made to export a BOM in an indented form, showing all sub-assemblies and components by level, such as you would get if you looked at Model Info. It's kinda funny... Model info gives you the indent, but no BOM info... just the model names... BOM gives you the parameters you ask for but doesn't seem to wat to indent them.

Any thoughts?

Chris Benner
Designer II
Powell Fabrication & Manufacturing, Inc.
<u>www.powellfab.com
P: 989-681-2158
F: 989-681-5013
</u><u></u>

[ptc-1182355]

For me, the easiest way to have an indented excel parts list was to create a table with three repeat regions (usually I have two or three levels). See attached file.

I've seen you can also define some indentation in repeat regions in proE help, but I've never tried.

I hope this helps.