1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Ideas for inserting part from excel sheet?

Discussion in 'Alibre Script' started by ericwhite@burt, Apr 29, 2019.

  1. Hey guys,

    At my job we have various components saved into various libraries and to be honest, its is a mess. I would like to have an excel sheet where i can input data of all the files. Ideally it would have some function where I can click on it and insert it into the current assembly... Any ideas? I want to be able to filter/search through the excel file to find the specific part I am looking for, then easily insert it into alibre.

    I guess this would be a suedo parts library, minus the PDM aspect.
  2. Lew_Merrick

    Lew_Merrick Alibre Super User

    Eric -- Why not try to connect your Excel (xlsx assumed) through Global Variables (more often mis-called Global Parameters) and then perform a SaveAs from your Library Directoru into your Current Project Directory?
  3. Lew- thanks for reply. Let me know if I am misunderstanding you.

    I would like to avoid using SaveAs for each individual part. It would take up a lot of room on our servers and would not update previous assemblies of changes to particular parts.

    Another issues is the Library Directory gets messy after a while. You cannot move parts or you break associations, as you know very well. This makes it hard keep the library organized after years of use. (And why Alibre should create an actual parts library/PDM. I'd gladly pay another grand for that).

    Theoretically, you could have all your Parts in the 'Library Directory' just thrown into a folder, and not worry about organization. The excel sheet would be how you organize the 'Library Directory' and you wouldn't even need to look at the actual 'Library Directory'. If you need to change a part or recreate it, you just throw it into the 'Library Directory' while leaving the old 'out of spec' Part. Then you update the Excel Library to use the new/updated part. This lets you update parts or recreate them while not breaking old files. Not to mention all the benefits of having you part information in a neat, filterable excel sheet. Sure, the 'Library Directory' will be a mess after a while, but you would not use that as the interface. You would use the excel.

    So to circle back; This can be done but will be a lot of steps. Being able to have the Excel sheet insert parts into an assembly directly would make this system pretty painless.

    So far the best I have come up with is having the file path of the Part saved into the excel sheet. There is a built in excel function in Python called xlrd (excel reader). I have not messed with it, and my programming is rusty, but it seems this allows Python (And thereby Alibre, hopefully) to interact with an Excel sheet.
  4. Lew_Merrick

    Lew_Merrick Alibre Super User

    I may well be mis-understang you here, but I thought that you had a "Base Part" design that needed to be "modified" (slightly most likely) based on (if you will) differing "Project Variables." Thus, one "version" for (say) "Project X" that differs from the "Library version" and will (almost certainly) differ from that required for (say) "Project Y." Thus each "Version" gets saved with the "Project" rather than kept in the "Library." ???
  5. Kinda, but it's more like we do that because sometimes there is no better option. I would like to make a better option. The issues that I am trying to solve is the existing parts library is very disorganized. For example, we have files from years ago for different length pipes (but same diameter). Then coworkers learned about configurations. This was before my time here. Now in our pipe folder, we have '1"_PVC_1"_LONG', '1"_PVC_2" LONG'.... and so forth as separate files, followed by '1" PVC PIPE' that has different lengths as configurations (as it should be). We can't touch the old files, as it will break files that use it. So now our library is messy. This is just one area where our library has extra 'junk' that we can't get rid of. So if we cant get rid of it, I would like to 'hide it' via doing my organizing in an excel sheet. But lets say I just ignore the existing directories and make a new one. Whats to stop it from happening again down the road? I need a system to maintain library.

    The good news is I think I am on to something. I just did a quick test using openpyxl in Alibre Script and was able to read a specific cell in an excel sheet.

    Below successfully printed the contents of a specified cell. Developing this further I am thinking you can grab the part file path from the organized excel sheet and use AddPart( ) to insert it. So say I scripted a UI where I enter in the part type (Pipe), material (PVC), and size(1") and it looks up the row in excel, grabs the path, and inserts it. You would have a suedo parts library where you can ignore unwanted parts in the directory by excluding them from the excel sheet, but still be able to quickly insert them... in theory.....

    from openpyxl import load_workbook
    wb = load_workbook('EXCEL SHEET PATH.XLSX')
    sheet_ranges = wb['SHEET NAME']
  6. idslk

    idslk Senior Member

    Hello Eric,

    should your part "only" be inserted (AddPart) or should it be also automaticly constrained in the assembly?

    Additional question: By whom and how the table will be created with the columns filename,type,material,size and (maybe)file_is_valid? Who will take care for the table?

    Do you have an approximate value for the number of files?

    Last edited: May 2, 2019
  7. Lew_Merrick

    Lew_Merrick Alibre Super User

    I do something like that in that my Library has (among other things) "Iron Pipe Size" component Parts (all nominally 72 inches long) Thus, I can Open (from the Library) a Part that is (say) "6-000 Sch 80 X 72-000 Long" edit the Length and then SaveAs (to my Project directory) it as (say) 6-000 Sch 80 X 48-000 Long knowing that the Dimensions and Density values are correct. [It should be obvious that they "48.00 Long" is a Stock Cut value and that the Finished Part will be at least .465 inches shorter to allow for mis-cut by the supplier).
  8. NateLiqGrav

    NateLiqGrav Alibre Super User

    What do you envision for the script to look like? Each Sheet listed out alphanumeric order? Then each Row listed out in alphanumeric order? Or sorted out by some other method?
  9. Stefan, thanks for joining the discussion.

    Only inserted, constraining would be to complex (or impossible). For reference my company builds custom fluid processing systems. We mainly work with pumps, valves, plumbing, steel (angle, channel, etc), and plastic fabrication. Most systems are custom to the customer's specs, however we use a ton of components from various manufactures in our systems. The idea is to allow for fluid maintenance of our part library while keeping existing assemblies intact.

    I will make the excel sheet and maintain it. It will be a lot of work upfront but our library needs to be overhauled from the ground up. I would like to make sure all my work is not in vain a few years down the road.

    I have not thought that through yet, still need to make sure it is possible, and will actually be helpful instead of convoluting the process by adding steps that do not make it efficient. Really this is suppose to make it 1) easy to find parts and insert them and 2) make sure I don't end up needing to do a library overhaul again in a few years.

    It will definitely have part numbers, part category (Tee, Reducer, Pump, etc.), material, description, file path and whatever else my fellow engineers want. Ideally the flow process would have drop downs (maybe?) where you select part category> then the option to select material>then option to select size. Then this would point to a specific cell that contains the file path for the desired part. All of this is up in air since as of today I recently just proved the concept that an Alibre script can read specific sheets/cells in Excel and return them as stings, which this is possible theoretically.

    Nothing exact, but roughly 500-1500 at least. Keep in mind with plumbing we commonly use (for example) tees in half inch increments from 1/2" up to 12", which come in 4 different materials, so there are a ton of parts.

    Lew, this is an approach I use when I need to modify a part slightly. The issues is my company (which I am relatively new to) are not all on the same page as far as part descriptions, making parts accurate vs 'close enough' and so on. They are stubborn as some engineers are, so I'm hoping to make the system painless as possible so they will want to use it. I will handle maintaining the data base and keeping it up to date. Its unfortunate that I cannot rely on them to do that. They are all very smart and do a great job at what they do, but we desperately need to standardize and that is incredibly hard to do with Alibre and engineers suck in their ways!

    I wrote a lot so I'll recap. My end goal is to have a folder that contains all our library parts. This folder will start out nice and neat, but as time goes on it will inevitably become disorganized from needing to add/update parts since you cannot move/delete files easily without destroying dependencies. My proposed solution is to let this folder get disorganized, but ideally you won't even notice since all part insertion will be done via a script that references an excel file that contains part data. If you need to see if a part is in the library, you can use excel functions to quickly and easily find parts. If we decide a part is no longer needed, or say a manufacturer completely revamps a ball valve and we need to replace the file we can add it to the library and update the excel sheet file path (but leave the old one in the folder). In effect this will keep all previous files intact, but remove that file from the part library interface. Make sense? My brain hurts but I promise the logic is there...
    Last edited: May 2, 2019
  10. It's not that far yet (lol), but I can imagine something like this:

    1) You run the script and there is a drop down that list part type. Example: Tee / Reducer / Pump / etc...
    2) Selecting (for instance) Reducer prompts the script to return all options listed for reducers. Example: PVC / CPVC / 304SS / etc...
    3) Selecting material will return all reducer sizes that are for the selected material. Example: 1"X2" / 2"X3" / etc...
    4) Depending on the part type, would affect what options appear and when you get to the final step, you can insert the file.

    The process technically does not exist yet, as I only just achieved proof that Alibre Script can read Excel files.

    I will update as I get farther!
  11. NateLiqGrav

    NateLiqGrav Alibre Super User

    A few things that may be of use to you:

    To learn more about reading spreadsheet values using AlibreScript check out CreateLibrary.zip for an example.
    AlibreScript was originally called WizoScript so don't let that name there frighten you.

    Have you tried M-Files? It used to be sold with Alibre, but now it can only be bought separately. If you are at a bigger company it may be worth the investment.

    I have scripts that use this exact idea of narrowing down selections. When you get to that point let me know.
  12. Lew_Merrick

    Lew_Merrick Alibre Super User

    Eruc -- It is probably a (if you will) "type of work difference." I perform design work for many different companies each year. Thus (for Alibre using clients)) my "deliver" is a Package file that will "UnPackage" into a series of Directories and SubSirectories" that contain the component Parts and Assemblies in a fashion that allow my clients to open and maintain the information contained therein in a (mostly) sensible manner. Thus, my Libraries are set up to Hyperlink component files such that I can sensibly store them in Project Directory Formats. That way, my Spreadsheet Parts Lists only require the (client) user to copy in their File Directory Paths into a Worksheet that I always title as System_Values. That gives them a Hyperlink to each Component or Drawing file simply and easily.
  13. Nate,
    Thanks so much. I will read up on this when I get back to work! And unless it's to cumbersome— post what you got for that when you get a chance. I've made a few basic scrips with alibre and spent a lot of time reading the help book (or whatever they call it— the resource thing). So I've got a decent grasp on python basics, but I claim to be no programmer.

    If only the engineers I worked with were that organized! (within my company, companies I work with, and myself of course)

    Do you send parts as step files or someother importable format? How do you deal with drawing format? (I'm not familiar with how drawings interact without losing functionality like exporting as.dwg/.dxf)

    I only ask because some customers ask for the models, which we always oblidge (usually just a top level assembly in .stp format). But I haven't ran into any other company that uses alibre. A setup like yours would be very nice and definitely make the customer happy...
  14. Lew_Merrick

    Lew_Merrick Alibre Super User

    I work with whatever CAD system the Client demands (and is willing to supply a copy or VPN-link to me for). The problem with STEP is that neither Constraints nor Material Designations come through. About 75% of the companies I work with use Alibre (often because I set them up with it) and the rest balance out (mostly) between SolidWorks, CREO, and SolidEdge. Each has it's own "equivalent" to Package (though I wish that the Package format would include associated Drawings and (if you will) "Ancillary Files."

    As somebody who "grew up with" the MIL-Q-9858 Project Management system, the "core" for any Project for me is a (spreadsheet-based) Parts Listr that identifies all the Installation (read: Top Assembly), Assembly, Sub-Assembly, and Part (including Sheetmetal) Files by Identity, Nomenclature, and Revision, Material, Stock Size, and Function (as well as Drive/Directory Path) and Quantities (both Total and UsedOn Assembly). Thus, on a Project I am currently working on I have a "XYZ00400" part number defined as a "Idler Support Assembly" that has the Function of "Stabilizing and supporing the idler side of a Rotatory Sifting Assembly against XXX lbs of force and 250 RPM Rotational Inertia." The Identity of this Assembly is: "XTZ00400 Idler Support Assembly -- Rev New.ad_Asm." The "entry" in the Parts List where the "XYZ00400" part number is defined is also a Hyperlink to the indicated Part (or, more accurately, Assembly) and a separate Htperlink will active the Drawing file.

    Although I have carried this further, this was the MIL-Q-9858 documentation system required by the US Navy sine (in my experience) 1971.

Share This Page