topic Re: Imported csv data is different on ArcGIS Pro than what is on my Excel sheet? in ArcGIS Pro Questions

Imported csv data is different on ArcGIS Pro than what is on my Excel sheet?

Emberlynn — Tue, 23 Feb 2021 02:06:44 GMT

I'm doing an assignment for my ArcGIS Pro class, and we were required to download data from data.census.gov, clean up the data in Excel, save as a csv file, and import that csv into a new map on ArcGIS Pro. However, when I upload my csv file to ArcGIS Pro (using table to table tool in geoprocessing), I'm missing a heading for my data in ArcGIS Pro, which resulted in all of my headings being pushed one column to the left. I made sure to change all my numeric data to numeric form, but when I open the table in ArcGIS, all of my numeric values became text fields. I can't change the data type from text to numeric in the fields view of the table and I can't fix the issue no matter how many times I try.

I've attached two screenshots of my data below.

If anyone knows how to fix this, I would really appreciate your help!

Re: Imported csv data is different on ArcGIS Pro than what is on my Excel sheet?

DanPatterson — Tue, 23 Feb 2021 03:11:15 GMT

Excel To Table (Conversion)—ArcGIS Pro | Documentation .

Also make sure that you have no blanks in your spreadsheet. and I would explicitly format each column that is numeric as numeric.

Re: Imported csv data is different on ArcGIS Pro than what is on my Excel sheet?

jcarlson — Tue, 23 Feb 2021 03:14:26 GMT

A spreadsheet, unlike a Feature Class or a database table, doesn't have an explicit type for each column, and it's not uncommon for the column's data type to be incorrectly inferred, or for a single value in the column to throw off the the way the rest are stored.

Without making any significant changes to your input CSV, just spend some time going through every field in the Field Map section of the tool. This allows you to explicitly state the type for each field in the new feature class.

Alternatively, you could also create the new table first, then use Append to copy the CSV rows into it.

And lastly, if you want to tackle this a different way altogether, you can query the Census API directly and the resulting JSON can be converted directly into a FeatureSet that can then be written to a Feature Classs, merged with other data, etc.

Re: Imported csv data is different on ArcGIS Pro than what is on my Excel sheet?

RolandDuhaime — Tue, 23 Feb 2021 03:16:30 GMT

It is hard to tell what is going on without having a copy of the data... but I suggest renaming the field "Geographic Name Area" to Geographic_Name_Area... to eliminate the spaces. Also, there is an Excel to table tool in ArcGIS Pro that might work better for importing your Excel spreadsheet.

https://pro.arcgis.com/en/pro-app/latest/tool-reference/conversion/excel-to-table.htm

Best Wishes, Roland

Re: Imported csv data is different on ArcGIS Pro than what is on my Excel sheet?

Emberlynn — Tue, 23 Feb 2021 03:22:19 GMT

I tried renaming the field "Geographic Name Area" to Geographic_Name_Area like you suggested, then using the Table to Table tool (my professor uses Table to Table because she wants us to convert the xlsx file to a csv and import it as a csv file).

I then tried using Excel to Table tool and that worked! Thank you so much!

Re: Imported csv data is different on ArcGIS Pro than what is on my Excel sheet?

MicZatorsky_AEC — Tue, 23 Feb 2021 23:55:45 GMT

Check to see if you have a schema.ini file in the same folder as your csv that references you CSV. It can set column names and type columns.

Re: Imported csv data is different on ArcGIS Pro than what is on my Excel sheet?

Anonymous User — Mon, 10 Jan 2022 20:15:17 GMT

I'd never opened this file and honestly never wondered about it. Lo and behold, the fields I was interested in were set to Text. Changing them to "Double" worked. Thanks for the solution!

Re: Imported csv data is different on ArcGIS Pro than what is on my Excel sheet?

BrendanDijkstra — Tue, 14 Mar 2023 07:55:49 GMT

This was incredibly helpful! I couldn't wrap my head around the fact that some csv loaded properly as doubles and some got randomly converted to text. I wonder what initially made this schema.ini file, is it excel of ArcGIS Pro?