Creating XY Event Layer from .xlsx

[JanNielsen]

Hi all. I'm currently trying to create an XY event layer from an excel table. The table has the top 4 rows merged in which the description of the content is displayed. The fifth row is subheaders, and finally in the 6th row the data is displayed, including the fields with X and Y coordinates.

I can't seem to create the layer, without having to delete the first 6 rows from the table, making the X and Y data being displayed from row one.

I'm wondering if there is an option to tell ArcGIS that the event layer should only start reading X and Y coordinates from row six, instead of row one.

Regards: Jan Holdorff Nielsen

[DavidAskov1]

Let's say you start with this:

You can create another worksheet. In cell A1, type = to start a formula, open the other worksheet, and click on the cell you want to be in the upper left. The formula will look like =Sheet1!A5, assuming "Sheet1" is the name of the worksheet with data and cell A5 is the first one you want.

Then, with cell A1 selected, select as many to the right as the columns you want. Hit CTRL-R to fill to the right. Select down as many rows as you want and hit CTRL-D to fill down.

Use this worksheet when you import.

If you want to just fill down a bunch of rows without worrying about the number of rows on worksheet 1, you can do this:

=IF(LEN(Sheet1!A6)>0, Sheet1!A6, "")

It will give you a blank cell if the reference cell doesn't have any length.

[Todd_Metzler]

Jan,

In the long term, suggest reversing the workflow.  In other words, when your field collection/reporting teams go out to collect data, provide them with a table in a schema that supports your GIS needs.  Then when you get those data back it requires little or no conditioning.

In the short term, try the table to table or excel to table tools and only select the column you want in your export GIS suitable table.  Once you're in GIS table space then you can use scipting or field calulator to manipulated/condition those data for display as spatial content.  Remember to set a geographic datum when working with table based x, y or you may get an undesirable result.