1. Home
  2. Solidworks CAD Design software
  3. TIP: Import SW native BOM into Excel

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'

Reply to
Sporkman
Loading thread data ...

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

Reply to
rider89

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'

Reply to
Sporkman

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

Reply to
rider89

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'

Reply to
Sporkman

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.

Reply to
Sporkman

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

bill

Reply to
rider89

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

Reply to
robin.boudreault

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...

Reply to
Markku Lehtola

PolyTech Forum website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.