In this article, learn how to import items from a CSV file
Applicable Subscription Levels:
QC MANAGER | STARTER | PRO | PRO+ | ENTERPRISE
Overview
Using the Item Import feature is a fast an easy way to create new Items or update existing Item Details in bulk. This article explains how to create the import file correctly so there are no errors during the import.
Creating Item Import Files
The Item import file must be in the comma separated value (CSV) format. This format separates each value with a comma. The easiest way to make this file is to use a spreadsheet program like Microsoft Excel. When it is time to save the file, use Save As, and select the CSV file format.
Anatomy of an Item Import File
The Item import file has two main sections.
- Item Definition - This is where you identify the Item to be created or updated.
- Item Details - This is where you define the Custom Property values for the Item
Above is an example of an Item import spreadsheet.
The first 3 columns (A, B, & C) make up the Item Definition section. Together, these items identify the item to be created or updated.
The first row of the file should include the column headers. The first three column headers (shown in Red Text) should appear in your spreadsheet exactly as pictured above. In the same order and with the same text. The second row would include the information to go along with each header item. Each corresponding value should be directly under it's header. Each new row represents a different item being imported.
- Every row in the spreadsheet must have a Serial Number value. Values in the Serial Number column are case sensitive.
- Project Number is optional.
- Every row in the spreadsheet must have an Item Type Number value. This value is not case sensitive.
Column 4 (D) begins the Item Details section. In column D, and each subsequent column, enter column headers for each additional value you would like to import along with the item. You can include header labels for any defined Custom Properties for the Item Type being imported.
- Custom Property columns can be in any order
- You do not need to include a column for every Custom Property, only the ones you wish to import or update.
- When using Custom Properties, make sure you spell the Property Name exactly the same as it is in the Custom Property definition.
- Including a Custom Property column that is not valid for the Item Type will cause an import error.
Import Sheet Column Information
| Column | Header | Required | Description |
| Column 1 (A) | Serial Number | Always | The Item Type Number |
| Column 2 (B) | Project Number | Optional | The project Number |
| Column 3 (C) | Item Type Number | Required |
The Item Type Number If a project Number was specified, this Item Type must exist within the project |
| Column 4 and above (D and above) | Custom Property Name | Optional | Custom Property values you want to import with each Item Type |
|
Valid Data Type formats for Custom Properties |
|||
|
Property Type |
Data Type |
Description |
|
|
Date |
dateTime
|
This data type is used for values that contain both date and time parts. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. NOTE: Dates must be entered in UTC time. The system will convert the entered datetime into the appropriate time zone when displayed to the user via the Management Portal or Mobile application. UTC stands for Coordinated Universal Time, a standard used to set all time zones around the world. Each time zone is "offset" from UTC time, either forward or backward. Eastern Standard Time, for example, is 5 hours behind Coordinated Universal Time. UTC offset for EST is therefore minus 5 (-5). Eastern Daylight Time is 4 hours behind UTC, so offset is -4. When entering datetime values, be sure to use the UTC equivalent of your local time. Examples: I want to import a Pour Date & Time
You can easily find your UTC offset using Google. Google search "EST UTC Offset" will return -5. Google search "PDT UTC Offset" will return -7. Supported formats:
|
|
|
Numeric |
float |
A number that is not an integer, because it includes a fraction represented in decimal format Decimals are not required, but accepted Supported formats:
|
|
|
Text |
nvarchar |
Unicode character data, up to 32767 characters NOTE: The longer your text, the more the system will need to wrap the text to fit the space available for display. Please consider the length of your text when setting text values. |
|
Though it is most common to create a spreadsheet of new items to be created OR a spreadsheet of items to be updated, you may include both new items and existing items in the same spreadsheet. When the file is imported, the application will look at each row in the spreadsheet and attempt to find an existing item with a matching Serial Number. If one is found, that item's project, item type, and custom properties will be updated with the values in the .csv file. If no matching serial number is found, a new item will be created.
When updating an existing item, any columns left blank in your spreadsheet will be skipped. For example:
Existing Item in Your System
- Serial Number: 1C5620IP98
- Project Number: 7
- Item Type Number: PC56
- Diameter of Pipe: 36"
- Concrete Mix ID: 7B34RT9
- Cast Date: 03/23/2017
Import Spreadsheet
| Serial Number | Project Number | Item Type Number | Diameter of Pipe | Concrete Mix ID | Cast Date |
| 1C5620IP98 | 9 | PC97 | 7B356PL | ||
| 1C5620IP99 | PC56 | 3/26/2017 |
In the example above the first row in the spreadsheet will cause the existing item to be updated with a new Project Number, Item Type Number, and Concrete Mix ID. No other information about the item will be updated (the blank Cast Date will not overwrite the existing Cast Date value). For the second row, if no item is found with a matching Serial Number, a new stock item will be created. The Diameter of Pipe and Concrete Mix ID values will be left blank.
When a new item is created, any default process associated with the Item Type will be added.
Importing the File
Select Items from the Main Menu. The Items list will appear.
Click the Import button in the upper right-hand corner.
Click the Browse button, select the file on your computer, and click Next.
As an initial validation, the application will scan the file, count, and then display the number of rows in your spreadsheet that represent items to be updated. If the count is correct, click Import. If the number is incorrect, click the Back button.
Below is an Excel Template you can use to create an Item Import file using these instructions. Be sure to replace or remove the sample column headers and the sample data.