QuickBooks POS adding numbers to the UPC when imported from excel
Why does QuickBooks POS add on a number to the UPC when I import from excel? It makes it so the item cannot be found in the sale screen.
I just spent an entire day with a client on this one. Quickbooks POS recognizes only EAN-13. If you are importing anything other than EAN-13, POS will automatically convert the number into EAN-13 format by adding leading zeros to a UPC-A or leading zeroes and a trailing check digit to UPC-E.
We had to configure an Orbit 7120 scanner to read leading and trailing digits in the quiet zone (the digits outside of the barcode itself). UPC-Es scanned without problem, UPC-As did not scan at all. Because our import file was exported from Argis (who reads barcodes as Code 39), our only option was to physically scan each UPC-A into the system. Our only saving grace was that by scanning the item’s UPC into the Inventory Item search field, it matched the imported UPC enough to locate it without problems. We then scanned the UPC in the UPC field, saved, and tested with success.
POS only recognizes 12 or 13 digit UPC codes.
You must manipulate your upload file to make 12 or 13 digit strings.
- Count the digits (characters) in the UPC code string. In Excel use formula =len(UPC_code) where UPC_code is the cell address where the UPC code is.
- Copy formula downward
- Add zeroes to the front. In Excel use this magic formula:
=IF(DY2>=12,(TEXT(O2,”#”)), (IF(DY2=11,CONCATENATE(“0″,”0”,O2),(IF(DY2=10,CONCATENATE(“0″,”0″,”0”,O2),(IF(DY2=9,CONCATENATE(“0″,”0″,”0″,”0”,O2),0)))))))
where DY2 is the cell address where you put the LEN formula. - Copy formula downward
- Copy “as values” the new UPC codes from the “calculated” colum to the original column.
Good luck!