The Stock Import Tool allows you to create and update stock lines from a spreadsheet. This is especially useful when adding new stock from suppliers.
We've attached a sample excel spreadsheet that we will refer to in the article.
Ideally, please run a backup before making any major changes to your stock e.g. with the stock import tool or with a bulk edit.
Importing Stock Using the Stock Import Tool
The Stock Import Tool can be used to help you load in stock data provided by the supplier in the form of an excel spreadsheet. The purpose of the tool is to point Z office to the columns where certain data can be found e.g. PDE data can be be found in column 2 (or letter B).
To load data into the stock import tool:
- Go to Z Office > Stock > Load Stock Import File:
- In the Stock Import Tool, locate and select the spreadsheet you would like to use to load in your stock details:
- In the new window, a sample of your spreadsheet will load allowing you to view the information which will be imported:
A. Item Start Row - specify the row number where the first line of stock appears
B. Match Type - choose how to match to existing stock using combinations of PDE and Barcode
C. Supplier (Optional) - identify which supplier new stock cards will be created under
D. Template Name (Optional) - give the template a new if you wish to re-use it
E. Columns Details - use the drop down boxes to select the data found in each column - Press Done to load in the the data:
You will be able to see the information that the system will try to load in. You should correct any discrepancies before proceeding. - Select the options you would like the tool to complete when loading in the data:
A. Tick Create Stock from New Lines option if you would to create new stock lines for lines in the spreadsheet that do not exist. Otherwise, only existing stock cards will be updated with new information.
B. Tick Update Stock Name option if you would like to update matching lines to the stock names found in the spreadsheet.
C. You can select or create a stock group to add the stock lines to. - The Stock Import Results will tell you the number of lines that will be created (
), how many will be updated (
) and how many will be ignored (
).
Any lines with duplicate matching or conflicting information (e.g. duplicate PDE/APN data already in the database) will be flagged with a yellow warning sign and remain unaltered if you proceed. - Press OK when you are ready to import the data.
- A prompt will appear notifying you of the results of the import:
Checking Created Stock Lines
To check the stock you have created from the Supplier file:
- Go to Stock > Manage Stock
- Under Advanced Filter on the left, set stock status to ALL Stock and go to the Range filter and select Date Created. Then set op as in and the Values as the date you created the stock using the Stock Import Tool:
- Press Apply to show the newly created stock lines and edit them if needed.
- If you have placed the stock into a stock group when importing the file, go to Stock Groups and select the created stock group:
You will need clear or include inactive lines in the stock status filter for newly created lines. - Press Apply to show the stock group.
Manually Creating a Stock Import Template
Alternatively, if you are more familiar with our older method of importing stock, you also have the option to create a stock important template manually.
To create a stock import template:
- Go to Z Office > Tools > Stock Import Template > Create New
- The Stock Import Template window will appear:
A. Input a name for the template
B. Choose the Supplier that you're importing the stock/stock details for.
C. The Match Type refers to how you would like to match the stock on the spreadsheet with the stock in the system. Choose to match stock by using PDE, Barcode (APN) or both.
D. Item Start Row refers to which row in the spreadsheet the stock information starts (excluding headings). From the example spreadsheet, it would be Row 3 and you would enter 3 in this field. - Under Column details, match up the required column fields with those in the excel sheet. Using the example spreadsheet, the PDE is in column A of the spreadsheet so you would put '1' in PDE field on the stock import template.
If you wish to create new stock lines, the Stock Name field on the template must be filled with the relevant column from the excel. If the stock in the file does not have a stock name, it will not be added.
For more information about each column detail field, please refer to the Syntax for each field. - Continue matching the excel columns to the fields in the template. This is an example of the stock import template for the example spreadsheet:
For fields that you do not have information in your spreadsheet, please leave them blank. - Press Save.
Syntax for each field
Field |
Source |
Field Type |
Default Value |
Notes |
Stock Name |
Spreadsheet |
Alphanumeric |
Nothing |
Items that have been matched (i.e. update), by default will not update their stock name to the excel sheet unless elected to do so in the stock import tool Items that haven’t been matched (i.e. create) will require the stock name to be selected. If the column isn’t selected or no value in the excel sheet, it won’t create the line |
Supplier |
Selection List on Template |
List Item |
Nothing |
Template should always have a supplier selected |
Department |
Spreadsheet |
Alphanumeric |
Nothing |
Match input by name to existing Department in Z Office and assign the matched Department to the Stock, if no Department is matched then ignore it |
Sub-Department |
Spreadsheet |
Alphanumeric |
Nothing |
Match input by name to existing Sub-Department for the matched Department in Z Office and assign the matched Sub-Department to the Stock, if no Department or Sub-Department is matched then ignore it |
Manufacturer |
Spreadsheet |
Alphanumeric |
Nothing |
Match input by name to existing Manufacturer in Z Office and assign the matched Manufacturer to the Stock, if no Department is matched then ignore it |
Locations |
Spreadsheet |
Alphanumeric |
Nothing |
Match input by name to existing Location in Z Office and assign the matched Location to the Stock, if no Department is matched then ignore it |
Categories |
Spreadsheet |
Alphanumeric |
Nothing |
Match input by name to existing Categories in Z Office and assign the matched Categories to the Stock, if no Categories is matched then ignore it |
Pricing Policy |
Spreadsheet |
Alphanumeric |
Nothing |
Match input by name to an existing Pricing Policy in Z Office and assign the matched Pricing Policy to the Stock, if no Pricing Policy is matched then ignore it |
Rounding Policy |
Spreadsheet |
Alphanumeric |
Nothing |
Match input by name to an existing Rounding Policy in Z Office and assign the matched Rounding Policy to the Stock, if no Rounding Policy is matched then ignore it |
Carton Size |
Spreadsheet |
Numeric |
1 |
|
SOH |
None |
Numeric |
0 |
|
Min. Stock Level |
Spreadsheet |
Numeric |
0 |
|
Min. Ord. Qty |
Spreadsheet |
Numeric |
0 |
|
Active |
Spreadsheet |
Alphanumeric |
No |
Y = Yes, N = No |
Ethical |
Spreadsheet |
Alphanumeric |
No |
Y = Yes, N = No |
WS1 Cost |
Spreadsheet |
Decimal |
Nothing |
No symbols allowed $...etc |
Cost |
Spreadsheet |
Decimal |
Nothing |
No symbols allowed $...etc |
Avg Cost |
None |
Decimal |
Same as Cost |
No symbols allowed $...etc |
Last Inv Cost |
None |
Decimal |
Same as Cost |
No symbols allowed $...etc |
GST |
Spreadsheet |
Alphanumeric (Case sensitive) |
F |
F = Free to All, N = No to Customer, Y = GST to All |
Sell Price |
Spreadsheet |
Decimal |
Nothing |
No symbols allowed $...etc |
RRP |
Spreadsheet |
Decimal |
Nothing |
|
APN |
Spreadsheet |
Alphanumeric |
Nothing |
Set APN to Default |
PDE |
Spreadsheet |
Alphanumeric |
Nothing |
Set PDE for selected Supplier to Default |
Using a saved template to complete a Stock Import
You can use the templates saved in the Stock Import tool or created manually to streamline the process of importing stock on a regular basis.
To select and use the template,
- Go to Stock > Load Stock Import File:
- Click on the three dots to select your spreadsheet containing stock data:
- Use the drop down box to select the template you have just created:
The columns should correspond to those selected when creating the template and match with the data: - Press Done to load in the the data:
You will be able to see the information that the system will try to load in. You should correct any discrepancies before proceeding. - Select the options you would like the tool to complete when loading in the data:
A. Tick Create Stock from New Lines option if you would to create new stock lines for lines in the spreadsheet that do not exist. Otherwise, only existing stock cards will be updated with new information.
B. Tick Update Stock Name option if you would like to update matching lines to the stock names found in the spreadsheet.
C. You can select or create a stock group to add the stock lines to. - The Stock Import Results will tell you the number of lines that will be created (
), how many will be updated (
) and how many will be ignored (
).
Any lines with duplicate matching or conflicting information (e.g. duplicate PDE/APN data already in the database) will be flagged with a yellow warning sign and remain unaltered if you proceed. - Press OK when you are ready to import the data.
- A prompt will appear notifying you of the results of the import:
Comments
0 comments
Please sign in to leave a comment.