I get a excel spreadsheet document from someone. Ideally I would be able to export it as a dbf and then instantly upload it to Arcmap. However it is not always that simple.
Usually it is the heading that throws everything off. Arcmap does not like headers with spaces between words, so most of the time when you get an error when trying to import csv, txt, etc, those headers are to blame. Now what gets a little weird is when you change them, remove excess rows and you still have an import problem. For some reason, the GIS software can be more picky then it lets on. For example I could not import a file with this is a header ” 05_06_07_08_09_hg_kg” so I had to change it to a simpler Kg for it to accept the file. It is something you have to play with.
Since the files are created in Excel for the most part, they already have natural breaks from which you can use as delimiters. If you need to change some of the table data, I have found it easier to load it in a .txt document and correct from there.
Though it usually takes very little to change in order to bring it into ArcGIS, it is often easier to edit the data itself outside of the GIS with some of the tools already mentioned. You don’t want to be stuck changing the names of numerous fields one by one!
If the data you are brining in is basically more tabular information for features that already are in a spatial form. You will need to right click the appropriate feature class, and then join it to the table you have just brought in. They new two similar columns in order to do this. At that point, all the information from the table will be attached to the feature class you already have. Important to note is that if you move the table or delete it, all that information that was attached will disappear. You will need to right click and export the data in order to keep all the information together without keeping the txt, csv, Excel or dbase table in the same place.
If the table you are working with has a spatial reference (usually X, Y, coordinates) then once you import it, you will have to right click it, and click the “Display X, Y, data” From there you choose your latitude and longitude coordinates (hint: latitude runs side to side, is “x” and for the US is in the high 20s to mid 30s. Longitude is “y” and goes up and down, starts at the prime meridian and for the US is in the negative numbers.)
Sometimes you’ll have no choice but to play with the data in Arcmap. Here are a few tips I’ve picked up about concerning it:
Remember what you name your files and where you save them to!!
If you want to add another location to a feature class with simple X, Y, coordinates, you will have to reexport the data itself into a table (dbase, txt or any of the other options the GIS gives), and add the point manually, and then reexport that into your geodatabase. Be aware that this also tends to do some funky things with your column/field headers.
Frequently Asked Questions
What if you have csv or spreadsheet files so large that the whole thing cannot be opened in Excel? This usually happens when the data is extracted directly from SQL like processes. What I have found to be generally useful is to create an Access database and change the headers from there. It can be a little frustrating to learn, especially when you’ve never used Access database, but don’t give up! It is slightly different but the logic is the same.