Clients are able to add their products manually on the QuickBooks iPad POS backend by navigating to the Products tab on their management console and following the steps outlined in the Products article. However, some businesses with larger product offerings (usually more than 500 products) may find the manual upload cumbersome and time-consuming. For those clients, we offer the ability of performing a mass upload using Microsoft Excel.
Exporting a Template
To export the template, log into your QuickBooks Point of Sale management console and click on the Products tab. On the left-hand bar, click on Import/Export, followed by Products. In the following screen, select Empty Template, and click Export.
This will download the product import template to your computer as an Excel file. Click on the downloaded file to open it on your computer.
Filling out a Template
Open the Excel file and fill out the spreadsheet. Note that the following columns are required:
- Product Category
- Product Subcategory
- Product Name
- SKU OR Barcode
- Active (to be set as Yes or No)
Above each column are examples of a single product with the appropriate input. Each column also has a tooltip that is revealed when mousing over them to provide additional insight about the column.
If a column is not filled out correctly, the system will reject the file.
NOTE: Columns Product Class, Product Description, and Cost are all optional. Clients also have the ability to add additional columns when exporting the template should any other settings be applicable.
Tips on Filling out the Template
A couple of things to keep in mind when filling out the spreadsheet: This information is also within the Excel file for reference.
- Do not change or create new values for “Id” column. It is internal number assigned to the product. When creating new products, leave it blank.
- Any new category/subcategory imported through the spreadsheet will be automatically populated in the backend under the Products tab > Categories.
- Category/Subcategory duplication WILL result if capitalization and spacing do not match across products.
- E.g. If your spreadsheet contains “Drinks” “DRinks” and “ Drinks” in the Category column, they will populate in the backend as three separate Categories. To ensure consistency across all cells, use the click and drag function to rapidly populate all relevant fields.
- Every product MUST have a SKU AND/OR a Barcode. These numbers may be a manufacturer’s barcode or completely arbitrary, but they must be at least 4 digits long. This number is used as a reference by the system when making any additional edits after the product is in the system. (Click and drag may also be used to assist in populating sequential numbers across several cells).
- If your products have a manufacturer’s barcode and you purchase them through a vendor, request the manufacturer’s barcodes in an Excel file from your vendor for the products. A lot of vendors usually have this information readily available, which will save you a significant amount of work when filling out the template.
- If your products have a manufacturer’s barcode, but there is no information available from your vendor, you can scan your products’ barcodes into an Excel file if you have a Bluetooth-enabled computer AND a Motorola Bluetooth Scanner. The Bluetooth scanner acts as a keyboard which, when paired with your computer, will fill out the cells with the barcodes when scanned. NOTE: this ONLY works with Motorola Bluetooth Scanners. Honeywells are not capable of being connected to your computer.
- If your products have a manufacturer’s barcode, but there is no vendor information available AND you don’t have a Bluetooth scanner, you can opt to scan and upload the products via the Create Item on Unmatched Barcode feature.
- Active (Yes or No) denotes whether the product will be displayed on the POS and active or not. If Active is set to No, the product will still upload to the management console, but will be inactive.
Importing a Template
Once the file is complete, navigate once again to the QuickBooks POS Products tab > Import/Export > Products. On the bottom of the screen, you will see an Import Products section. Click Choose File in the bottom of the page labeled Import Products, select the file from your computer, and then click Import.
The results of the import will be emailed to the email address of the employee logged in. Results can be emailed to a different address by inputting a different address in the corresponding field during the import.