Technical documentation - Import using Excel
Users can import parts, part assemblies, kits, catalogues, documents and specifications from Excel files. Each type have different configuration sections. Users can create multiple configuration in the same file differentiated by configuration name.
Overview
The import.config can be used to define a set of import templates for presentations and other data. The config is used to define which columns or cells in the XLSX goes into which attribute or specification on the imported object. It also defines what happens if cells are empty and if column names are to be validated.
General settings
All types of imports have some settings which are general. Sample config:
<ExcelPartConfigs>
<ExcelPartConfig on-update-keep-previous-value-if-null="true" config-name="MyConfig" remove-macros="true">
</ExcelPartConfig>
<ExcelPartAssemblyConfig on-update-keep-previous-value-if-null="true" config-name="Default" remove-macros="true">
</ExcelPartAssemblyConfig>
</ExcelPartConfigs>Each template has three settings.
Setting | Usage |
|---|---|
on-update-keep-previous-value-if-null | True: If a cell is empty (null) the import will not overwrite the existing value in the database, it will be untouched. This is the default value if nothing is specified. False: If a cell is empty (null) the import will make the corresponding value in the database empty (null). |
config-name | This is the name that will display in the template selection drop down in the Manager. |
remove-macros | If true, import will create a temporary copy of the XLS file and remove all macros from that file before import is started. Set the value to false unless other needed, since removing macros may cause access rights problems on a server. |
Macros in Excel
In some cases macros can cause problems at import. If this happens ensure to set remove-macros="true". Please note that this setting may require changing access rights for the processes running the import.
Options available to all presentations
Version 5.3 and later
It is possible to skip presentations if a cell contains a specific value, or include a presentation if a cell contains a specific value. This setting can be applied to all presentations and to part rows in assemblies.
<ImportIf row="3" column="1" direction="Down" true-on-value="..01" default-value="false"/>
<SkipImportIf row="3" column="25" direction="Down" true-on-value="0" default-value="false"/>Excel parts import
Note! If row or column is set to 0, the value will not be used. So the below default config will actually not read any values and you will need to create your own config.
Excel part import configuration
<ExcelPartConfigs>
<ExcelPartConfig on-update-keep-previous-value-if-null="true" config-name="MyConfig" remove-macros="true">
<Name row="0" column="1" direction="Down" culture="en-US" />
<Description row="0" column="2" direction="Down" culture="en-US" />
<SpecificationType culture="en-US" name="" type="Text" persistent-id="">
<Specification row="0" column="3" direction="Down" culture="en-US" />
</SpecificationType>
<Identity row="0" column="4" direction="Down" />
<PersistentIdentity row="0" column="0" direction="Down" />
<PartNumber row="0" column="4" direction="Down" />
<Note row="0" column="5" direction="Down" />
<Classification row="0" column="6" direction="Down" culture="en-US" code="" name="" />
<Image row="0" column="7" direction="Down" />
<IncludeInPublish row="0" column="8" direction="Down" true-on-value="true" default-value="false" />
<Sellable row="0" column="9" direction="Down" true-on-value="yes" default-value="false" />
<SupplierNumber row="0" column="10" direction="Down" />
<Permission row="0" column="11" direction="Down" />
<QuantityUnit row="0" column="12" direction="Down" culture="en" />
<PartReplacement>
<Remark row="0" column="13" direction="Down" culture="en-GB" />
<Code row="0" column="14" direction="Down" culture="en-GB" />
<Date row="0" column="15" direction="Down" culture="en-GB" />
<ReplacementEntry>
<PartPersistentIdentity row="0" column="16" direction="Down" />
</ReplacementEntry>
</PartReplacement>
<ItemStatus>
<Status row="0" column="0" direction="Down" />
<Note row="0" column="0" direction="Down" />
</ItemStatus>
<AddToKit>
<Identity row="0" column="0" direction="Down" />
<PersistentIdentity row="0" column="0" direction="Down" />
<Number row="0" column="0" direction="Down" />
<Quantity row="0" column="0" direction="Down" />
<KitPresentationType>
<Name row="0" column="0" direction="Down" culture="en-US" />
<Code row="0" column="0" direction="Down" />
</KitPresentationType>
<KitPartPresentationType>
<Name row="0" column="0" direction="Down" culture="en-US" />
<Code row="0" column="0" direction="Down" />
</KitPartPresentationType>
</AddToKit>
</ExcelPartConfig>
</ExcelPartConfigs>Importing html to specification
Starting in version 5.1 it is possible to import html into specifications of type HtmlText and HtmlString. Sample config is as below.
Part import configuration settings
"Name", "Description" and other text fields can have several translations in different languages.The attribute "row" and "column" represent rows and columns in the Excel sheet. The attribute "direction" is the direction from where the import will start reading the text.Part can have more than one image, in order to add multiple images to the part. Images of parts must be in the same directory where the Excel file is present on disk. All images which are referred in the Excel sheet will automatically be imported.
Text with different languages
Note! The field persistent-id is very important for managing duplication. If we have defined a persistent-id and the part is re-imported with the same persistent-id, the import will identify that part is already imported therefore part will be updated or replaced according to the selected option.
We can import more than one specification, where we have to define the specification type.
Specifications
Enumerable
If type EnumerableSingle is used, the different values shall be placed in the same cell and separated by "|".
E.g. a part has a specification of type EnumerableSingle with possible values A, B and C. The Excelsheet may then contain a column for this specification and the cell may then contain "A|B|C" if the specification shall contain all three possible values.
It is possible to add parts to a kit during import and thus use XLS import to create kits. A possible xls file to achieve this is according to this table.
Part number | Qty | Kit number |
|---|---|---|
123456789 | 1 | 987654 |
123456780 | 1 | 987654 |
123456781 | 2 | 987654 |
123456782 | 1 | 987653 |
123456783 | 2 | 987653 |
The configuration would then be like this.
Excel part import Add to kit
Excel part assemblies import
Same as parts config we can have multiple configures for the part assemblies import. All other features such as culture and persistent identity and specification are same as parts import. The below import.config will import parts along with part assemblies.
Part assemblies can be imported same way as parts by right clicking in the Part Assembly Storage and selecting "Import part assemblies.."
Note! The Note tag is used as information to the editor and is not included in publications. To add a remark on a part row, instead use the Remarks tag.
Excel part assembly import configuration
Note! The field persistent-id is very important for managing duplication. If we have defined a persistent-id and the part assembly is re-imported with the same persistent-id, the import will identify that part assembly is already imported therefore part assembly will be updated or replaced according to the selected option.
Specifically note that the persistent-id on the parts in the part assembly also need to be defined. This since part assembly import also will import parts.
In some cases, the need is to import part assemblies, where the part assembly only refer to parts that are already imported. In such case the part row section shall refer parts instead. This is achieved by adding the below configuration on part row section.
Excel part assembly import configuration - references to parts
Import part assembly as a list
From version 5.1 and later it is possible to import part assemblies as a list where one row is one part assembly with its attributes and specifications. The attribute assembly-list will make the config treat each row in Excel as a part assembly.
Import part assemblies as a list with level and parts
From version 5.1 it is possible to import part assemblies as a list of assemblies and parts where a level column determines if following parts belong to an assembly. Eg below table will import two assemblies with 3 parts each.
Level | Identity | Name | |
|---|---|---|---|
| 1 | 1 | 10001 | Assembly 1 |
| 2 | 2 | 20001 | Part 1 |
| 3 | 2 | 20002 | Part 2 |
| 4 | 2 | 20003 | Part 3 |
| 5 | 1 | 10002 | Assemvly 2 |
| 6 | 2 | 20001 | Part 1 |
| 7 | 2 | 20004 | Part 4 |
| 8 | 2 | 20005 | Part!-- 5 |
Excel catalogue import
It is possible to import basic properties as name, description, identity, persistent identity and specifications on catalogues. Catalogues can be imported same way as parts by right clicking in the Catalogue Storage and selecting "Import catalogues..."
Excel document import
It is possible to import documents using Excel. Each row in Excel will then refer to one document and its files.
Excel specification import
It is possible to import specifications of different types. Specifications can be imported same way as parts by right clicking in the Specification Storage and selecting "Import specifications..."