.xlsx data import truncated by ArcGIS Pro/removes leading/ending zeros

[doggpath]

Importing .xlsx file which has 1,977 12-digit APN data into ArcGIS Pro results in the 12-digit APN data being truncated, and leading/ending zeros are removed.

This also occurs if i convert the .xlsx file into a .csv file, and import it into ArcGIS Pro, it also truncates the 12-digit APN number and removes the leading/ending zeros.

How can I import either a .xlsx or .csv into ArcGIS Pro without the data being truncated??

Any help would be appreciated...

Thank you in advance. 

[DanPatterson · Accepted solution]

Fixing the excel sheet would be easiest

=TEXT(A2,"00#######") +"000"‍

in cell C2, put in the above formula and copy it down the column

In cell C1... Give it a name like Parcel_txt

Or just do it in Pro using a field calculator function.... easy as 1, 2, 3

[doggpath]

I was able to SOLVE the problem using your SECOND method: "Or just do it in Pro using a field calculator function.... easy as 1, 2, 3". 

Thank you very much!!!

The FIRST method: "Fixing the excel sheet would be easiest". 

I created the new column, populated it with the formula, and the leading/ending zeros did appear. But once I brought it into ArcGIS Pro, the zeros disappeared.  Not exactly sure why?

[BenjaminPezzillo]

Did you copy the data column and then "Paste Special" with "Values Only" selected? 

That gets the formulas out of the data. Then you might want to check the data type setting for the column. After adding the Leading Zeros and Paste Special, you might want to change the data type to Text. That should lock in the changes you want in the table before you save it and import it

[doggpath]

1) copy "FUNCTION" results from 'Column C' and paste-special-value into TEXT formatted 'Column D'. Leading/Ending zeroes are truncated. 

[doggpath]

2) copy "FUNCTION" results from 'Column C' and paste-special-value into GENERAL formatted 'Column E'. Leading/Ending zeroes are truncated

[BenjaminPezzillo]

Try converting Column A to Text before you Copy and Paste Special/Values Only?

Below:

Columns A/B/C -- all had the same six keystroke input but three different encodings at input

Columns D/E/F -- all copy and pasted special values only from A/B/C respectively, leading zeros as text preserved

[JoeBorgione]

Please define 'import'.  Are you using the excel to table tool or simply viewing the xls or csv in ArcGIS Pro?

[doggpath]

Morning Joe,

I am working in ArcGIS Pro 2.6.1

1st) I tried the "Add Data" from the Map ribbon.

2nd) I tried 'importing' from a Geodatabase I created.

3rd) I tried the Geoprocessing toolbox 'excel to table'

4th) I tried the Geoprocessing toolbox 'table to table'

5th) I tried loading the .xlsx data in Access, and then importing as a .dbf

Each time, the 12-digit APN data comes in, however the zeros are truncated which then creates either a 10-digit or 8-digit APN.

[DanPatterson]

Use one of the methods I suggested.

Next time you should format the column as text before you put anything in it.  Your column was formatted as numbers with a custom format. and hence wasn't text