Created with Sketch. Visit the QuickBooks Community to find answers and meet other small business owners just like you!

Created with Sketch.

Showing results for 
Search instead for 
Did you mean: 

QuickBooks Desktop Point of Sale: Import and Export overview

When making collective changes in the data within your company file, you can import and export information in your file using MS Excel. This is a more efficient way of updating your data instead of doing it manually inside the software. In order to do this successfully, one must follow templates and formats to prevent errors when updating or transferring a data file.

The Import process

Importing data through excel requires the use of the Default Template exported from POS. You can import up to 65,000 rows at a time.

You can also convert .csv files to excel files. Just open the .csv file then go to File menu > Save as, then choose Excel Workbook in Save as type dropdown.

Excel data format

For a smooth import using excel, take note of the following:

  • There should be no dashes in empty excel fields
  • Remove empty rows and columns
  • Delete hidden columns
  • Don't use formulas, hyperlinks, and special characters i.e.: @,#,$,%,&,*
  • Use “clear contents” instead of deleting info separately

If you have difficulty importing specific columns from your spreadsheet, verify that the column cells are formatted correctly to work with the Data Import wizard

Column Name MS Excel Cell Format
Item Number General
Item Name General - 32 character max
Department Name General
Item Description General
Brief Description General
Alternate Lookup General
Attribute General
Size General
Average Unit Cost Number - 2 decimal places
Regular Price Number - 2 decimal places
MSRP Number - 2 decimal places
Custom Price 1- 4 Number - 2 decimal places
Tax Code General
UPC Custom - 0000000000000
Order Cost Number - 2 decimal places
Item Type General
Base Unit of Measure Text
Company Reorder Point Number
Income Account General
Column Name MS Excel Cell Format
COGS Account General
Print Tags Text
Unorderable Text
Serial Tracking Text - 25 character max
Eligible for Commission Text
Vendor Name General
Vendor Code General
Manufacturer General
Weight Number - 2 decimal places
Length Number - 2 decimal places
Width Number - 2 decimal places
Qty 1 - 3 Text
Custom Field 1 - 5 General
Eligible for Rewards General
Sync to Mobile General
Vendor Name 2 - 5 General
UPC 2- 5 General
Order Cost 2 - 5 General
Reorder Point 1 - 3 General

Tips when navigating the spreadsheet:

  • Freeze the headers of the spreadsheet to scroll down while viewing headers
  • Use Page Up and Page Down keys instead of scrolling with mouse
  • Ctrl+End: page down to the bottom of the excel data set
  • Ctrl+Home: go back to the beginning of the excel sheet data

Import data

Before data import:

  • Create a backup copy of your file
  • Do a quick scan of the entire worksheet before mapping. Look for anomalies, strange data, etc.
    • Open .csv spreadsheet in notepad to see irregularities
    • Copy then paste any file that gives you trouble into a fresh worksheets using paste special > values
  • Break large lists into smaller sections (worksheets)

Follow these steps to import your data.

Step 1: Get your template

Note: If you do not have an existing template available for import, you may need to download a new template file or recreate from scratch.

  1. Close QuickBooks Desktop Point of Sale.
  2. Right-click the Windows Logo button and select File Explorer.
  3. Navigate in the left window to the location: C:\Users\Public\Public Documents\Intuit\QuickBooks Point of Sale (x.0).
    To navigate, select the arrow symbol next to each folder listed.
  4. Right-click QB POS Import Template.xls and select Delete. Confirm the deletion.

Step 2: Set up and import data to Point of Sale

  1. In QuickBooks Desktop Point of Sale, go to File > Utilities > Import.
  2. From the Data Import Wizard, select Next.
  3. Select the type of data to import then Next.
  4. Select Default template on how you want to import the data and open the Import Template.

    If you chose to have a different file template for import:

    1. Select Custom file then Next.
    2. Locate your saved template. Ensure to select the correct worksheet tab and what row the data started.
    3. To match the columns from the import template to the Point of sale fields, you may need to map the data manually. Select Choose File Mapping > Add or Select Mapping > Manage Mappings.
    4. After mapping is completed, select Save.
    5. Select Next and follow the prompt to start the import.
  5. Select Next and follow the prompt.
  6. After the import wizard checked the import file, select Import.
  7. Close  the wizard.
    Note: To check for any errors, go to File > Utilities > View Data Import Log.

The Export process

Exporting data is often used to save a copy of your file in your computer using another recognized application and do changes especially when you need to create a new file, transfer information, or upgrade. You may need to create a backup copy of the file before following the steps to export.

You can export the following lists from QuickBooks Desktop Point of Sale to Excel:

  • Inventory Items
  • Customers
  • Vendors
  • Departments
  • Employees

Export data

  1. In QuickBooks Desktop Point of Sale, go to File > Utilities > Export.
  2. From the Data Export Wizard, select Inventory Items then Next.
  3. Enter the needed data in the following fields:
    Select destination file - location where file will be saved
    Write records to (sheet) - worksheet  where data must be entered
    Begin exporting data to row - row where data should be started
  4. From the Choose export template, you may select the default inventory template or manually manage the mapping to match the details to be exported from QuickBooks Desktop Point of Sale.
  5. Select Next.
  6. Validate if the records ready for export is correct, then select Export.
Note: Performing mass update of Reorder Quantity for a remote store can only be done manually. Export and import of this data is currently not available. Want this feature in our product update? Tell our engineers! Go to Help menu > Send feedback and request for this functionality to be added.

Was this helpful?

You must sign in to vote, reply, or post

Need to get in touch?

Contact us