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

[PanGIS]

Hi Jan,

do you need those 5 rows?

Because the table you will get will not display that merged field anyway, unless there is a way to do so and which I really don't know.

Maybe, if it is an important info you can add it into the Description of the Event Layer (temporarily) or better into the Description of new FC/SHP you create after you export the event layer.

[JanNielsen]

I don't need the rows but since the structure of the table is the same for all tables, it would be nice if I could streamline the process so I wouldn't need to manually delete the top 5 rows for each table. And if I could find a way to make the tool only read from row 6 and down I could create the layers from tables located on a server witout having to download each table and modify my own copy each time.

[PanGIS]

Ok, now it is more clear.

I believe a possible way to either delete or tell the software to start from row 6 is a script.

If so, Dan_Patterson‌ could help.

[AmyRock1]

If you're repeating the same process, you can easily create a macro in Excel to do the cleanup.  You'll need to move between cells with the arrow keys instead of the mouse, but once you have the process down, just turn on the macro recorder, go through the steps, then turn it off.  Then, you can link it to a custom button for ease of use. (You may need to add the Developer tab to access these functions).

[DanPatterson_Retired]

Working with excel files

You must adhere to the rules that are contained there

You can't even hide rows so that they are imported properly.

My advice is format your tables properly on the first page, then put the description information on the second page.

When you import the table, you will be able to use the first page.

Exporting to another format prior to bringing (ie csv etc) won't fix the problem... the problem is the way that the data are set up in Excel... a bigger problem is that Excel is being used as a database management system.

[JanNielsen]

It is indeed a hassle to work with the table structure from Excell, but unfortunately it is the way the reports are being generated. Perhaps a bit of background info is in order here. I work with rail track drainage. When replacing tracks etc, and new drainage is designed I use reports including information on existing wells, to which I might be able to connect in order to keep the rail track dry. These reports are super handy when importing them of the XY coordinates, since I can just click a point near to the place I'm designing new drainage and get all the relevent information from it. And then be able to model in my CAD tool.

But if there is no way to create the layer by forcing it to start looking from row 6, I will have to keep doing it one at a time.

[DanPatterson_Retired · Accepted solution]

In short ... No.  Any substitute would take as much time to accomplish the same goal

If you have to do this hundreds of times, then an alternate spreadsheet structure might help

[JanNielsen]

I think creating a new table where I can copy-paste everything from each induvidual spreadsheet, so I have them all in one go, and only need to create that one layer for an entire railtrack stretch, might be the way to do this best. It might take 15-20 mins for an entire project which is not that bad to be honest.

Thank you for your help in this matter Dan. I am amazed at the speed to which I got these questions answered

And thank you as well Pan Gis for hooking me up

[TedKowal]

Dan is correct .... but .....

One trick I use is a template macro to copy the data I want to another sheet... this is a vary straightforward excel macro....

In excel create a macro (This example will copy data starting on Row 5 down to Row 8 Column "C":

Sub PasteData()
    Dim rng1 As Range
    'this is where you want to place your data
    Set rng1 = Sheets("Sheet2").Range("A1")
    'Set your range as the starting Row to the last Row you want copied
    Sheets("Sheet1").Range("A5:C8").Copy _
            Destination:=rng1
End Sub‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍