topic Re: Creating XY Event Layer from .xlsx in ArcGIS Pro Questions

Creating XY Event Layer from .xlsx

JanNielsen — Thu, 04 Jan 2018 09:18:47 GMT

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

Re: Creating XY Event Layer from .xlsx

PanGIS — Thu, 04 Jan 2018 11:09:40 GMT

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.

Re: Creating XY Event Layer from .xlsx

JanNielsen — Thu, 04 Jan 2018 11:41:53 GMT

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.

Re: Creating XY Event Layer from .xlsx

PanGIS — Thu, 04 Jan 2018 11:48:40 GMT

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.

Re: Creating XY Event Layer from .xlsx

DanPatterson_Retired — Thu, 04 Jan 2018 12:46:24 GMT

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.

Re: Creating XY Event Layer from .xlsx

JanNielsen — Thu, 04 Jan 2018 13:25:41 GMT

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.

Re: Creating XY Event Layer from .xlsx

DanPatterson_Retired — Thu, 04 Jan 2018 13:30:12 GMT

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

Re: Creating XY Event Layer from .xlsx

JanNielsen — Thu, 04 Jan 2018 13:40:20 GMT

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

Re: Creating XY Event Layer from .xlsx

TedKowal — Fri, 10 Dec 2021 22:06:48 GMT

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Creating XY Event Layer from .xlsx

DavidAskov1 — Thu, 04 Jan 2018 23:56:54 GMT

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.

Re: Creating XY Event Layer from .xlsx

Todd_Metzler — Fri, 05 Jan 2018 13:14:18 GMT

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.

Re: Creating XY Event Layer from .xlsx

AmyRock1 — Fri, 05 Jan 2018 14:17:37 GMT

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).