How to import and export stock, non-stock products and services

Solution Properties

Solution ID:

240304191549187

Last Modified Date:

Mon Mar 04 19:15:49 UTC 2024

Taxonomy Path:

Case category//How do I...//processing

Author:

sageadmin

How to import and export stock, non-stock products and services

Description

Cause

Resolution

This explains how to import new records, export records, and update existing records from spreadsheets (CSV files). Learn how to

Import new product and service records
Export all product and service records
Import and update existing product and service records

Import new product and service records

Use the section to learn how to import new product and service records from a spreadsheet (CSV file)

If you want to update existing records, see Update existing products and services.

Before you begin

Ensure CSV file headings match the template exactly
Download the CSV template to verify the correct headings
Include Item Code and Description; other fields are optional
Create categories and sub-categories before importing them
Include the correct category when importing a sub-category
Activate the correct Analysis Type for Stock, Non-stock, or Service items
Use separate CSV files for products and services
Limit file size to 0.5 MB
You can import up to 10 prices

Download the CSV file template

Go to Products and Services.
Select Import items from the New Item drop-down.
Choose the item type: Stock, Non-stock, or Service.
Click the template CSV file link.

Prepare the CSV file

Keep the column headings from the template.
Replace sample data with your product or service details.

▼Non-stock product CSV file format

Ensure you include a header row in your CSV file, using the column headings shown in the Field name column below.

Column	Field name	Type	Required	Notes
A	Item Code	Alphanumeric	No	Enter a code to identify the product.
B	Description	Alphanumeric	Yes	Enter a description for the product.
C	Category	Text	No	Make sure you have already set up your categories. Leave this blank if you aren’t using categories.
D	Sub-Category	Text	No	To import a sub-category, the row must also include the correct category.
E	Sales Price 1 Name	Alphanumeric	No	To use the default name, Selling Price, leave this column blank.
F	Sales Price 1 Value	Numeric plus 2 decimal places	No	Enter the price that you charge for the product.
G	Sales Price 1 Includes Tax	Alpha	No	Enter Yes or No. Leave this column blank to use the default value, which is No.
H	Sales Ledger Account	Numeric	No	Enter the ledger account code to use for the sale of this product. To use the default account, leave this blank.
I	Sales Tax Rate	Alphanumeric	Yes	Enter the sales tax rate name as it appears on the Sales Tax Rates page, accessed by going to Settings, Sales Taxes.
J	Usual Vendor	Text	No	Enter the vendor's account code. The account must already exist. Maximum 10 characters.
K	Expense Description	Text	No	Maximum 250 characters.
L	Cost Price	Number plus 2 decimal places	No	Enter a cost price for the stock item If this is blank, it's set to 0.00.
M	Expense Ledger Account	Numeric	No	Enter the account code to use for the purchase of this product, for example, 5000. To use the default account, leave this column blank.
N - P	Analysis Type 1, 2, 3	Text	No	The system includes these fields in the file only if you set up analysis types on the Settings, Analysis Types page.
Q	Notes	Alphanumeric	No	Enter a note if applicable. You can enter up to 500 characters.
R	Active	Text	No	To mark the as Active, leave this blank. To mark the customer as Inactive, enter No.
S	Sales Price 2 Name	Alphanumeric	No	To use the default name, Selling Price, leave this column blank.
T	Sales Price 2 Value	Numeric plus 2 decimal places	No	Enter the price that you charge for the product.
U	Sales Price 2 Includes Tax	Alpha	No	Enter Yes or No. Leave this column blank to use the default value, which is No.
V	Sales Price 3 Name	Alphanumeric	No	To use the default name, Selling Price, leave this column blank.
W	Sales Price 3 Value	Numeric plus 2 decimal places	No	Enter the price that you charge for the product.
X	Sales Price 3 Includes Tax	Alpha	No	Enter Yes or No. Leave this column blank to use the default value, which is No.

▼Service CSV file format

Ensure you include a header row in your CSV file, using the column headings shown in the Field name column below.

Column	Field name	Type	Required	Notes
A	Item Code	Alphanumeric	No	Enter a code to identify the service.
B	Description	Alphanumeric	Yes	Enter a description for the service.
C	Category	Text	No	Make sure you have already set up your categories. Leave this blank if you aren’t using categories.
D	Sub-Category	Text	No	To import a sub-category, the row must also include the correct category.
E	Rate 1 Name	Alphanumeric	No	Enter the name of the rate that you charge for the service. To use the default name, Rate, leave this blank.
F	Rate 1 Value	Numeric plus 2 decimal places	No	Enter the rate that you charge for the service.
G	Rate 1 Includes Tax	Alphanumeric	No	Enter Yes or No. Leave this column blank to use the default value, which is No.
H	Sales Ledger Account	Numeric	No	The account code you want to use for the sale of this service, for example, 4000. To use the default account, leave this column blank.
I	Sales Tax Rate	Alphanumeric	Yes	Enter the sales tax rate name as it appears on the Sales Tax Rates page, accessed from Settings, Sales Taxes.
J	Usual Vendor	Text	No	Enter the vendor's account code. The account must already exist. Maximum 10 characters.
K	Expense Description	Text	No	Maximum 250 characters.
L	Cost Price	Number plus 2 decimal places	No	Enter a cost price for the stock item. The default is 0.00.
M	Expense Ledger Account	Numeric	No	Enter the account code to use for the purchase of this product, for example, 5000. To use the default account, leave this column blank.
N-P	Analysis Type 1, 2, 3	Text	No	The system includes these fields in the file only if you set up analysis types on the Settings, Analysis Types page.
Q	Notes	Alphanumeric	No	Enter a note if applicable. You can enter up to 500 characters.
R	Active	Text	No	To mark the as Active, leave this blank. To mark the customer as Inactive, enter No.

▼Stock item CSV file format

Ensure to include a header row in your CSV file, using the columns shown as the Field names below.

Column	Field name	Type	Compulsory	Notes
A	Item Code	Text	Yes	Maximum 30 characters and must be unique.
B	Description	Text	Yes	Maximum 255 characters.
C	Category	Text	No	Make sure you have already set up your categories. Leave this blank if you aren’t using categories.
D	Sub-Category	Text	No	To import a sub-category, the row must also include the correct category.
E	Sales Price 1 Name	Text	No	To use the default name of Sale Price, leave this blank. If you have more than one pricing level, you can enter the additional levels after you import your records.
F	Sales Price 1 Value	Number plus 2 decimal places	No	The default is 0.00.
G	Sales Price 1 Includes Tax	Text	No	Enter Yes or No, or leave blank to default to No.
H	Sales Ledger Account	Number	No	The ledger account code you want to use for the sale of this stock item. To use the default account, leave this blank.
I	Sales Tax Rate	Text	No	To use the default tax rate, leave this blank. Alternatively, enter the rate name as it appears on the Settings, Sales Tax Rates page.
J	Usual Vendor	Text	No	Enter the vendor's account code. The account must already exist. Maximum 10 characters.
K	Expense Description	Text	No	Maximum 250 characters.
L	Cost Price	Number plus 2 decimal places	No	Enter a cost price for the stock item If this is blank, it's set to 0.00.
M	Expense Ledger Account	Number	No	The ledger account code you want to use for the purchase of this stock item. To use the default account set for your stock items, leave this blank. You can set this on the Settings, Record and Transactions Settings page.
N -P	Analysis Type 1, 2, 3	Text	No	The system includes these fields in the file only if you set up analysis types on the Settings, Analysis Types page.
Q	Notes	Text	No	Maximum 500 characters.
R	Vendor Code	Text	No	The code your vendor uses. Maximum 50 characters.
S	Quantity in Stock	Number	No	The number of items currently in stock
T	Reorder Level	Number	No	The default is 0.00.
U	Reorder Quantity	Number plus 2 decimal places	No	The default is 0.00.
V	Location	Text	No	Maximum 25 characters.
W	Active	Text	No	To mark the as Active, leave this blank. To mark the customer as Inactive, enter No.
X	Barcode	Text	No	Maximum 50 characters.
Y	Weight	Number	No	If you leave this blank, the system sets the price to 0.00.
Z	Unit of Weight	Text	No	Enter gram, kilogram, tonne, or ounce, pound, ton. If you leave this blank, it defaults to pound.
AA	Usual Vendor	Text	No	Enter the vendor's account code. The account must already exist. Maximum 10 characters.
BB	Sales Price 2 Name	Text	No	To use the default name of Sale Price, leave this blank. If you have more than one pricing level, you can enter the additional levels after you import your records.
CC	Sales Price 2 Value	Number plus 2 decimal places	No	If you leave this blank, the system sets the price to 0.00.
DD	Sales Price 2 Includes Tax	Text	No	Enter Yes or No, or leave blank to default to No.
EE	Sales Price 3 Name	Text	No	To use the default name of Sale Price, leave this blank. If you have more than one pricing level, you can enter the additional levels after you import your records.
FF	Sales Price 3 Value	Number plus 2 decimal places	No	If you leave this blank, the system sets the price to 0.00.
GG	Sales Price 3 Includes Tax	Text	No	Enter Yes or No, or leave blank to default to No.

Import your product and service records

Click Products & Services.
Click New Item, then Import Items.
Select the item type: Stock, Non-stock, or Service.
- If the upload fails, review the error message, fix the file, and try again
Upload or drag and drop your CSV file.
Click OK.

Export products and services

There are two ways to products and services

Use the option to Export all records.
Export selected records from the products and services list.

Export all records

This exports all Stock, Non-stock, or Service records to separate CSV files (excluding Analysis Types).

From Contacts, select Products and Services.
Choose Export all from the New Item drop-down.
Select the item type and click Export.
Wait for the notification that the file is ready before exporting again.

Export selected records

This exports only visible fields for selected records.

From Contacts, select Products and Services.
Select the records to export.
Click the CSV icon on the action toolbar.
Add or remove fields as needed before exporting.

Update existing products and services

To avoid duplicates, export all records first. The system uses a unique ID to identify and update records.

Export all records using the steps above.
Open the exported file, make changes, and save it.
From the navigation bar, select Products and Services.
Select Update items from the New Item drop-down.
Upload or drag and drop your updated file.
Choose how to handle empty cells, then click Continue.
Review the summary and click Start import.

Steps to duplicate