AnsweredAssumed Answered

CSV data source documentation

Question asked by tminterWork on Jul 17, 2018
Latest reply on Jul 20, 2018 by bharold-esristaff

ArcGIS Pro (and ArcMap before it) would make some assumptions about the column values in a CSV file. Too often in my experience, the assumptions were just plain wrong, causing speed bumps, gnashing of teeth, and general furrowing of brows. It turns out that Esri's customers who use ArcGIS Pro can remedy the assumptions being made by using a schema.ini file to tell ArcGIS Pro how to perceive the column values. It also turns out that as of 7/17/2018, it's really hard to find this information, which can lead to time spent interacting with the Esri support teams. So, I'm sharing some info here, where I might be able to find it when I need it again.

 

Background

  1. This becomes a problem when you need to import text values that are composed of:
    • numerals (0009198045), and keep the leading zeros
    • numerals and dots (1.0), and need the resulting value to specify a release version in text format instead of "1"
    • and probably some other cases I don't remember
  2. GP tools and other functions don't seem to expose any control over how ArcGIS Pro should perceive column values. For example, in the Append GP tool, specify a CSV file as input, click on a field name in the "Field Map" area of the dialog, click "Source", and hover over the field name. ArcGIS Pro will show you a "tooltip" (or something) that tells you what type of value it has assumed is in the field.
  3. There is very little info about this situation in the ArcGIS Pro Help documentation. Esri support referred me to this, which appears to be related: http://resources.arcgis.com/en/help/main/10.1/index.html#//005s00000010000000. I just didn't think to look there for help with ArcGIS Pro. I probably need to up my game .
  4. Schema.ini info - (random search result) Schema.ini File (Text File Driver) | Microsoft Docs 
  5. Edit:  Esri Support Services reports that Esri does not document how to import CSV files in the ArcGIS Pro help because Esri's view is that the issue is not with the tool, rather "the issue is with the CSV" (maybe they mean that the issue is in how they have chosen to design and develop their tools to work with CSV files... maybe not).
    • I don't buy this proposed notion because from the user perspective, Esri's tools read the correct value and write an incorrect value.  As a user, I don't care if the developers use a stick to scratch in the sand to make their function work correctly or if they use the Microsoft ODBC Text File driver.
    • Maybe my idea posting will get some traction, or maybe this is an edge case that no one cares about.  ArcGIS Pro - specify CSV file input field definitions 


Workaround 

- from an Esri support request for ArcMap, tested and adjusted for ArcGIS Pro

  1. Add the csv file to the Contents of an ArcGIS Pro map.
  2. Export the csv file to a new folder outputting the same file name as the input. ArcGIS Pro will write out the CSV file and a "schema.ini" file.
  3. Using Windows Explorer go to the output location and copy the schema.ini file that gets created.
  4. Paste it into the original location (original folder).
  5. Right-click and edit the schema.ini file to specify the correct field type. Note that the header specifies the target CSV file name.
    • Edit:  Note that you'll need to make the column number indicators and specified fields line up with your source CSV.  During step 2 above, ArcGIS Pro adds its OBJECTID field and writes that specification to the schema.ini table.  If your CSV file does not have an OBJECTID field, then things may not go well when you use it, unless you fix it.
  6. Now, when you use the CSV file with ArcGIS Pro, it should (hopefully - haven't tested all cases) notice the schema.ini file and use it to override the assumptions it has made about your input.

 

ArcGIS Ideas

  1. Expose the ability for the user to override ArcGIS Pro assumptions and specify CSV file input field definitions in all ArcGIS Pro tools and functions that accept CSV as input
  2. Document the capability and/or the workaround in the ArcGIS Pro Help instead of causing support requests and Geonet posts.
  3. Edit:  

 

Alright, enough of that...  back to work!

tim

Outcomes