topic Re: .xlsx data import truncated by ArcGIS Pro/removes leading/ending zeros in AEC (Architecture, Engineering and Construction) Questions

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

doggpath — Mon, 14 Sep 2020 03:57:53 GMT

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.

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

DanPatterson — Mon, 14 Sep 2020 10:27:14 GMT

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

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

JoeBorgione — Mon, 14 Sep 2020 13:14:43 GMT

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

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

doggpath — Mon, 14 Sep 2020 16:07:32 GMT

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.

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

DanPatterson — Mon, 14 Sep 2020 16:54:34 GMT

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

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

AndrewCreek — Mon, 14 Sep 2020 17:10:23 GMT

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.

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

doggpath — Mon, 14 Sep 2020 17:29:36 GMT

thank you very much!!!

very much appreciated..

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

doggpath — Mon, 14 Sep 2020 17:30:13 GMT

Thank you, Dan!

very much appreciated.

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

doggpath — Mon, 14 Sep 2020 17:32:52 GMT

Thank you, Joe.

Definitely will try all the solutions recommended.

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

JoeBorgione — Sat, 11 Dec 2021 07:43:41 GMT

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.

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

doggpath — Mon, 14 Sep 2020 23:04:02 GMT

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

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

JoeBorgione — Mon, 14 Sep 2020 23:16:57 GMT

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

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

DanPatterson — Tue, 15 Sep 2020 01:20:44 GMT

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

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

doggpath — Tue, 15 Sep 2020 02:49:07 GMT

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

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

doggpath — Tue, 15 Sep 2020 02:59:51 GMT

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?

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

doggpath — Tue, 15 Sep 2020 03:24:44 GMT

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

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

BenjaminPezzillo — Thu, 17 Sep 2020 17:47:19 GMT

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

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

doggpath — Tue, 22 Sep 2020 00:54:04 GMT

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

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

doggpath — Tue, 22 Sep 2020 01:01:53 GMT

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

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

BenjaminPezzillo — Mon, 28 Sep 2020 15:57:37 GMT

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