Inventor General

Reply
Active Contributor
barsea
Posts: 27
Registered: ‎06-07-2011
Message 1 of 8 (1,242 Views)

BOM export to formatted text

1242 Views, 7 Replies
04-02-2012 02:07 PM

I've found a lot of information on exporting BOM lists to excel, access, etc. but what I am trying to accomplish is to export assembly BOMs into a text file that I can import into our ERP software.

 

Our ERP software requires that the first character of the line be an 'S' then in the next 30 characters is the part number (trailing spaces are ignored) and then the quantity in the next 14 characters and finally the sequence of operations in the remaining 6 characters, a total of 51 characters per line.

 

I am using Inventor Simulation 2011 and thought that I might be able to do this by creating a custom rule; however, the BOM export snippets simply mirror the export features from the BOM table.

 

Am I going to have to do this in VBA or is it possible to accomplish this through creating a rule?

*Expert Elite*
Curtis_Waguespack
Posts: 2,566
Registered: ‎03-08-2006
Message 2 of 8 (1,238 Views)

Re: BOM export to formatted text

04-02-2012 02:11 PM in reply to: barsea

Hi barsea,
Can you provide a screen shot of a typical BOM, and maybe an example .txt file?

Also (just to confirm) are you wanting to export from the BOM (assembly file) or from the Parts List (drawing file)?

 

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com




  solution.png  Did you find this reply helpful ? If so please use the Accept as Solution or  Kudos button below.





Active Contributor
barsea
Posts: 27
Registered: ‎06-07-2011
Message 3 of 8 (1,223 Views)

Re: BOM export to formatted text

04-02-2012 02:45 PM in reply to: Curtis_Waguespack

Thank you for your reply Curtis,

 

I have attached two files for a small assembly, the first is the BOM table but the arrangement of the columns will not necessarily be as they are shown as different people in our group tend to arrange them differently.

 

The second is the formatted text file.  It may be difficult to see but extra spaces are always trailing the text or number, the last number having five spaces following it, in this example.

 

I am trying to export from the assembly itself, not a drawing.

 

I have noticed that as I am editing the rule if I hover over the BOM export snippet, a popup message says that other formats besides those provided are available but I have to consult the Inventor API.  I have not been able to find the documentation for the API though.

 

Thank you for your help!

*Expert Elite*
Curtis_Waguespack
Posts: 2,566
Registered: ‎03-08-2006
Message 4 of 8 (1,168 Views)

Re: BOM export to formatted text

04-04-2012 08:26 AM in reply to: barsea

Hi barsea,

 

Where does the sequence of operations number originate from? I don't see that in your assembly BOM, so I'm curious at what point this gets assigned.

 

I'll kick this around today as time permits and see if I can figure something out.

 

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com




  solution.png  Did you find this reply helpful ? If so please use the Accept as Solution or  Kudos button below.





Active Contributor
barsea
Posts: 27
Registered: ‎06-07-2011
Message 5 of 8 (1,164 Views)

Re: BOM export to formatted text

04-04-2012 08:40 AM in reply to: Curtis_Waguespack

Hi Curtis,

 

Thanks again for replying.  Yes, that's something I was hoping to add during the parsing process - I've been able to create input text boxes for other rules and my thought was to create a prompt while running the rule to supply the sequence number.  Because the sequence involves the ERP system and not the CAD BOM, it would be quite a lot of work to add the sequence number to each part, not to mention that the parts themselves may be in different sequences for different assemblies such as bolts and washers for example.

 

I still have not been able to find where in the API documentation that I can export to formats other than, for example, comma delimited / tab delimited text, etc.  I was hoping to be able to export selected columns to text and write code that would pad the spaces between the part number, quantity and sequence number - if that's possible.

 

Sean

*Expert Elite*
Curtis_Waguespack
Posts: 2,566
Registered: ‎03-08-2006
Message 6 of 8 (1,158 Views)

Re: BOM export to formatted text

04-04-2012 08:52 AM in reply to: barsea

Hi barsea, 

Here's a bit of code to get  you started, this writes the file out as a tab delimited txt file to same name and path as the assembly. 

 

 

fileName = ThisDoc.PathAndFileName(False) & ".txt"
ThisBOM.Export("Structured", fileName, kUnicodeTextFileTabDelimitedFormat)

 

Here's a list of the format enumerators:

 

kMicrosoftAccessFormat            = Microsoft Access
kMicrosoftExcelFormat            = Microsoft Excel
kdBASEIIIFormat                = dBASE III
kdBASEIVFormat                = dBASE IV
kTextFileTabDelimitedFormat        = Text File Tab Delimited
kTextFileCommaDelimitedFormat        = Text File Comma Delimited
kUnicodeTextFileTabDelimitedFormat    = Unicode Text File Tab Delimited
kUnicodeTextFileCommaDelimitedFormat    = Unicode Text File Comma Delimited

 

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com




  solution.png  Did you find this reply helpful ? If so please use the Accept as Solution or  Kudos button below.





Active Contributor
barsea
Posts: 27
Registered: ‎06-07-2011
Message 7 of 8 (1,138 Views)

Re: BOM export to formatted text

04-04-2012 03:27 PM in reply to: Curtis_Waguespack

Thanks for your help Curtis.

 

I spent the better part of the afternoon going over the system and custom code snippets supplied in Inventor and - in sort of a Frankenstein monster version - cut and paste and tested until it works as I need it to.  I've copied the code here to this message thread in the off chance that it might help anyone else.

 

I'm not a programmer so if anyone sees any problems that I might have with the code, please feel free to comment.

 

' Path to the end result datafile
filePath = "L:\Program Files\SYSPRO60\base\Inventor\"


'____Create BOM and save to text file w/ same name as assembly
fileName = ThisDoc.PathAndFileName(False) & ".txt"
ThisBOM.Export("Structured", fileName, kUnicodeTextFileTabDelimitedFormat)

' Read the datafile back into the system for parsing
Dim ReadTAB As New System.IO.StreamReader(ThisDoc.PathAndFileName(False) & ".txt")
Dim WordSets As New ArrayList()

k = 0
Do While ReadTAB.Peek <> -1
WordSets.Add(ReadTAB.ReadLine.Split("	"))
k += 1
Loop

ReadTAB.Close()

' Ask for the sequence number, default to 1
SeqNumber = InputBox("Enter the SYSPRO sequence number for this assembly", "Sequence Number", 1)
Do While Len(SeqNumber) < 6
SeqNumber += " "
Loop


'____Create and write to a text file_________________
' the .txt file is for file verification, the .dat file is the default
' extension used by the ERP
oWrite1 = System.IO.File.CreateText(filePath & ThisDoc.FileName(False) & ".txt")
oWrite2 = System.IO.File.CreateText(filePath & ThisDoc.FileName(False) & ".dat")

' find the column for the part number
i = 0
Do While String.Compare(WordSets.item(0)(i), "Part Number")
i += 1
Loop

' find the column for the quantity
j = 0
Do While String.compare(WordSets.item(0)(j), "QTY")
j += 1
Loop

' loop to create the parsed file
m = 1
Do While m < k 
	PartNumber = WordSets.item(m)(i)
	Do While Len(PartNumber) < 30
	PartNumber += " "
	Loop

	Quantity = WordSets.item(m)(j)
	Do While Len(Quantity) < 14
	Quantity += " "
	Loop
	
	dataLine = "S" + PartNumber + Quantity + SeqNumber
	
	oWrite1.WriteLine(dataLine)
	oWrite2.WriteLine(dataLine)
	
	m += 1
	
Loop

' close the finished files
oWrite1.Close()
oWrite2.Close()

'open the file to verify the data has exported as expected
ThisDoc.Launch(filePath & ThisDoc.FileName(False) & ".txt")

 Thanks again for your help Curtis!

 

Sean

*Expert Elite*
Curtis_Waguespack
Posts: 2,566
Registered: ‎03-08-2006
Message 8 of 8 (1,111 Views)

Re: BOM export to formatted text

04-05-2012 11:40 AM in reply to: barsea

Hi barsea,

 

I just re-read you original questions and realized what you were asking was if there was a way to export the BOM and choose which columns to export (like we can with the parts list export). As far as I can tell there isn't a way to do that. So I was taking the same approach as you on this and exporting the BOM and then using streamreader to bring it back in and parse it out. I went a different direction for there and didn't got stuck and never got back to it, but I like your method better anyway. Thanks for sharing your solution, I'm sure someone will find it useful in the future.

 

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com


 

 



  solution.png  Did you find this reply helpful ? If so please use the Accept as Solution or  Kudos button below.





You are not logged in.

Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register

Announcements
Welcome to the new Autodesk Community!
If this is your first visit, click here to get started and make the most of the Community. Let us know what you think of the new experience in the Community Feedback Forum.

Need installation help?

Start with some of our most frequented solutions to get help installing your software.

Ask the Community


Inventor Exchange Apps

Created by the community for the community, Autodesk Exchange Apps for Autodesk Inventor helps you achieve greater speed, accuracy, and automation from concept to manufacturing.

Connect with Inventor

Twitter

Facebook

Blogs

Pinterest

Youtube