How to Prepare a Product List in Excel or CSV Before Importing It Into a POS
A practical checklist for cleaning a shop's product spreadsheet before a POS import: columns to prepare, barcodes, units, prices, duplicates, a ten-row test batch and free tools for each step.
Contents
Most POS imports fail for dull reasons. The same product appears three times with slightly different names, barcodes lose their leading zeros, prices contain currency symbols, and "Pack", "pack" and "PKT" become three different units. A POS will usually accept the file anyway, and then you spend the first month fixing stock counts at the counter. Cleaning the spreadsheet first takes an hour for a few hundred items and saves a lot of confusion later.
Step 1: Decide which columns the import needs
Open your current product list and look at the header row. Most retail POS importers want a small set of fields: product name, barcode or SKU, category, unit of sale, cost price, sale price, tax rate if you charge one, and opening stock. Check the import template of the system you chose and match its column names exactly. Drop the extra columns you will not map, because stray columns are the most common reason an importer rejects a file or shifts data into the wrong field.
Step 2: Remove duplicate products
Lists built over several years usually contain repeats. Sort the file by product name and look for near-duplicates. For a quick pass on a plain list of names, paste the column into the Duplicate Lines Remover to strip exact repeats. Exact matching only catches identical text, so normalise the names first (next step), then run it again to catch the ones that now match.
Step 3: Normalise names, brands and units
Pick one style and apply it to every row. A simple convention is title case for product names and one fixed spelling for each unit: for example box, strip, piece, kg, gram, dozen. The Text Case Converter and the Case Converter will fix a pasted column in one step. Trim leading and trailing spaces at the same time. If the shop sells in more than one unit, such as a box and a single strip from that box, decide how your POS handles pack sizes before you import. Some systems keep one product with several units, and others expect separate products.
Step 4: Protect barcodes and generate missing ones
Barcodes are numbers that must stay text. Spreadsheet programs often turn a long barcode into scientific notation or drop a leading zero when you save, and the scanner then reads nothing at the counter. Format the barcode column as text before you paste or type values, and spot-check a few rows after saving the CSV. For loose items that never had a barcode, give each one a short internal code and print labels. The Barcode Generator creates a barcode image for a code you provide, which you can print on a label sheet.
Step 5: Make prices and stock plain numbers
Remove currency symbols, thousands separators and words from the price and stock columns. "Rs 1,250" should become 1250. Keep cost price and sale price in separate columns so the POS can calculate margin. If you are unsure how much to raise a price, the Percentage Increase Calculator and the Percentage Calculator are handy for checking a new price list before you load it. Count opening stock on the shelf, not from the old system, because the first import sets the starting point for every report that follows.
Step 6: Save as CSV with the right encoding
If product names include Urdu or other non-English text, save the file as CSV UTF-8 so the characters survive. Excel offers this as a separate option in the Save As menu. Open the saved file in a plain text editor or a browser-based viewer to confirm the text still looks right before you upload it.
Some systems and automation tools expect JSON rather than CSV. If yours does, the CSV to JSON converter turns a cleaned sheet into JSON, and JSON to CSV goes the other way when you want to review an export in a spreadsheet. UrwaTools has a similar walkthrough for another kind of file in its guide to cleaning a lead list before a CRM import, and most of its steps apply here too.
Step 7: Import ten products first
Before loading the whole catalogue, import ten rows. Open each one in the POS and check the name, barcode, unit, prices and stock. Scan two or three barcodes with a real scanner. Ring up a test sale and a test return, then check that stock went down and up by the right amount. If anything is wrong, fix the source file and re-import the test batch instead of editing inside the POS, so your spreadsheet stays the single clean copy.
Step 8: Keep the original and a change log
Save the untouched export, save the cleaned file under a new name, and write down what you changed: duplicates removed, units renamed, prices corrected. When a customer disputes a price in three months, you will be able to trace where it came from.
Choosing the POS that receives the data
A tidy file only helps if the POS fits your shop. Ask each vendor whether it imports products from Excel or CSV, whether it supports your units and barcodes, and whether it handles the tax invoicing your business needs. As one example, ProductListo's directory entry for HisaabPOS says products and customers can be imported from an Excel file. The directory's POS buying guide compares price, trial length, offline billing and platforms, and its roundup of the best POS software for retail shops in Pakistan walks through the main options for a general store, clothing shop, mobile shop or pharmacy counter.
Once the system is running, a QR code on the receipt or shelf label can point customers to a price list or a payment page. UrwaTools has a guide to QR codes for business, and a free Invoice Generator is useful for the occasional bill outside the POS. Makers of POS and inventory software can submit a tool to the ProductListo directory or read the write for us page about contributing.
Quick checklist
- Match the importer's column names and drop extra columns.
- Normalise names and units, then remove duplicates.
- Keep barcodes as text and label items that have none.
- Use plain numbers for prices and stock.
- Save as CSV UTF-8 if names include Urdu.
- Import ten products and test a sale and a return before the full file.
- Keep the original export and a short change log.
Frequently asked questions
What columns does a POS product import usually need?
Why do barcodes lose their leading zeros in Excel?
How do I save a CSV that keeps Urdu product names?
How many products should I test before a full POS import?
Do I need to convert my product list to JSON?
The UrwaTools Editorial Team delivers clear, practical, and trustworthy content designed to help users solve problems efficiently. Our articles focus on accurac...