Bulk import of materials
To import materials, click GLOBAL SETTINGS > Import/Export > MATERIALS tab.
Click on the Template CSV file with column headers here link and add or copy your data (in this case, Material data) into the spreadsheet. Only the required fields identified (with reqd at the end) in the template need to include data - the rest can remain blank if the information is not available. Unit Cost and Stock Level cannot be adjusted for Managed Material Items updated via this import.
Material Headings
The Material import facility provides the ability to set the following columns in the CSV file:
| Column | Comments |
|---|---|
| Code | A material (or 'stock') code for your item (optional - leaving blank will cause an auto generated code). Up to 25 characters made up of - alphanumeric characters. Uppercase letters and numbers - optional. Special characters.... "-" (hyphen), "_" (underscore), "." (dot/period), or "/" or "" (backward or forward slashes) - must not contain or have leading or trailing spaces |
| Material Name | Required. |
| Material Category | Required. The full Material Category name as it appears in the Material Category tab under the Materials menu. Also known as Material Type. |
| Description | Up to 256 characters, optional |
| Suggested Supplier | The full Supplier Name as it appears in the Suppliers list in TidyStock. Can be blank. |
| Usage Unit Pricing Unit | Required. Valid values are: ITEM, m, cm, mm, m2, cm2, mm2, m3, cm3, mm3 |
| Cost Currency Charge Currency | Required. The 3-letter ISO code for a currency that has already been added to the list of Currencies in TidyStock |
| Unit Cost Unit Charge | Must be a valid decimal value. Invalid values will not be loaded. Can be blank. |
| Reference | Optional |
| Managed | Must Be Y or N |
| Stock Level | Can be changed for unmanaged items. Cannot be changed for managed items. |
| Location | Must be one of the locations already configured. This is the default location for inbound stock. Cannot transfer stock between locations by changing this name |
| SKU | Optional. May be blank |
| Barcode | Optional. Barcode of material item |
| Catalogue Price | Required. |
| Catalogue Price Currency | The 3-letter ISO code for a currency that has already been added to the list of Currencies in TidyStock |
After you complete and save the spreadsheet, click Choose File and browse to the template file you have created. At this point, you will have the option to Import the file:
If there are errors in file format or data, an error message will be displayed. Data errors will be displayed by spreadsheet row number. Typical errors are:
- Blank reqd data
- Duplicate item codes and / or descriptions
Once corrections have been made, repeat the Import. When finished, you will be notified that the import was a success. You will be able to view the materials in GLOBAL SETTINGS > Materials > MATERIAL ITEMS. To export materials, click GLOBAL SETTINGS > BULK DATA IMPORT/EXPORT > Export Material Records.
Note:
- If any data has leading zeroes, such as material codes, do not open the exported csv file directly in Excel, as it will strip the zeroes, thinking they are numbers. Instead, import as a text file into Excel, and specify the relevant column as text.
- Unmanaged items may be converted to managed items once, by an upload. Specify Y in the managed column
- Managed items cannot be converted to unmanaged, either manually or via upload
- If unmanaged items are converted to managed, the value of these items is added to the stock on hand account in Xero. Take care to reconcile and adjust the stock on hand account to true value, avoiding duplications. Example SOH account = $50k, the value of unmanaged stock manually journaled by periodic stocktakes. Upload and convert to managed stock. New SOH value = $50k +$50k. Credit SOH by $50k, so it reduces to $50k
- Good practice, if uploading many items, is to upload in subsets, such as category by category, or sets of 50 at a time. This way its easier to reconcile and control.