Field format lost when exporting events layer to shapefile - long field type converted to double

Question

I have a MS Access 2010 database with about 80 000 records in it. It is point data for animal locations. I would like to bring this into GIS and project the data into lambert conformal conic. I am experiencing some difficulties.

First I tried exporting the database in MS Access 2010 as a .csv before importing to ArcGIS 10.1, but there is apparently a known bug where Access will cut off your double fields to two decimal places. This doesn't work for me: I need those lat longs to be precise!

Then I tried exporting the data as an Excel .xlsx file type. Here all of my long integer fields from Access were converted to doubles (no good - for future processing I need those fields to be longs or integers in GIS).

Then I tried to directly import the x,y data using an OLE DB Connection. So far so good... the events file from the x,y data is imported as NAD 83 and the integer fields from Access are shown as being the long data type in Arc GIS.

Crossing my fingers I tried to then export this events file (NAD 83) as a shapefile (using the background data frame projection, lambert conformal conic, as to properly project the data for visualization). All of my fields that were previously classed as "long" in GIS have become doubles - and now they don't work in any of my post processing programs (namely, GME the geospatial modeling environment).

This is my first question - not sure if I can upload some sample data but I'm happy to provide it if I can figure out how to do it!

I have considered just bulldozing through them and making all new fields in GIS using the field calculator to change the field types, but I will be performing these kinds of operations regularly and since my data has about 50 fields I'd rather get this solved!

I am trying to export these data to a shapefile so that UDID is in long format.

Answer 1

When adding table information from any source to arcmap, selecting the add xy option,
Arcmap programmatically converts the data (internally) to a spatial type and displays it on your map with the xy coordinates in the projection you choose.
When you select any data source and right click and choose export data there are no options to modify the output field type/s.
In order to accomplish this task so that you have EXACTLY the field type you want. Create an empty shapefile (utilizing steps 1-3 below).
Steps 1 and 2 keep you from having to manually add all your fields.
Import brings them in from your mdb.
This will provide a container with the correct field types in a shape file.
Then steps 4 and 5 will export the data with the wrong field types and you load that data into the container with the right field types.

Use these esri resources help pages to understand limitations and uses.
Data Type in the DBMS
ArcGIS Field Data Types

1.) create a new file geodatabase in arccatalog and on the field tab use the import feature.

2.) select the original file. (your accdb)
3.) modify the field type to exactly what you need.

4.) export the data to a scratch shapefile. (or better yet file gdb.

5.) Load the data using simple loader to the file gdb you created with the specific fields.

Answer 2

If some of the other suggestions posted don't work, another option could be using your initial export to CSV idea.

However, before exporting, simply add an extra X and Y field and calculate the current XY values * whatever the number would be to remove the decimal (ex: 32.123456 * 1000000 = 32123456) you can even store that as a text field if it's easier for formatting (something like replace(field1,".","") depending on what system/language you're using for the calculate). Then export to CSV.

Then, import the CSV into ArcGIS (if you have an issue with field types changing or not being what you expect during a CSV import all you have to do is use a schema.ini file (see support for that at the very bottom of this page http://resources.arcgis.com/en/help/main/10.1/index.html#//005s00000010000000 and at this page http://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx

Then when you get it into ArcGIS as a table you can simply calculate the original XY fields from the new ones you stored either by dividing by the appropriate number (32123456 / 1000000 = 32.123456) or placing the decimal back at the appropriate character position if storing as a text value (ex: place "." after the 2nd character in 32123456.). Then you could use this to create a feature class/shapefile from a table with XY values.

Yes this involves probably a few more steps than you would ideally like, but I figured I'd throw it out there as an option if you weren't able to get the others to work.

Answer 3

I had problems setting fields to Long Integer in Access only to have them converted by ArcGIS to Double when exporting them to their final format using either ArcCatalog or ArcToolbox.

The solution I found was to load the feature set, or table, into ArcMap while it is still in Microsoft Access and then right-clicking on the feature set/table and choosing Data - Export Data. The resulting exports save the feature set/table with the Long field type I set in Access.

My guess is that ArcCatalog and ArcToolbox exports process the data in terms of what will best define it for use by ArcGIS, while ArcMap just accepts the data as it is presented to it, and it either works or it doesn't. The result being exports from ArcMap forward what it is told the data is, not what it thinks it should be.