| Author |
Message |
Sporkman
Guest
|
 Posted:
Sun Dec 11, 2005 5:10 pm Post subject:
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'
|
|
| Back to top |
|
 |
Sporkman
Guest
|
| Posted:
Sun Dec 11, 2005 9:10 pm Post subject:
Re: Import SW native BOM into Excel |
|
|
rider89 wrote:
| Quote: |
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' |
|
| Back to top |
|
|
rider89
Guest
|
| Posted:
Sun Dec 11, 2005 9:10 pm Post subject:
Re: Import SW native BOM into Excel |
|
|
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
"Sporkman" <sporkedUNDERLINEagainMUNGE@bigfootDOT.com> wrote in message
news:439C7B4C.7884F231@bigfootDOT.com...
| Quote: | rider89 wrote:
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' |
|
|
| Back to top |
|
|
rider89
Guest
|
| Posted:
Sun Dec 11, 2005 9:10 pm Post subject:
Re: Import SW native BOM into Excel |
|
|
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
"Sporkman" <sporkedUNDERLINEagainMUNGE@bigfootDOT.com> wrote in message
news:439C4B0E.8AEC1894@bigfootDOT.com...
| Quote: | 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' |
|
|
| Back to top |
|
|
Sporkman
Guest
|
| Posted:
Mon Dec 12, 2005 1:10 am Post subject:
Re: Import SW native BOM into Excel |
|
|
rider89 wrote:
| Quote: |
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' |
|
| Back to top |
|
|
Sporkman
Guest
|
| Posted:
Mon Dec 12, 2005 1:10 am Post subject:
Re: Import SW native BOM into Excel |
|
|
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. |
|
| Back to top |
|
|
rider89
Guest
|
| Posted:
Mon Dec 12, 2005 8:30 am Post subject:
Re: Import SW native BOM into Excel |
|
|
aren't we supposed to be watching NFL cheerleaders today anyway?
bill
"Sporkman" <sporkedUNDERLINEagainMUNGE@bigfootDOT.com> wrote in message
news:439CC19D.8FF3A4BD@bigfootDOT.com...
| Quote: | 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. |
|
|
| Back to top |
|
|
|
|
|
|
FAQ
Memberlist
Register
Profile
Log in to check your private messages
Log in