{"title":"Import a Parts List from Excel","slug":"import-a-parts-list-from-excel","body":"Many official parts lists are managed externally in a PDF or the 3D CAD tool. These parts list can be imported as a QuadriSpace parts lists by mapping an a key name from the Excel file to a key name already in your 3D part’s meta-data.\n\nQuadriSpace includes tools that allow users to import BOMs exported from a CAD tool, a PDM database or generated manually using a spread sheet program. This allows external data to be used to indicate balloon numbering or rows/columns in a table.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"STEP 1: SET UP THE EXCEL SPREADSHEET\"\n}\n[/block]\nThe first step is to generate the proper spread sheet. Most users will start by exporting a BOM from a CAD tool or PDM system. If necessary, Microsoft Excel can then be used to further manipulate the data.\n\nThe first row of the spread sheet must contain the titles for the individual fields. If you need to associate parts with an individual row, then you will need to make sure that one of the columns is a key string that can be used to identify the parts. QuadriSpace supports some special fields that are used in specific ways within the program, such as Call Number and Quantity. If you plan to use these then you will need specific columns for this information.\n\nAn example BOM in xls format and a matching 3D model is attached below.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"STEP 2: IMPORT THE PARTS LIST INTO QUADRISPACE\"\n}\n[/block]\nTo import the Parts List into QuadriSpace:\n[block:callout]\n{\n  \"type\": \"success\",\n  \"title\": \"Import a Parts List\",\n  \"body\": \"- Select the Model Ribbon\\n- In the Parts List Ribbon Group, click Import\\n- Browse and select the Excel Spreadsheet\\n- A dialog will display to select the sheet that the BOM resides on, usually this will be Sheet1$.\\n- You will now need to map the fields your spread sheet to the special fields in QuadriSpace. Any fields that are now mapped will still be imported, as columns in the BOM.\\n- “Map Parts By” choice tells the mapping tool how to match 3D parts with BOM rows. Custom\\n- Field Mapping is the most common option and allows you to select a MetaData field in your 3D model and map this to a Column title in your Excel spread sheet. For mapping to occur, the names in both fields must match exactly. Some SolidWorks users may choose to use the “SolidWorks File-Configuration” name option.\\n- The call number is treated as a special field in QuadriSpace and is automatically used when balloons are added to an illustration. Quantity is another special field in QuadriSpace.\\n- Press OK, to import the BOM.\\n- This will bring up the BOM Editor which you can use to edit the BOM, if desired.\\n- Click “Done” in the top right corner to exit BOM editing.\\n- To verify that you have imported the BOM properly and have associated the parts in your 3D model, click on the Model Panel Button.\\n- When the model panel is displayed, use the pull-down at the top of the panel to select the BOM you just created by name.\"\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"STEP 3: USE THE PARTS LIST\"\n}\n[/block]\nUses of imported BOMs:\n- Add Balloon Numbers to illustrations that are consistent with external BOM data\n- Add BOMs to Word publishing in Publisher3D\n- Add BOMs to documents in Pages3D\n- Create interactive 3D documents with BOMs that can be clicked to select parts\n- Set up Mouse-over tips\n\n\nLimitations of Importing a BOM\n- A BOM row can associate with multiple parts of the same type. However, the row cannot associate with multiple parts of different types. If this is desired, you will need to use the BOM editor in QuadriSpace to manually add additional parts to a row.\n- Only Excel (xls) import is supported\n- Importing Indented BOMs is not currently supported.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Exporting a BOM from SolidWorks\"\n}\n[/block]\n**** \n[block:callout]\n{\n  \"type\": \"success\",\n  \"title\": \"Export a Parts List from SolidWorks\",\n  \"body\": \"- Right click on the BOM in either an assembly or drawing view\\n- Select “Save As”\\n- Select “Excel (*.xls)” from the Save As Type pulldown\\n- Type a filename in the File Name box\\n- Press Save\"\n}\n[/block]\n\n#Recommended Settings for Importing a SolidWorks BOM\nThe following are the recommended settings for SolidWorks BOM Import. Note that the First Row of the Excel File MUST contain the column headings, SolidWorks will sometimes export the BOM with the column headings in the last row.\n\n- Map Parts By: Custom Field Mapping\n- Meta Data Field: DocumentName\n- Excel Column: Part Number\n\n- Call Number: ITEM NO#\n- Quantity: QTY#\n- Row Name: PART NUMBER\n\nNote: these settings may vary according to your specific exported BOM.\n \n#SolidWorks Configuration Name Mapping\nWhen importing a BOM from Excel, it is necessary to map a field in the Excel spreadsheet to a field in 3D Part meta data. This is required for imported BOMs to properly assign balloon numbers and select parts in the 3D scene. One of the options for mapping is “SolidWorks File – Configuration Mapping”.\n\nIn order to use this option, the BOM must include the following fields before exporting from SolidWorks:\n\n- SW Filename\n- SW Configuration Name\n\nIf these fields are exported to the Excel spreadsheet, then QuadriSpace will detect this on import and default to the “SolidWorks File – Configuration” mapping option.\n\nIn most cases, this is not the recommended mapping method because most user created BOMs in SolidWorks do not have these two fields in the table and a little setup is required by the SolidWorks user. In most cases, SolidWorks users will not use this option but will opt for the general approach by selecting “Custom Field Mapping” instead.","_id":"55b6e7676127b1250036459f","tags":[],"user":{"name":"QuadriSpace Team","username":"","_id":"55a6dc0c112cf20d003a3196"},"createdAt":"2015-07-28T02:22:31.946Z","project":"55b41741c8f1ca1700e5e194","__v":0,"changelog":[],"metadata":{"title":"","description":"","image":[]}}

Import a Parts List from Excel


Many official parts lists are managed externally in a PDF or the 3D CAD tool. These parts list can be imported as a QuadriSpace parts lists by mapping an a key name from the Excel file to a key name already in your 3D part’s meta-data. QuadriSpace includes tools that allow users to import BOMs exported from a CAD tool, a PDM database or generated manually using a spread sheet program. This allows external data to be used to indicate balloon numbering or rows/columns in a table. [block:api-header] { "type": "basic", "title": "STEP 1: SET UP THE EXCEL SPREADSHEET" } [/block] The first step is to generate the proper spread sheet. Most users will start by exporting a BOM from a CAD tool or PDM system. If necessary, Microsoft Excel can then be used to further manipulate the data. The first row of the spread sheet must contain the titles for the individual fields. If you need to associate parts with an individual row, then you will need to make sure that one of the columns is a key string that can be used to identify the parts. QuadriSpace supports some special fields that are used in specific ways within the program, such as Call Number and Quantity. If you plan to use these then you will need specific columns for this information. An example BOM in xls format and a matching 3D model is attached below. [block:api-header] { "type": "basic", "title": "STEP 2: IMPORT THE PARTS LIST INTO QUADRISPACE" } [/block] To import the Parts List into QuadriSpace: [block:callout] { "type": "success", "title": "Import a Parts List", "body": "- Select the Model Ribbon\n- In the Parts List Ribbon Group, click Import\n- Browse and select the Excel Spreadsheet\n- A dialog will display to select the sheet that the BOM resides on, usually this will be Sheet1$.\n- You will now need to map the fields your spread sheet to the special fields in QuadriSpace. Any fields that are now mapped will still be imported, as columns in the BOM.\n- “Map Parts By” choice tells the mapping tool how to match 3D parts with BOM rows. Custom\n- Field Mapping is the most common option and allows you to select a MetaData field in your 3D model and map this to a Column title in your Excel spread sheet. For mapping to occur, the names in both fields must match exactly. Some SolidWorks users may choose to use the “SolidWorks File-Configuration” name option.\n- The call number is treated as a special field in QuadriSpace and is automatically used when balloons are added to an illustration. Quantity is another special field in QuadriSpace.\n- Press OK, to import the BOM.\n- This will bring up the BOM Editor which you can use to edit the BOM, if desired.\n- Click “Done” in the top right corner to exit BOM editing.\n- To verify that you have imported the BOM properly and have associated the parts in your 3D model, click on the Model Panel Button.\n- When the model panel is displayed, use the pull-down at the top of the panel to select the BOM you just created by name." } [/block] [block:api-header] { "type": "basic", "title": "STEP 3: USE THE PARTS LIST" } [/block] Uses of imported BOMs: - Add Balloon Numbers to illustrations that are consistent with external BOM data - Add BOMs to Word publishing in Publisher3D - Add BOMs to documents in Pages3D - Create interactive 3D documents with BOMs that can be clicked to select parts - Set up Mouse-over tips Limitations of Importing a BOM - A BOM row can associate with multiple parts of the same type. However, the row cannot associate with multiple parts of different types. If this is desired, you will need to use the BOM editor in QuadriSpace to manually add additional parts to a row. - Only Excel (xls) import is supported - Importing Indented BOMs is not currently supported. [block:api-header] { "type": "basic", "title": "Exporting a BOM from SolidWorks" } [/block] **** [block:callout] { "type": "success", "title": "Export a Parts List from SolidWorks", "body": "- Right click on the BOM in either an assembly or drawing view\n- Select “Save As”\n- Select “Excel (*.xls)” from the Save As Type pulldown\n- Type a filename in the File Name box\n- Press Save" } [/block] #Recommended Settings for Importing a SolidWorks BOM The following are the recommended settings for SolidWorks BOM Import. Note that the First Row of the Excel File MUST contain the column headings, SolidWorks will sometimes export the BOM with the column headings in the last row. - Map Parts By: Custom Field Mapping - Meta Data Field: DocumentName - Excel Column: Part Number - Call Number: ITEM NO# - Quantity: QTY# - Row Name: PART NUMBER Note: these settings may vary according to your specific exported BOM. #SolidWorks Configuration Name Mapping When importing a BOM from Excel, it is necessary to map a field in the Excel spreadsheet to a field in 3D Part meta data. This is required for imported BOMs to properly assign balloon numbers and select parts in the 3D scene. One of the options for mapping is “SolidWorks File – Configuration Mapping”. In order to use this option, the BOM must include the following fields before exporting from SolidWorks: - SW Filename - SW Configuration Name If these fields are exported to the Excel spreadsheet, then QuadriSpace will detect this on import and default to the “SolidWorks File – Configuration” mapping option. In most cases, this is not the recommended mapping method because most user created BOMs in SolidWorks do not have these two fields in the table and a little setup is required by the SolidWorks user. In most cases, SolidWorks users will not use this option but will opt for the general approach by selecting “Custom Field Mapping” instead.