ArcGIS Pro Ignores Changes to CSV data

2739
14
04-17-2019 12:20 PM
MattAnderson2
New Contributor

I added a csv datasource to my project, but after adding it I found two problems. The first is that a column filled with numeric data had a "Grand Total" entry in the bottom row, so ArcGIS Pro intrepreted the column as text instead. The same column also had a non-descriptive heading label. I removed the datasource from ArcGIS Pro, open the csv file in Excel, remove the grand total row, and change the column heading to something more descriptive, then save the file. I go back into ArcGIS Pro and re-add the datasource. To my frustration, ArcGIS Pro is still calling the column by its old label and still interpreting it as text. Why is this application so frustrating??? How can I get it to read the file without deleting everything and starting over with a new project?

14 Replies
DanPatterson_Retired
MVP Emeritus

You removed the file from the project, then saved the project?

Close and restart does nothing?

Try a simple rename of the *.csv, then re-add

Add the file to an empty project.

Any of those work?

0 Kudos
MattAnderson2
New Contributor

Thanks for the reply Dan.

If I remove it from the project, save the project, then restart and reopen the project, it still reads the field as text.

So I went into Windows Explorer and changed the filename. I tried to re-add the file, but the ArcGIS Pro "Add Data" dialog still shows me the old file name. If I try to open that, I get an error ("The file doesn't exist"...no $%#&*, I just renamed it). I have to restart ArcGIS Pro for it to see the file with the new name. Unbelievable.

After I have restarted the application, reopened my project, and re-added the renamed file, the application FINALLY reinterprets the data, realizes that there is no longer a call that says "Grand Total" and correctly interprets the column as numeric. At this point I've only torn out half the hair on my head.

Just for kicks I try making a new project, but in the dialog to set the path for my new project, I cannot create a new folder. I have to go back to Windows Explorer to create a new folder for my project. That's why my company is paying thousands of dollars for an ArcGIS license....because we want to have to open a whole separate program to create a folder (*sarcasm*).

ESRI software makes me reconsider my career choice as an urban planner.

0 Kudos
KoryKramer
Esri Community Moderator

Matt, what version of Pro are you using?

In the current version, 2.3.2, it is possible to create a new folder when I'm pathing to where I want to create a new project:

0 Kudos
JoshuaFlickinger
New Contributor III

Did anyone figure out what's happening here?  I'm running Pro 2.5.1 and noticing some similarly strange behavior where ArcGIS Pro appears to be 'remembering' something from old csv files.

Basically I've got a jupyter notebook that generates a csv file from a pandas dataframe and then imports the csv into a geodatabase using the table to table tool.  (As an aside, it would be nice if the arcgis API could directly export pandas dataframes to geodatabase tables so I wouldn't have to use this workaround to begin with.) 

My notebook worked as expected the FIRST time.  However, after tweaking my code and re-running, my notebook spits out a csv that has been re-arranged.  In other words, the order of columns is not the same.  When this newer version of the csv is added to a map in Pro, I can open it and see that it doesn't update correctly.  Unfortunately it maintains some of the old csv file structure.  But not all of it.  It's a bit jumbled up.  Certain fields that I added to my dataframe/csv have been 'overwritten' by the fields which were originally in that position in the csv.  I check the csv file itself in Excel and am seeing exactly what I expect... so I know the csv generation part of the process is fine.  

Of course then the subsequent table to table tool produces wonky results as well, as shown below, where those 'overwritten' fields are trying to read into the field map twice.

field map in the table to table tool not working properly

I'm a bit confused as to why this should happen, as my notebook totally erases the geodatabase workspace and reconstructs it each time I run the notebook.  The problem DOES resolve itself if I close everything down and rename the csv and re-add it... but this still seems like a bug and could be a big "gotcha" for people.  Any feedback on why it's happening?

ValerieZimmer
New Contributor

Me too, and this happens in ArcMap and ArcGIS Pro, for both .csv and for .xls files.  Seriously, ESRI, why is this a thing?  A year later, and why is there still no solution or response from ESRI on this?

 

 

DeanChiang
Occasional Contributor

It is June 2023 and this is still happening (v2.9.2).  Remove the CSV file from map. Save, close, and open APRX. Click Refresh on Project, Folders, the data source folder. Dozens of times.  Still the old *ghost* data field names persist in the latest CSV file (view by Open or under Fields in Table To Table conversion tool).  If it was just the field name, I can change that after converting to file geodatabase table, but the data type is interpreted wrong too for me not to be able to import the data. 

How is it a native Windows Desktop software program cannot read a text file on the system dynamically correctly?  Boggles the mind.  Not to mention hours of time wasted. 

It's a question for ChatGPT4 - but I don't have time to do fancy GIS stuff. Cannot even import a text file into ArcGIS *Pro*.

 

DeanChiang
Occasional Contributor

A workaround is to rename the CSV file differently every time you refresh the source CSV or change its data structure, but that is no way to build a reliable, repeatable workflow or automate a task.

Here is a potential solution (need others to verify):

Trying to figure out why/where ArcGIS Pro is persisting data format for these external files, I noticed there was a file called schema.ini in the source folder for the CSV files that I tried to load into Pro. I deleted that file (which I did not create).  Open the APRX project again - Voila!  The CSV file now loads with the correct field name and type, and converted to FGDB table correctly.

I cannot believe it took me this long to figure this out, or that Esri does not have this documented somewhere. 

EricaNova
Occasional Contributor

Thanks @DeanChiang , that worked for me.

0 Kudos
KellyDunn
New Contributor II

A better question is why are all these steps necessary, even if they work? Why can't saving tne csv and refreshing cause it to update? Would love if this could be improved.