Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I wonder if there are any recomendations how to properly prepare CSV files for use in ArcGIS. 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.

So, is there any better way to properly prepare CSV files for use in ArcGIS?

share|improve this question
1  
ESRI creates the INI files ("stores metadata in an extrafile"), not Excel. –  user3461 Jan 2 '13 at 16:17
3  
I give to recommendations. If using something like WGS84, WEb Mercator, etc. use negative instead of east or west. Also, make sure to save your CSV files as unicode. Will probably help with your character issues. –  Jamie Jan 2 '13 at 16:33
3  
According to webhelp.esri.com/arcgisdesktop/9.2/… it is ArcGIS that creates the schema.ini file, but it appears to create the INI to "Microsoft ODBC Text Driver for text files" specs. Schema.ini is not created when a csv file is opened/saved in Excel, but is created any time Arc Map/Catalog read a directory with CSV files present (assuming you have write access to the directory). –  user3461 Jan 2 '13 at 17:23
1  
@GeoKevin, thanks for the clarification! I wasn't aware that we (ArcGIS) create the files when using the ODBC text driver. –  mkennedy Jan 2 '13 at 23:18
2  
Also have a look at this question: gis.stackexchange.com/questions/42522/… It details how you can auto create a schema.ini file & then edit it, so that you can control the data types when ArcGIS open your csv file. –  Devdatta Tengshe Jan 3 '13 at 3:28
show 5 more comments

3 Answers

up vote 9 down vote accepted

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.

share|improve this answer
    
+1 that's what I do too. The first record in the CSV should contain values representative of the data types in each field, then delete that record once it's successfully imported. –  Dan C Jan 2 '13 at 18:10
1  
+1 me too. The other trick to use.make sure field names are 8 char with no special chars or spaces –  Brad Nesom Jan 3 '13 at 4:19
    
@ccn thanks for your advice. it worked out a little better for me but I still had to change the field type of some rows... but thats okay. –  Dspanes Jan 3 '13 at 13:02
add comment

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.

share|improve this answer
    
+1 for nice "outside-of-the-box" solution –  user3461 Jan 2 '13 at 18:53
add comment

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.

share|improve this answer
    
The Simple Data Loader has been around since ArcGIS 8. –  blah238 Jan 2 '13 at 19:16
    
I'll take this advice for smaller tables. thank you. Still for big tables with lot's of collumns it seems to be quite some work to define every field manually. –  Dspanes Jan 3 '13 at 13:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.