Importing Products: What to Know

When importing barcodes to the barcode field and you have the ability to scan multiple barcodes per item, there are a couple items to consider. 
  1. Which barcode you scan the majority of the time (Revel has one barcode field)
  2. You must know how your application has stored the barcode number
We will use QuickBooks Point of Sale Desktop (QB POS DT) as an example.  QB POS DT has the ability to scan barcodes stored within 3 main fields; UPC, Item Number and ALU.  We will review the first 2 options as the barcode field in Revel only has the ability to accept digits.  If you are using a label format which creates a barcode with alphanumeric characters you will need to import that barcode to the SKU field within Revel and on the POS press the search button before scanning the barcode.
Scenario #1 - Scanning a barcode generated from the Item Number
When creating new items in QB POS DT, QB POS DT creates an incremental Item Number for each Item beginning with "1".  When printing labels with barcodes based on the Item Number (which QB POS DT uses as it's default label), QB POS DT creates the barcode with a minimum of 6 digits.  For any item number smaller than 6 digits it will pad the barcode with leading zeros. For example, if you open notepad and scan the barcode for item number "1" you will see "000001" as the result in notepad.  If you scan the barcode for item number "12" the result will be "000012".  If you scan the barcode for Item Number "12345" the result will be "012345", etc. You must make sure you match the number you are formatting with the number being produced when you scan the barcode.  So, you must make sure all barcodes are a minimum of 6 digits in your spreadsheet.  Here is a formula to do this:
=IF(LEN(L2)=1,CONCATENATE("00000",L2),IF(LEN(L2)=2,CONCATENATE("0000",L2),IF(LEN(L2)=3,CONCATENATE("000",L2),IF(LEN(L2)=4,CONCATENATE("00",L2),IF(LEN(L2)=5,CONCATENATE("0",L2),L2))))
Scenario #2 - Scanning a UPC-A (12 digit barcode)
QB POS DT stores UPCs in the UPC field of the Item record and in doing so converts the 12 digit UPC to a 13 digit barcode by padding the data with a leading zero.  The leading zero needs to be trimmed within the Excel spreadsheet before importing into the Revel barcode field.  Although not many EAN13 barcodes have had a leading zero, we are seeing newer EAN13 barcodes with a leading zero.  By using this Excel formula your EAN13 barcodes will not scan properly.  Here is the Excel formula that will remove the leading zero on a 13 digit barcode:
=IF((LEN(RC[-3])=13),IF(LEFT(RC[-3],1)="0",RIGHT(RC[-3],12),RC[-3]),RC[-3])
Please be sure to change the cell references.

 

In case you prefer to handle both Scenarios use the following Excel formula:

=IF(LEN(L2)=1,CONCATENATE("00000",L2),IF(LEN(L2)=2,CONCATENATE("0000",L2),IF(LEN(L2)=3,CONCATENATE("000",L2),IF(LEN(L2)=4,CONCATENATE("00",L2),IF(LEN(L2)=5,CONCATENATE("0",L2),IF((LEN(L2)=13),IF(LEFT(L2,1)="0",RIGHT(L2,12),L2),L2))))))
=IF(LEN(L2)=1,CONCATENATE("00000",L2),IF(LEN(L2)=2,CONCATENATE("0000",L2),IF(LEN(L2)=3,CONCATENATE("000",L2),IF(LEN(L2)=4,CONCATENATE("00",L2),IF(LEN(L2)=5,CONCATENATE("0",L2),IF((LEN(L2)=13),IF(LEFT(L2,1)="0",RIGHT(L2,12),L2),L2))))))
Set SKU for SKU + Vendor + Item Number ->
=CONCATENATE(SUBSTITUTE(RC[2]," ","-"),"/",SUBSTITUTE(RC[30]," ","-"),"/",RC[-3])
Set Primary Vendor ID ->
=VLOOKUP(RC[10],'[Vendors List Export from Revel II.xlsx]Sheet1'!C1:C2,2,FALSE)
Set temp barcode field to make item number 6 digits
Sorted by barcode and copied Item Number to Barcode field
If the Barcode has a length of 13 make sure the left most character is not a Zero
=IF((LEN(RC[-3])=13),IF(LEFT(RC[-3],1)="0",RIGHT(RC[-3],12),RC[-3]),RC[-3])
Set customer notes field to include last sale date and rewards number
=CONCATENATE("Last Sale: ",MONTH(Y3),"/",DAY(Y3),"/",YEAR(Y3)," ","Reward # ",AB3)
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk