QuickBooks HelpQuickBooksHelpIntuit

QuickBooks Desktop Point of Sale: Import and Export overview

SOLVEDby QuickBooks4Updated 1 year ago

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 NameMS Excel Cell Format
Item NumberGeneral
Item NameGeneral - 32 character max
Department NameGeneral
Item DescriptionGeneral
Brief DescriptionGeneral
Alternate LookupGeneral
Average Unit CostNumber - 2 decimal places
Regular PriceNumber - 2 decimal places
MSRPNumber - 2 decimal places
Custom Price 1- 4Number - 2 decimal places
Tax CodeGeneral
UPCCustom - 0000000000000
Order CostNumber - 2 decimal places
Item TypeGeneral
Base Unit of MeasureText
Company Reorder PointNumber
Income AccountGeneral
Column NameMS Excel Cell Format
COGS AccountGeneral
Print TagsText
Serial TrackingText - 25 character max
Eligible for CommissionText
Vendor NameGeneral
Vendor CodeGeneral
WeightNumber - 2 decimal places
LengthNumber - 2 decimal places
WidthNumber - 2 decimal places
Qty 1 - 3Text
Custom Field 1 - 5General
Eligible for RewardsGeneral
Sync to MobileGeneral
Vendor Name 2 - 5General
UPC 2- 5General
Order Cost 2 - 5General
Reorder Point 1 - 3General

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
QuickBooks Point of Sale BasicQuickBooks Point of Sale Multi-StoreQuickBooks Point of Sale Pro

Sign in for the best experience

Ask questions, get answers, and join our large community of QuickBooks users.

More like this