Preparing CSV files for use in ArcGIS Desktop?

Question

How do I prepare CSV files for use in ArcGIS Desktop.

I ask because I have some troubles using CSV files because ArcGIS attributes wrong field types to my columns and also misinterprets special characters such as á or ê.

I have read in the Esri forum that there is a so-called schema.ini file that defines somehow the field types e.g "Col22=V002 Text" see here http://forums.esri.com/Thread.asp?c=93&f=1149&t=64464

That's kind of funny because I have often seen these .ini files on my disc but never actually wondered what they are good for. It is kind of weird that Excel stores such metadata in an extra file since other programs like R don't do so.

I already tried to manipulate this .ini file with little success since I didn't find out how to apply for example "string" type. There are some information on MS sites, see here: http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353%28v=vs.85%29.aspx but I couldn't find a solution.

Also I didn't really like the idea to work with this .ini file because it is quite a bunch of work to define and type all the fieldnames when I have let's say 50 columns. And these .ini files might get lost, etc.

Answer 1

My quick fix is to create the first row all with dummy values, and then delete this row/record after bringing into in ArcGIS.

This first row contains representative values or often wildly different values (e.g. alphabetic characters even if the column contains numbers that I want to be text data type) and with the largest number of characters needed for that row (because text fields tend to get truncated).

Date/time values are subject to import errors (especially between Canada/U.S. default date formats) so my work around is to split the date/time parts in to separate columns (e.g. year, month, day, hour, minute), and then concatenate these in a new field calculation after successfully bringing into in ArcGIS.

The geographic coordinates tip from Jamie is also necessary - specify negative values for western hemisphere longitude and southern hemisphere latitude. And unicode takes care of special characters.

Lastly, if a field data type is still misinterpreted after bringing into ArcGIS I will add a new field in the correct data type and calculate/convert the values from the original field, but usually the dummy row/record takes care of most, if not all, problems.

Answer 2

A slight departure from the traditional CSV->ArcGIS layer could be to use ogr2ogr to create your shapefile from CSV BEFORE loading it into ArcMap.

When using OGR, you can manually create a CSVT file that describes your column types, similar to what ArcGIS attempts to do with the schema.ini file.

This post by @underdark on How to Specify Data Types of CSV Columns (for use in QGIS) explains the details of creating a CSVT. You can ignore the use of QGIS, as it just uses ogr to import the CSV data into it's UI.

Answer 3

With ArcGIS 10 you can create the feature class and set all field types first, then load the .csv file using ArcCatalog. Simply right-click the feature class and choose Load > Load Data. Then follow the Simple Data Loader dialog to match fields, etc. It is a simple loader in that there are not many options but it works for well-formatted data. I'm not sure if this is new to 10 because I don't have an earlier version handy to check.

Answer 4

The correct way to resolve these issues is by using a 'schema.ini' file. This is a Microsoft standard way of handling input from CSV into a database where the column type could be interpreted ambiguously.

ArcGIS respects these 'schema.ini' files when importing from CSV.

See Microsoft's documentation at: https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-2017

The 'schema.ini' file needs to be in the same location as your CSV file and it can include configurations for multiple other CSV files in the same location if you need it to.

A simple 'schema.ini' file for a single CSV file that only has one column that otherwise gets read with the wrong type could look like this:

[SomeFileName.csv]
Col2=SomeFieldName Long

Answer 5

A workaround that I have found is to open the CSV in a text editor (I used notepad++) and save as a CSV again. I don't see any visible changes in the file content, including special markup characters, but the problem likely is related to how Excel formats CSV files. I was saving the file in Excel using the standard CSV options 'CSV (comma-delimited)(*.csv)'. Perhaps a different CSV format would work better.

Additional info: Something fundamental must have changed with the CSV file when I saved it with notepad++, because now I can edit and save it in excel and it is still imported as numeric by arcmap. I have no clue what changed though.

Answer 6

Surprisingly opening the original file (for me .txt) in a text editor (UltraEdit for me) and saving it as a .csv or .txt worked. There is no noticeable change to the file in UltraEdit, pandas, or any other method of examination I can see.

None of the workarounds, including my own, explain the fundamental underlying reason why ArcMap only sometimes decides to read number columns as text.

So, this worked twice... then the exact same procedure stopped working.

I have to remove all joins, remove the table, shut down ArcMap, open the table in a text editor, re-save the table, open ArcMap, add the table, and redo the join... every time I change anything in the table.