ArcMap concatenates feature class fields when copy/pasting to Excel

470
8
01-22-2021 12:30 PM
Labels (1)
RickBombaci
New Contributor III

I'm using ArcMap 10.8.1 and Excel 2010. I frequently copy and paste records from attribute tables into Excel in order to perform calculations.

Generally, this works fine, but sometimes the pasted data, which (for example) may consist of 20 different fields, is concatenated into a single very long text string for each record. These concatenated fields can be very difficult to parse in Excel.

I have not been able to determine what causes this to happen. Anyone else observe this obnoxious phenomenon? Can anyone think of a fix?

Thanks for thinking about this.

Tags (3)
8 Replies
JoeBorgione
MVP Esteemed Contributor

Why not use Table to Excel  on the selected records so you don't have to rely on copy and paste?

can't wait to retire....
RickBombaci
New Contributor III

Thanks for the suggestion. I know I could, but that actually takes more time than copy/paste when copy/paste works properly. And usually copy/paste does work properly.

 

0 Kudos
ClayDonaldsonSWCA
Occasional Contributor

Are there tabs or newlines in the long text? I have found both do cause issues when copying and pasting.

The alternative would be to use Table to Excel as Joe mentioned. Alternatively, you could use python and pandas if you want more customized outputs.

RickBombaci
New Contributor III

There's nothing unusual in the attribute table. No tabs, no newlines.

0 Kudos
DanPatterson
MVP Notable Contributor

or blanks in one of the columns, it may treat the two fields as one.


... sort of retired...
RickBombaci
New Contributor III

I don't think blanks in one of the columns can be the cause, because it's not just two fields that are getting concatenated as one. All twenty fields are getting concatenated into one long text string.

0 Kudos
Mary_Murphy
Esri Contributor

Are you using the "Copy Selected" option from within the table in order to copy the items to the clipboard?

If so, when you double click into a cell in Excel, then paste, it will typically paste everything into one cell. However, if you click once on the cell that will be the origin cell [e.g. A1] in Excel > then paste, it SHOULD paste the individual columns. 

RickBombaci
New Contributor III

I always use the second method you described:  Click once on a cell in Excel, then right-click and choose paste. You're right, it SHOULD paste the individual columns. I'm wondering if this is something that cropped up in 10.8.1, because until recently I was able to consistently use copy/paste without a problem. However, it now seems to consistently not work. At first I thought maybe it made a difference whether I was copying from a shapefile vs. a feature class, but that does not seem to be the case.

0 Kudos