x

Leading zeros dropped from CSV contact import

I imported my contacts when I moved to Square and, ever since, have been running to malformed zip codes. I live in Maine which uses a leading zero for many (all?) of our zip codes which seems to have triggered an issue with the CSV import script.
Obviously in many circumstances it makes sense to remove leading zeros, but clearly not when leading zeros are part of the recognized standard format for the data set (such as zip codes).

1,326 Views
Message 1 of 4
Report
1 Best Answer

Best Answer

Hi there

 

Went through the same issue.  Here is the scenario:

I want to clean up my client directory to implement some better categorization, etc.

 

So I did the export...and once again, I did not think about the fact that there are leading zeros in any of the stupid fields and just did a plain upload into GSheets.  

 

The damned auto import converted the "Text" to "Numbers".   Why does that matter?

 

Well -- for the purpose of a spreadsheet a "Number" is something that can be used to calculate math equations which means that 01 = 1 and 04401 = 4401 because there is no numeric meaning to a leading ZERO in a number.  But clearly, if you live Bangor, Maine -- the leading zero is pretty bloody important!   Well...in systems that recognize Zipcode format, they have know how to interpret that field.  But when you do CSV format -- it is in a universal format and the way it retains that leading zero is by being considered TEXT!!. 

 

So, when you import that file into the next program (Excel, GSheets or whatever), you need to make sure that new spreadsheet knows there are some fields where the data that looks like numbers are not NUMBERS, they are "TEXT" -- for example SKUs, Zip Codes.

 

So, how do you avoid tell the spreadsheet you are importing your CSV into that your POSTAL Code field is TEXT? 

 

1. Do NOT simply double-click & open the file in Excel -- even though it will let you do it.  If you do that, it will autoconvert the TEXT to NUMBERS. 

 

2. Instead, you must IMPORT any CSV that has leading zeros which will then open up a dialog box to ask you if you want to convert or treat Text as Numbers.   Tell it NOOOOOO!!!! 

Screen Shot 2020-11-06 at 4.33.40 PM.png

In fact, I am writing this post today because I cannot believe that I made this mistake -- yet again.  It's all good....I don't live in Maine -- so the number of leading zero zip codes that needed to be fixed was not  horrific...but EEEK.

 

Hope this helps.

View Best Answer >

1,240 Views
Message 3 of 4
Report
3 REPLIES 3
Square Community Moderator

Hi there, @bmos - thanks for reaching out to us here on the Seller Community! Happy to have you join us here! Welcome

 

This issue with your imports sounds frustrating, indeed. I did some digging on our side to see if this redaction of zeros in CSV files was being experienced by anyone else, and strangely am finding nothing like this particular issue. We may need to get direct visibility on this to troubleshoot; that way we can escalate this to our Engineers if need be.

 

Would you mind flagging this over to our Support Team when you get a chance? They will take a deep dive with you to insure everything is functioning properly going forward.

 

We will keep an eye out for your contact!

 

Joe
Community Moderator, Square
Sign in and click Mark as Best Answer if my reply answers your question.
1,270 Views
Message 2 of 4
Report

Best Answer

Hi there

 

Went through the same issue.  Here is the scenario:

I want to clean up my client directory to implement some better categorization, etc.

 

So I did the export...and once again, I did not think about the fact that there are leading zeros in any of the stupid fields and just did a plain upload into GSheets.  

 

The damned auto import converted the "Text" to "Numbers".   Why does that matter?

 

Well -- for the purpose of a spreadsheet a "Number" is something that can be used to calculate math equations which means that 01 = 1 and 04401 = 4401 because there is no numeric meaning to a leading ZERO in a number.  But clearly, if you live Bangor, Maine -- the leading zero is pretty bloody important!   Well...in systems that recognize Zipcode format, they have know how to interpret that field.  But when you do CSV format -- it is in a universal format and the way it retains that leading zero is by being considered TEXT!!. 

 

So, when you import that file into the next program (Excel, GSheets or whatever), you need to make sure that new spreadsheet knows there are some fields where the data that looks like numbers are not NUMBERS, they are "TEXT" -- for example SKUs, Zip Codes.

 

So, how do you avoid tell the spreadsheet you are importing your CSV into that your POSTAL Code field is TEXT? 

 

1. Do NOT simply double-click & open the file in Excel -- even though it will let you do it.  If you do that, it will autoconvert the TEXT to NUMBERS. 

 

2. Instead, you must IMPORT any CSV that has leading zeros which will then open up a dialog box to ask you if you want to convert or treat Text as Numbers.   Tell it NOOOOOO!!!! 

Screen Shot 2020-11-06 at 4.33.40 PM.png

In fact, I am writing this post today because I cannot believe that I made this mistake -- yet again.  It's all good....I don't live in Maine -- so the number of leading zero zip codes that needed to be fixed was not  horrific...but EEEK.

 

Hope this helps.

1,241 Views
Message 3 of 4
Report

Yup, that's what it was.
Thanks for the really clear write-up on how to do it properly!

1,229 Views
Message 4 of 4
Report