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.
Best Answer
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.