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

3802
19
Jump to solution
09-13-2020 08:57 PM
doggpath
New Contributor III

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. 

19 Replies
doggpath
New Contributor III

Thank you, Dan!

very much appreciated.

0 Kudos
doggpath
New Contributor III

Thank you, Joe.

Definitely will try all the solutions recommended. 

0 Kudos
AndrewCreek
Esri Contributor

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.

doggpath
New Contributor III

thank you very much!!!

very much appreciated..

0 Kudos
doggpath
New Contributor III

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

0 Kudos
JoeBorgione
MVP Emeritus

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"

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

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.

That should just about do it....
doggpath
New Contributor III

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

0 Kudos
JoeBorgione
MVP Emeritus

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

That should just about do it....
DanPatterson
MVP Esteemed Contributor
doggpath
New Contributor III

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

0 Kudos