TIP: Import SW native BOM into Excel

I've seen questions on the newsgroup about this, but I hadn't had time
to see whether what I thought might work would actually work. Turns out
it will . . . sorta . . . with some fair amount of cleanup required.

Starting with an assembly drawing with a SolidWorks native format BOM in
it:
1) Highlight the BOM and from the File pull down menu choose SaveAs.
2) The default file format in the "Save as type:" scroll list is
"Template (*.sldbomtbt)"; instead from the scroll list choose "Text
(*.txt)"
3) Use a text editor such as UltraEdit (preferably) or Notepad to insert
semicolons in the text file between data which should define columns.
Where you want to skip a column, insert two semicolons. (Spaces and tabs
will be irrelevant if you follow the instructions below.)
4) Open up Excel, and from the File > Open dialog box choose "Text Files
(*.prn, *.txt, *.csv)" and find the file you output from the SolidWorks
assembly drawing BOM.
5) In the next dialog box, choose "Delimited" (the default).
6) In the next dialog box, choose "Semicolon" as the delimiter. Excel
will give you a preview of what the file will end up looking like.
7) Clean up the new Excel file.

'Sporky'

 

I'm not understanding the thing about inserting semicolons.
After exporting to a csv, I do the file->open in excel (with the cursor in
cell A1)
and the data loads with the columns and rows ready to go.
I think the CR characters already in the csv file tell excel where to end
the rows.

Bill

 

Well yes, if you create a .csv file instead of a .txt file SolidWorks
inserts commas in between fields. The only problem with that is when
someone uses commas in Descriptions or Filenames. When you import a
..csv file Excel won't ask you what kind of delimiter you want to use . .
.. it automatically uses commas. With a .txt file you get a choice.
Either way you get to clean things up manually, but it's a bit less
confusing (not to mention less work) if you don't get extra columns that
you didn't want. But if you want to do it that way, who'm I to suggest
otherwise?

'Sporky'

 

I didn't catch the issue about commas in the data.
I just tried it and opened the (unedited) text file with delimited checked,
but used "tabs" as a delimiter,
and the data with the comma came into the excel column OK.
I'm using excel2000.
Does your setup not work that way?

Bill

 

As I said, the problem with comma delimiting is that if there are any
unexpected commas (as in a Description or a Filename) additional columns
are created. If you don't mind having to combine data and shift data
from one column to another, comma delimiting works fine. But creating a
..txt file instead of a .csv file doesn't insert commas between fields.
That's an advantage in many (perhaps most) cases, especially if you ever
use commas in Descriptions (e.g., "BRACKET, MOUNTING").

AS I LOOK INTO IT FURTHER I find that it's unnecessary even to include
semicolons in the .txt file. Just import the file into Excel and choose
Tabs as the delimiter. Result is just as good if not better and it's
less work.

'Sporky'

 

Geez, Louise. I should read more carefully. You said just exactly what
I said in a different way . . . and you noticed the advantage in using
Tabs as delimiters first. Sorry.

 

aren't we supposed to be watching NFL cheerleaders today anyway?

bill

 

Simpler way,

Go in Bom content, copy the bom by choosing the first column (the one
with items number) from top to bottom, ctrl-c, go to blank excel file,
ctrl-v, delete useless column.

Robin
Montreal, Canada

 

No need for cleanup, just save bom as text
and then in Excel, open it with "Data-Get external data-Import text
file" (propably not the exact pulldown menu commands because I'm running
finnish Excel right now), then just press "next" and that's it...

--

regards

Markku Lehtola

www.markkulehtola.net