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
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
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.
- Close QuickBooks Desktop Point of Sale.
- Right-click the Windows Logo button and select File Explorer.
- 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.
- Right-click QB POS Import Template.xls and select Delete. Confirm the deletion.
Step 2: Set up and import data to Point of Sale
- In QuickBooks Desktop Point of Sale, go to File > Utilities > Import.
- From the Data Import Wizard, select Next.
- Select the type of data to import then Next.
- 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:
- Select Custom file then Next.
- Locate your saved template. Ensure to select the correct worksheet tab and what row the data started.
- 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.
- After mapping is completed, select Save.
- Select Next and follow the prompt to start the import.
- Select Next and follow the prompt.
- After the import wizard checked the import file, select Import.
- 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
- In QuickBooks Desktop Point of Sale, go to File > Utilities > Export.
- From the Data Export Wizard, select Inventory Items then Next.
- 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
- 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.
- Select Next.
- 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.