CSV Imports & Preparing Data

The HirePOS CSV Uploaders

HirePOS features CSV importers in several key areas to speed up both initial data entry general administrative tasks.

The key importers for getting a new database up and running, and updating it in the future include:

  • Items
    • (Setup > Items > Options > Import Items CSV)
  • Item Prices
    • (Setup > Items > Options > Import Item Prices CSV)
  • Item Serials
    • (Setup > Items > Options > Import Item Serials CSV)
  • Customers
    • (Setup > Customers > Options > Import Customers CSV)
  • Customer Contacts
    • (Setup > Customers > Options > Import Customer Contacts CSV)
  • Suppliers
    • (Setup > Suppliers > Options > Import CSV File)

In addition, there are several extra uploaders that aid some specific tools in HirePOS, such as:

  • Sales Stocktake
    • (Setup > Item Related > Sales Stocktake > Import CSV)
  • Bank Feeds / Bank Reconciliation - Accounting / Bookkeeping Module
    • (Banking > Bank Reconciliation > (select account) > Import CSV)
  • Supplier Price List - Allows a full list of supplier items to be stored and used on purchase orders, without appearing in your inventory until meaningful stock is held.
    • (Setup > Items > Options > Import Supplier Pricelist CSV)
  • Contracts
    • Setup > Preferences > Sales > Options > Import Contracts CSV.

CSV Upload Guidelines

The CSV uploaders in HirePOS are very intuitive and easy to use, however, there are a couple of points specific to CSVs in general that must be observed:

  • No commas (,) can be present in ANY field in your spreadsheet.
    • This is how a CSV tells the computer to move to the next column, if present, these will skew your data significantly
  • No quotation marks (' ")(double or single) can be present in ANY field in your spreadsheet.
    • This is how a CSV tells the computer to ignore commas, if present, these will skew your data significantly
  • No line breaks (return characters, multi-line cells) can be present in ANY field in your spreadsheet. 
    • This is how a CSV tell the computer to move to the next row if present these will skew your data significantly
  • Avoid "special" or "unusual" characters.
    • Try to keep all data within the standard character set: A-Za-z0-9!*();:=+?#_.-
  • As a general rule of thumb, keep each CSV file to a maximum of 5,000 rows.
Dates and Times

All dates and times should be recorded in your CSV using International (serialised) date format: yyyy-MM-dd

This means, where you might expect 23/08/2021, you should record 2021-08-23

This can be easily achieves in Excel or Google sheets by selecting the date fields on the sheet, selecting custom data format, and entering yyyy-MM-dd . See images below.

Conditioning your Data

Despite your best efforts, sometimes a spare comma or line break will remain buried somewhere in your spreadsheet. Microsoft Excel has all of the tools required to help you clean up and condition your data, however not everyone has access to Excel.

Google offers a FREE spreadsheet editor called Google Sheets that you can easily use to condition your data before upload.

Load up your spreadsheet in Google Sheets, and perform the following:

Strip all formatting

  • Click the grey rectangle in the top left corner of the spreadsheet, or press (Ctrl+A) to select everything
  • With everything selected navigate to "Format" in the main menu, and select "Clear All Formatting" (Ctrl+\)
  • With everything still selected, Navigate through the main menu and select "Format > Number > Plain Text"

Remove unwanted characters

Navigate to "Edit" in the main menu, and select "Find and replace" (Ctrl+H)

Find any commas and remove them
  • Find: ","
  • Replace with: (empty)
    • or alternatively, you might like to use a hyphen instead
  • Press "Replace all"
Find any single or double quotation marks and remove them
  • Find: ","
  • Replace with: (empty)
    • you might alternatively want to replace these with the word foot or inch, depending in why they were there to start with
  • Press "Replace all"
Find any line breaks and remove them
  • Find: /n
  • Replace with: (empty)
  • Turn on "Search using regular expressions"
  • Press "Replace all"
Remove currency signs from prices

(only applicable for Items, Item PricesBank Feeds, and Supplier Price List)

You do not need to include any of these symbols in your price uploads. Just simple '12.34' decimals are all that is needed.

  • Find: "$"
  • Replace with: (empty)
  • Turn off "Search using regular expressions"
  • Press "Replace all"
Cleanup and Final Verification
  • Visually inspect data and be sure you are satisfied with the content.
  • Remove any columns you will not need - this will simplify the import process.
  • Ensure the first row of your spreadsheet has meaningful header names in it.
  • Export
    • When ready, navigate to File > Download > "Comma-separated values (.csv, current sheet)"
    • Save the file to your computer, ready for upload to HirePOS.

The Import Process

All of the importers work in the same basic fashion.

  • Simply navigate to the uploader
  • Select the desired CSV file from your computer
  • Press "Preview Data"
  • Match up the CSV column headers with their destinations in HirePOS.
    • If you have used one of the templates below, the column headers will auto-match, saving you time and effort. 
  • When satisfied that all columns have been mapped to their correct destination, press "Import Data"

Certain uploaders have specific settings, for example:

  • When importing new items, you must select the "Item Type" that all of the items in your CSV will be set to.
  • When importing item prices, you need to define if you are importing your prices including or excluding tax, and need to define the default tax code you will be using for them.

For more information on specific CSV uploaders, please see the related help doc. 

Blank CSV Templates

To make your data migration simpler, you may start with one of our blank templates, which have all available headers in them ready to go. These will auto-match when importing, further simplifying the process.

Items CSV Template

Item Serials CSV Template

Customers CSV Template

Customer Contacts CSV Template

Suppliers CSV Template

Supplier Pricelist CSV Template

Contracts CSV Template

Did this help?

Powered by HelpDocs (opens in a new tab)