By com


2010-12-04 11:33:36 8 Comments

Is there a way to export a table with selected records to an Excel spreadsheet?

I was trying to export just to .txt file but ArcGIS gives me an error message.

An error occurred exporting the table

The other problem I have is with coordinates. I want them only with one locus after the comma, I mean like the format: xx xx xx,x not like I have now xx xx xx,xxx.

alt text

The version of my software is 9.3.

5 comments

@If you do not know- just GIS 2014-06-12 13:36:59

Recent versions of ArcGIS, since 10.2 and we are now at 10.5, have a set of tools named Excel Toolset which has a Table To Excel tool which:

Converts a table to a Microsoft Excel file.

You can convert to excel directly from this tool. It does have some limitations. For example Excel 2010 onward supports 16,384 columns but ArcGIS will only export 256 when I last checked it.

I understand the need to go directly to Excel from a Geodatabase as using the DBF route will truncate your field names. If you are working in a shapefile I am not sure you gain much additional functionality.

See Jay's answer for the Parse command section of the answer as this still stands.

@CSB 2015-10-19 17:31:43

Should also note that you cannot export an attribute table with more than 65,535 records. (because it can only export to .xls, not .xlsx which can copy paste > one million records...)

@user25257 2017-01-03 20:21:40

I found this to be easier than using excel toolset. I had Tech support at ESRI actually tell me they thought imports and exports worked better when using .csv instead of excel. I don't know for sure if this works in Arc 9x, but it works for all 10x versions so far.

I found fewest problems following these steps:

  1. (before saving as .csv) you need to clean up data in excel. If you do this well, you will have fewer errors. Even 'invisible' formatting can cause issues.
  2. Assuming you have xy coordinates in the excel table, clean up your xy coordinates in excel. On data tab "text to columns" can help you split up coordinates into multiple columns, remove spacing, degree signs, commas, etc, then put coordinate pieces back together using "concatenate". It seems like decimal degrees or UTMs are the least problematic. Include minus sign in coordinates if applicable.

  3. clear all other formatting in Excel (Home tab (Office 2013), editing section, "Clear" has dropdown, select clear formatting). Again 'invisible' formatting such as number of decimal places, can cause issues.

  4. Manually remove all spaces or punctuation in field names, rename to 8 or fewer characters, *for 'blank' cells, remove anything like a hypen or other punctuation - NA or 0 seem to work best), then "save as" .csv
  5. Note that you can only save one active worksheet and that you will always get the warning that saving as a csv does not allow certain formats.....but it's okay. Save it.
  6. Close the csv, then go to that location in ArcCatalog
  7. Rt click the .csv in ArcCat and select "Create feature class" from XY table. 8. In pop up window, select x and y fields, z if you have it, and coordinate system, name it, and Arc will create a FC directly from your .csv.

In my experience, the invisible formatting caused the most issues - that is why the "clear formatting" is better than removing formatting by hand. Hope that helps. (Hope it works in 9x)

@angisti 2016-12-03 09:12:50

in attibute table select exports... menu, choose export to dbf file, you can open file in excel, here clip for you https://www.youtube.com/watch?v=HwJU-oiF5T0

@Brad Nesom 2010-12-04 14:28:24

the export has a bit of a bug I have found. If you export, then select the browse for the file location, then type in the file name the default "of type" is set to feature class or something. You need to select the type of dbf table. Then you would need to open and saveas in excel to xls.

@Brad Nesom 2010-12-04 14:30:09

You can set the decimals to display for the field.

@dassouki 2010-12-04 15:01:58

On a side, note, I moved from QGis to ESRI (yes, I know the opposite way to most people), and there is nothing in the Arc products that doesn't have a bug of some sort. reminds of a continuous product cycle of windows me

@com 2010-12-04 16:52:09

Ad Brad Nesom: I can't because the field is not a numer type (it contains not only numbers)

@Jay Cummins 2010-12-04 12:05:33

Do you receive the same error by copy/pasting records?

This is from memory, but if you right-click on the gray row indicator box, your menu should have "Copy Selected Records". Then you can paste directly into an Excel spreadsheet. alt text

A caveat: if your selections are in a related table that are a result of a feature selection, you might actually have incorrect selections (you get rows in your related table that aren't actually related to any of your selected features and you could be missing others). One of my users discovered this issue, which appears intermittent. I did narrow down the issue to ArcMap. The geodatabase+custom ArcObjects test code works correctly. Our workaround: if our users need the related data for something important and the number of records is too large for them to visually inspect, we obtain the data from the database using a sql query. We haven't seen this issue with ArcGIS 10.


Update: You said you got the data into Excel. Are your DMS values in a text field? You might be able to parse the information that you need and round your decimal value. based on what I see in your screen shot, i came up with:

=LEFT(A1,8) & ROUND(VALUE(MID(A1,9,6)),1) & MID(A1,15,2)

alt text

http://dl.dropbox.com/u/1156285/example.xls

@com 2010-12-04 12:31:40

Great! It's working :) What about the second question about coordinates? Now i was trying to set it directly form exel but i don't see any format for coordinates...Any suggestions?

@Jay Cummins 2010-12-04 12:39:40

I'm not at a workstation, so I don't know of anything from memory. Hopefully, someone else who knows that answer will post it. Your second question should be a second stand-alone question and it's likely a future answer-seeker would find answers to that second question useful. You could probably use your same screen shot.

@com 2010-12-04 14:22:52

It is not working. It gives back #ARG error in spreadsheet. The best would be to do it in Arc, but I;m not sure if it is that possibility? Maybe i should ask another question for this one as Jay said...

@matt wilkie 2014-05-07 18:41:43

for copy/paste route also see Copy, not export, a table from Arcmap which has methods for newer version of Arcmap as well.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Error exporting attribute table in ArcMap to Excel

2 Answered Questions

[SOLVED] Exporting to Excel gives ERROR 000732?

3 Answered Questions

4 Answered Questions

1 Answered Questions

[SOLVED] Exporting attribute table that has many rows in QGIS

1 Answered Questions

2 Answered Questions

[SOLVED] Coordinates format in ArcGis 9.3

Sponsored Content