nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

.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.

m1w_parcel_lean_edit.xlsx

ChelseaRozek_0-1732731768745.png

Find more posts tagged with

Accepted answers

DanPatterson

Fixing the excel sheet would be easiest

=TEXT(A2,"00#######") +"000"<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

All comments

DanPatterson

Fixing the excel sheet would be easiest

=TEXT(A2,"00#######") +"000"<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

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

ACreek

Thanks for reaching out. The issue is caused by the Parcel ID only containing numeric values however the desired format is a text structure.I wanted to add a few more methods that you can use when importing .csv files.

The first is to create a new table within your project and add the fields with their desired field types. In this example you would add a field named "Parcel" and the field type to text. Then you can append or load your data into this empty table.

The second is to tell ArcGIS Pro what schema to expect in the .csv file. You can do this by using notepad or another text editor to create a schema.ini file.

1. Open Notepad

2. Copy the following into the new notepad file

[m1w_parcel_lean_edit.CSV]

ColNameHeader=True
Col1=Parcel Text

3. Save the file as schema.ini and store it in the same location as the .csv file

When you open the csv within ArcGIS Pro, the fields you define in the schema.ini file will be known.

If you have any questions please feel free to reach out.

schema.ini.zip

doggpath

thank you very much!!!

very much appreciated..

doggpath

Thank you, Dan!

very much appreciated.

doggpath

Thank you, Joe.

Definitely will try all the solutions recommended.

JoeBorgione

Just also noticed that in the xls you sent, you use a comma to delineate thousands, so if you save that to a csv, things are gong to get real whacky for you.

Parcel,Amount Due
001015003000,"2,638.56"
001055006000,"1,046.08"
001057001000,360.12
001058006000,"1,517.84"
001065001000,342.88
001065020000,617.04
001078013000,322.32
001085016000,"1,822.18"<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Notice how some of the values of amount due are treated as text (records 2,3,5,9) while others are float. I've taken the comma out of the thousands and saved as csv again to get this as my csv:

Parcel,Amount Due
001015003000,2638.56
001055006000,1046.08
001057001000,360.12
001058006000,1517.84
001065001000,342.88
001065020000,617.04
001078013000,322.32
001085016000,1822.18
001085017000,1822.30<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

With a little python you could also open that csv and enclose the the APNs with double quotes but I think you have enough to work with already.

doggpath

Correct. However, like most things in GIS, we are given data to work with. This file was sent to me to add into GIS and join to an existing larger GIS parcel set. The joys of GIS... polishing a turd. 😉 FUN STUFF!!

JoeBorgione

The GIS isn't a **** to polish, it's the data non-database people provide us that's the ****. In another thread, someone called Excel the Devil's Database. That's what I call it now too....

AmyRoust_2-1755273088307.png

JohnEvans6_0-1755273272485.png

JohnEvans6_1-1755273333326.png

DanPatterson

https://www.explainxkcd.com/wiki/index.php/2180:_Spreadsheets

doggpath

correct. I personally believe GIS technology is amazing. The data we sometimes have to work with... not soo much, until it's cleaned up.

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?

doggpath

I wasn't able to get the first method working.

I created a table within the Geodatabase, created the fields with the correct header-names and data-types, right clicked the newly created Geodatabase table and selected 'load data', but it seemed to still truncate the APN numbers.

I wasn't able to get the second method working.

I saved the "m1w_parcel_lean_edit.xlsx" as a .csv, created the notepad m1w_schema.ini in the same location as the "m1w_parcel_lean_edit.csv", using the ArcGIS Pro Map-ribbon/Add Data brought in the .csv, but two digits were truncated? I may have done something incorrectly with the following .ini?

[m1w_parcel_lean_edit.CSV]

ColNameHeader=True
Col1=Parcel Text

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.

copying the Function results from

doggpath

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.