How can I deal with leading zeros when importing inventory with the .CSV?

[jsciabica]

I would like to change all the prices in my store.  This is only feasibly accomplished with a CSV import.  The problem is that I have had issues when importing my SKUs because they are Barcodes and MANY of them start with a zero.  If always try to fix them all in the CSV before saving, but inevitably, I miss one or more 😞

Is there a way to only update the fields I need to?  It would be nice to import a CSV with only 2 columns: "Token" and "Price"

[Sean · Accepted solution]

@jsciabica - Leading zero's can be quite a hassle, especially since most spreadsheet applications automatically default to scientific notation for columns that contain numbers (which mean they are dropped). While of course we recommend avoiding the use of SKU’s that contain leading 0’s, it's not always an option. To make sure you don't drop any digits, I'd suggest following these steps: 

1. With the item import CSV open, select the first SKU column so the entire column is highlighted (by selecting the column - you won't miss any items).
2. Right-click on the column > select Format Cells.
3. Select Text.
4. Click File > Save As and make sure the file format is saved as Comma Separated Values (CSV).
5. Click Save.
6. Import this new file to your online Square Dashboard.

Hope this helps! Also, if my answer solved your problem, would you mind marking it as "Best Answer"? That'll help the rest of the community find what they're looking for. If not, just write back, and I'll continue to help. Thanks!

[Sean · Accepted solution]

@jsciabica - Leading zero's can be quite a hassle, especially since most spreadsheet applications automatically default to scientific notation for columns that contain numbers (which mean they are dropped). While of course we recommend avoiding the use of SKU’s that contain leading 0’s, it's not always an option. To make sure you don't drop any digits, I'd suggest following these steps: 

1. With the item import CSV open, select the first SKU column so the entire column is highlighted (by selecting the column - you won't miss any items).
2. Right-click on the column > select Format Cells.
3. Select Text.
4. Click File > Save As and make sure the file format is saved as Comma Separated Values (CSV).
5. Click Save.
6. Import this new file to your online Square Dashboard.

Hope this helps! Also, if my answer solved your problem, would you mind marking it as "Best Answer"? That'll help the rest of the community find what they're looking for. If not, just write back, and I'll continue to help. Thanks!

[TheMixingBowl]

This is so frustrating and such a nightmare when trying to ring customers up. I wish I could charge $60 per month to make my customers so frustrated that they have to create their own spreadsheets after paying a so called cloud tech retail company to keep records and yet items and essential numbers in the SKU somehow magically just disappear. And I so appreciate getting Square ads constantly on my social media threads. How is that a priority when the app doesn't even work. Focus on fixing issues and not marketing. I would love to challenge any Square so-called programmer to come to my shop and work for a day with their system. Beta testing must not be recording the problems only the few things that actually work well.

[ryan1]

the best solution we found was to remove the prembles from the barcode scanner

[spektrummuzik]

We are a record store that has used square almost since we opened our business 5 years ago. We have never had a problem keeping tracking of inventory until the last few months. Today we discovered that when you download your inventory(.CSV file) from square, the file is dropping zeros from the beginning of all barcode number starting with zero. Was aggrivating to find given that we are about done with inventory. When you enter a barcode into the system (through square) it appears to be dropping a zero from the beginning as well.

[Sean]

@spektrummuzik - I helped another seller with the leading "0" issue in this thread. Check it out. 

[sciabica1936]

Yes, leading zeros are a bummer.  It's a shame that the designers of the UPC system even allowed codes with leading zeros to be registered!

Still, we're stuck with it and my company's UPC code starts with a zero so I have hundreds of codes that are a pain.

Adding the code though Square (register) does NOT drop the zero.  I recomend using a compitable scanner to add new items through the register.

In a spreadsheet, you need to highlight the entire column and change the format to "plain text".  Google is your friend to find a guide for your specific spreadsheet software.

HOW TO FIX: Unfortunately, you can not change the format BEFORE you open the file.  So, the zeros will still be 'gone', even after you change the format.  To add them back in, after changing the format, I use "find and replace".  For example, my UPC starts with 084938.  So I 'find' "84938" and 'replace all' with "084938".  The tricky part is identifying ALL UPCs that start with a zero and replacing them.

SUGGESTION FOR SQUARE:  The first thing we NEED RIGHT NOW is the ability to update our products via import WITHOUT a SKU column!!!  That way we can't screw them up after they're set up, but we can still update quantity and price. As long as we have the "token" column to match with the products, I don't see the need for the SKU.  OPTIONALLY, give us the option to export to .xlsx with the format of ALL columns as "plain text".  I belive this would also fix Excel changing long numbers (like UPC codes) to scientific notation which can also be really annoying.

[Sean]

@sciabica1936 - I'll be sure to pass along the suggestion! 

[sciabica1936]

Thanks Sean!  You may want to also suggest adding a separate field just for UPC 😉

And, update your "Best Answer" because if you simply change the format to "text", then "save as" and re-import, the zeros will still be gone.  You need to add the "find and replace" step.

[Sean]

@sciabica1936 - Happy to update the answer, but out of curiosity, how do you identify all UPCs that start wtih 0? Do you keep a separate list of them?

[Sean]

@sciabica1936 - Just wanted to see if you could share more detail. 🙂

[sciabica1936]

Hello Sean,

Yes, you'll need a list of UPCs with leading zeros.  However, there may be a way to bring in a CSV with leading zeros still intact via Excel's "Get External Data".

Here's a guide: http://www.upenn.edu/computing/da/bo/webi/qna/iv_csvLeadingZeros.html

I'm going to test today and will report back.

[Sean]

@sciabica1936 - Thanks for sharing! I look forward to hearing back. 👍

[Sean]

@sciabica1936- Any word on your test? 

[sciabica1936 · Accepted solution]

There is a way to bring in a CSV with leading zeros still intact via Excel's "Get External Data".

Using Excel:

In a new BLANK SPREADSHEET...

1) On the "Data" tab, choose "Import from Text".

2) On the popup window, choose "My data has headers".  Click "Next".

3) On the next page, under the delimiters section, UNCHECK "Tab" and CHECK "Comma".  Click "Next".

4) IMPORTANT:  Choose the the format for EACH column.  You can see in photo 4a that each column is set to "general".  You can simply click on the "SKU" column and then set it to "Text".  See photo 4b.  To be safe, you can select ALL columns and set them all to "Text".

5) You can choose where to import the data.  I suggest just leaving it as "Existing Worksheet".

 Step by step photos: https://drive.google.com/open?id=0B-UP4z3e62PBcXBySm5ieWxNUDA

[sciabica1936]

SUGGESTION FOR SQUARE:

Give us the option to place an apostrophe at the beginning of each field during CSV export.  That will ensure everything is kept exactly the same by all spreadsheet programs without any tricks.

[Sean]

@sciabica1936 - I'll be sure to pass along the suggestion, but I'm not sure that it would help would the leading 0's issue. Would you mind sharing an example of which problem this would solve for?

[sciabica1936]

Sure @Sean,

Also, It prevents Excel from interpreting the data.

You can test it yourself.  Pull up any spreadsheet software and type 0123.  You'll notice the zero disappears once you press enter or tab.  Now type '0123.  You'll see that the cell now includes the zero.  All formulas work just fine.

Also, I prefer to enter dates in the form of June 2016, Excel treats it as a date and reformats it in the form June-16. When I enter 'June 2016, Excel formats exactly as June 2016

The only thing to test would be re-importing the CSV with the apostrophes.

[Eyegor]

The external data fix doesn't work if you have certain special characters in fields like description (it seems to confuse excel even if you've correctly configured the import).

As an alternative, OpenOffice's spreadsheet application (openoffice.org), is much smarter and will allow you to set the format of the columns BEFORE the data is read into the spreadsheet and the SKU is correctly formatted as text.  OpenOffice is free of charge.