topic Re: Adding lots of excel files with X,Y data + attributes in Data Management Questions

Adding lots of excel files with X,Y data + attributes

AdamLee1 — Mon, 15 Sep 2014 06:57:07 GMT

I think my problem is quite simple and I might have just overlooked the solution but here it goes. I will try to explain the situation to the best of my ability.

I have 300 Excel Workbooks with 3 worksheets in them, each containing different types of data. Of the 3 sheets only Sheet 1 matters and it contains X, Y data in the first 2 columns followed by 2 attributes (name, and description). All of the workbooks follow the same format.

Now I'm trying to add in these X,Y coordinates into ArcMap and then convert them into a single shapefile.

I'm looking for a way to automate this or do it quicker.

I could always add in X,Y data but as you can imagine it would take forever with so many Workbooks

Re: Adding lots of excel files with X,Y data + attributes

RiyasDeen — Mon, 15 Sep 2014 11:00:37 GMT

Hi Adam,

Attached script will do the job.

Re: Adding lots of excel files with X,Y data + attributes

AdamLee1 — Mon, 15 Sep 2014 17:13:52 GMT

Hi Riyas,

That definitely helped and did the trick but I was wondering two things:

1. Is it possible to get the other two associated attributes? Running the script I get the XY points but without context their just random points.

* I believe I would achieve this by having a shapefile with the associated attributes already created?

2. Could you briefly explain the script a bit? I'm a bit new to Python and such and would love to understand it more.

Hope I'm not asking to much.

Re: Adding lots of excel files with X,Y data + attributes

NettoChad — Mon, 15 Sep 2014 21:36:59 GMT

Thank you Riyas,

I new to Python too! and can use this!

I can try to explain it a little bit...

-The first part (6 lines) is importing your libraries and setting up all your variables.

-The loop starts (line 😎 iterating though the list of "*.xls" files in the workspace folder

-look at the first sheet in the excel file (line 11)

-line 14 and 15 are getting the names the first two fields.

-line 18 is creating the points

-line 19 is adding the points to the new feature class (targetFc)

Re: Adding lots of excel files with X,Y data + attributes

RiyasDeen — Tue, 16 Sep 2014 00:25:20 GMT

Hi Adam,

Yes, if you setup your shape file to have attributes similar to your Excel attributes you should be good.

If you can't do that for any reason, then you can use field mapping as fourth parameter for append tool to map your Shape and excel attributes.

ArcGIS Help 10.1

Netto's explanation is spot on.