Getting ArcMap to recognize csv column as float and not int? [duplicate]

Question

I am trying to show a .csv file (2 columns, 291 rows) in ArcMap 10.2 using Catalog window to drag the .csv file into ArcMap. The second column of the .csv is a floating point number (range is [0-1]), however ArcGIS is treating that column as an int. That is: I can view the column as numeric with decimal places in Excel, text editor, etc., but when I open the table in ArcMap the column is reduced to 0, 1 or null. All non integers are replaced to null.

How to I ensure that ArcMap 10.2 reads the column as a float?

I have tried the following, but none have worked:

1. Re-ordering the observations so that observations with decimals are in the first eight rows (since I believe Arc looks at the first eight rows to determine data type).

2. Editing the scheme.ini file to indicate column 2 has the datatype float. (But I may not be doing this correctly.)

3. Saving the .csv file with an alternative delimiter.

4. Changing the column in Excel from numeric to text.

5. It's not possible to change the field from int to float as implied by this question/answer thread: Preparing CSV files for use in ArcGIS Desktop? question/answer

6. Adding a text "dummy" in the first 8 lines (also suggested by Preparing CSV files for use in ArcGIS Desktop? question/answer) that is text (to try to force the field into text so that I can convert to float in Arc) also did not work.

Answer 1

This is an expected behaviour from ArcMap. You can overcome it by using schema.ini

Test

I created two text files called csv2arc.csv, schema.ini in the same folder.

csv2arc.csv content:

oid1,num1,num2
0,3,0.05
1,0.343,0.23
2,0.23,0.234
3,0.001,2
4,5,5.5

schema.ini content:

[csv2arc.csv]
Col2=num1 DOUBLE

It turns out ArcMap can not determine the proper type of the second field (num1). So I created the schema.ini and now it work fine. Simply adding the csv file to the map show the double numbers correctly. No need to export (e.g. to dbf, gdb table ...); no need to use excel or any other application; no need to reorder your records; as they are all tedious and unpleasant.

I tested it with ArcGIS Desktop 10.2.2

If schema.ini doesn't work for you, I suggest you to share a sample of your csv and your schema.ini file in your question.

Answer 2

Unfortunately the quickest easiest way to solve this is to ignore it and format the file in excel. You can try various tricks but there are always cases where they don't work - now I just import to excel. ArcGIS doesn't offer a lot of control over the csv format or how to import it whereas now days it will read a well formatted excel file easily and consistently

1. Open you csv in excel.
2. Set all the columns to the correct Excel type (text to text, numbers to numbers etc) - if you want float then use a numeric format with many decimal places
3. Save your Excel version
4. Import the excel worksheet into ArcGIS using the table to table conversion tool which will now read the column headings and give you the option of setting data types via the field map control (and respecting your choices for the most part).

Cumbersome but works every time.

Answer 3

I have similar problems with ArcGIS creating number fields instead of text fields. One way I fix it is to open excel, or other spreadsheet, and then open the csv file. This forces the use of the "Text Import Wizard". One of the last steps allows you change the field type. then save as dbf and import that to your geodatabase.