nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

ArcGIS 10.1: MakeXYEventLayer-Extra Excel points appeared in attribute table. Why?

ScottChang

Hi all,
In the IDLE Python (GUI) of my ArcGIS 10.1, I executed the following Python-ArcPy script:

# scNavyAnnexMakeXYlayer.px  [X,Y-coodinates in the xls for 80 points of boring locations] # Description: Creates an XY layer and uses CopyFeatures to save it to a layer file (Used ArcGIS 10.0) # Author: ESRI - modified by Scott Chang per Arkadiusz Matoszka & Wayne Whitley (Date:  31 Oct 2012)  # ******* The old script was corrected & this new script executed in ArcGIS 10.1 by Scott Chang on 30 Jan 2013 ******  # import system modules  import arcpy from arcpy import env   try:     # Set the local variables     # in_Table = "firestations.csv"     tb = r"C:\TEMP\Boring_Locations_WGS84_Attributes.xlsx\Boring_Locations_WGS84_Attribut$"     xc = "POINT_X"     yc = "POINT_Y"     zc = "POINT_Z"      out_Layer = "Pointlocations_layer"     saved_Layer = r"c:\TEMP\NavyAnnex80BoringPoints.shp"    # not .layer!!!       # Set the spatial reference     # spRef = r"Coordinate Systems\Projected Coordinate Systems\Utm\Nad 1983\NAD 1983 UTM Zone 11N.prj"     spRef = r"Coordinate Systems\Geographic Coordinate System\World\WGS 1984"        # Make the XY event layer...     arcpy.MakeXYEventLayer_management(tb, xc, yc, out_Layer, spRef)       # Print the total rows     print arcpy.GetCount_management(out_Layer)       # Save to a layer file     # arcpy.SaveToLayerFile_management(out_Layer, saved_Layer)     arcpy.CopyFeatures_management(out_Layer, saved_Layer)       # New!!!  except:     # If an error occurred print the message to the screen     print arcpy.GetMessages()

I put the X,Y&Z coordinates of 80 points in the Excel .xlsx table as input. It worked. But it gave me the output with 9 extra Excel points of "Null" value I did not expect - see the attached file. I don't know how to avoid these extra Excel "NULL" points from entering the MakeXYEventLayer_management process!!?? Please kindly help and tell me why the extra Excel "NULL" points were read in the input and how to avoid it from happening.

Thanks in advance,
Scott Chang

scNavyAnnexMakeXYlayer-Extra.docx

Screenshot 2025-11-03 112713.png

Find more posts tagged with

Accepted answers

T__WayneWhitley

Hmmm, offhand I don't know since I don't mess around much with the Express version (maybe I should) and I don't actually have 10.1 installed yet (I'd better get with it!). I do a lot of prepping before I go installing next versions because it isn't just me affected, so when there are problems, as an admin I'd better have a ready answer now and again. (I try to have answers all the time like most of my users expect....alas, that is simply not realistic.)

At any rate, I try to read ahead as much as possible, so maybe - in case you haven't gone here already for assistance - you should try the webhelp. I find out sometimes at least that is a place to look for faulty information...just kidding, the webhelp has improved immensely, just try it, here's your source on Express and your 'Database Server' connections:

Connecting to a geodatabase in SQL Server Express
Desktop » Mapping » Working with ArcMap » Working with the Catalog window
http://resources.arcgis.com/en/help/main/10.1/index.html#/Connecting_to_a_geodatabase_in_SQL_Server_Express/00660000044q000000/

That's the best I can do for now -- think your original question was answered, so if you would please, mark it so.

Interesting questions though, and if you need to re-post a new question, please feel free.

Thanks,
Wayne

EDIT:
Scott, this (below) may be a better place to start, quite a few subtopics in here related to SQL Express--
I have experimented with Express, but not with 10.1 -- things may very well have changed.

Installing SQL Server Express
Geodata » Administering geodatabases » Administering geodatabases on database servers
http://resources.arcgis.com/en/help/main/10.1/index.html#//018t00000010000000

All comments

T__WayneWhitley

Scott, I posted the relevant help ref there at the other post. Probably a brief explanation is warranted here.

ArcGIS doesn't quite read Access data as 'automatically' as Excel data. If you want to maintain data in Access 2007, that is doable....however, you'll need to set up a special connection in Catalog - and that is actually a good thing, because then all you'd need to do in your script is modify the input table pathname to reference the connection file + the table name much like you would any other connection (for example for an sde connection, you would use the connection file pathname [or the shortcut Database Connection filename shortcut reference] + the table/fc name).

Connecting to a 2007 Microsoft Access database (.accdb) in ArcGIS
Geodata » Data types » Tables » Creating and editing tables
http://resources.arcgis.com/en/help/...00000024000000

If you're going with this means, it is good because you are maintaining 2 'worlds' of data, editable in the Microsoft 'world' and readable in the ArcGIS 'world' so that you can further process it... make sense now?

The webhelp should really guide you...

Hope it works out okay. I particularly like you interest expressed earlier about starting up, SQL Express. I advise you not to abandon that approach...could help you in the longer run.

Wayne

ScottChang

Hi Wayne, Thanks for your nice response.

I don't have the SP1, etc. in my "Windows 7" PC - I just used the newly intalled ArcGIS 10.1 (our Computer Team did the installation for me).

I just drafted a new Python script to execute the MakeXYEventLayer by using the Access 2007 accdb file. I ran it and I got Error 732. I posted this new thread in this Forum minutes ago. Please go to that post and help me to resolve the Error 732.

Thanks,
Scott Chang

T__WayneWhitley

Just noticed your new post...sorry for the delay.
I noticed in your script, the tb variable you have set for your input xlsx table is set to Boring_Locations_WGS84_Attribut$, so is that one of your existing worksheets, i.e. before you defined the new range?

When you define a print area or otherwise explicitly define a range (as in a range by name in Excel 2007 described earlier), you then have that 'recognized' in ArcGIS as a new table (or 'table view', if you want to call it that) - for example, you will see that new 'table view' show up after refreshing the view in ArcCatalog (or try adding it to ArcMap). It is best to save and exit the xlsx first so no 'locking' occurs to interfere with a fresh read of the data.

EDIT: Right, I've added this statement in case it isn't clear what to do in your script. The paragraph immediately above this statement serves to demonstrate the new 'table view' and how ArcGIS recognizes it. What you need to do in the script is modify your tb pathname to point to the newly recognized table, whatever it is called in your instance.

Start with that, believe that will solve your Excel problem, but test 1st...

As for your 2nd question, the 'extra' ID I think you are referring to is the database OBJECTID (or OID) that is normal db table behavior - you don't want control of this and indeed it is not user-editable (not editable by you). Why not, you may ask? In short, this is needed to enforce internal database integrity and maintenance-type tasks....the 'database manager' needs this to do its job.

If you need your own user-defined ID you may do that as well I suppose, but I question where you are going with this. Sequential IDs sometimes fit the task if that's what you need them for and there's no other ready means to define them, but actually if all you need is a means of, say, defining uniqueness of a location (as a GPS station collection), then for most users it makes more sense to define a more 'sensible' station ID -- if you will, something like "Station 7"; it isn't necessary to have an integer field as your own unique identifier. May be getting off on a tangent here --- if you must use Access for the input table, I would think you could import it directly from Excel - not sure what difficulties exactly you are having... I have occasionally imported tables in Access directly from Excel with no worries (except for when the Excel rows are unruly). But the only reason I've ever really had to do this is when someone needs something where Access does the job better than Excel (such as the additional need for joining tables).

Hope that helps. Actually I think you best bet is to as directly as possible, convert to whatever final output format you need. Probably the single most common reason I ever maintain or copy a data table in an 'intermediate' format is when a user cannot read it from it's final converted resting place or repository. If nothing else, just remember it's generally bad practice to make multiple copies because then you may inherit the headache of redundant data you have to keep current with one another...

If I have rambled here, I strongly apologize as there is apparently not enough coffee at this late stage in the week to keep all in good short order! Further questions, let me know. Also, post back here after your testing is complete... I don't have 10.1 installed yet and would like to know how it goes. Just wondering, do you have SP 1 installed as well?

Enjoy,
Wayne

ScottChang

Hi Wayne, Thanks for your responses.

1) In my Excel 2007 xlsx file, I tried (i) deleting the extra "Null" lines, and (ii) specifying the "Print" area of my input data. and then executed the same Python-ArcPy script. I still got the extra "Null" lines in the Attribute Table. What did you do to avoid the extra "Null" lines after your input data in your Excel?

2) I am thinking to use my Access 2007 to create the .accdb file as an input data. I have following things to ask you about using Access 2007 to create the accdb file: (i) when I launch the Access 2007, the ID column has "New" appeared and number assigned by the Access 2007 program (not user) randamly - see the attached file. This randamly appeared "ID" numbers bother me greatly, because I have no control over them for creating the orderly sequence starting from "0" or "1" I need!!!??? Do you know how to start the number of "ID" column from "0" or "1" in Access 2007? (ii) After I typed in the Pt_ID, X, Y, Z and populated these columns with appropriate data, I tried to save it. It always responds in the following ways: (a) it asks me to change "Table 1" to the name I want, and (b) it will save my current file to the Directory C:\Users\e1enxshc\Documents\ folder (in my "Windows 7" PC). This bother me too. How can save my Access 2007 accdb files to the folder in C drive where I have my geodatabase created already there? (c) In the Python-ArcPy script, the Excel 2007 xlsx file is coded: tb = r"C:\Tem\APGmmrp.xlsx\APG10points$" before X-, Y-, and Z-coordiades are read. How can I code the Access 2007 accdb file for the tb (in_Table of Python script)?

Please kindly help, respond again, and give me the answers for 1) and 2).

Thanks,
Scott Chang

askWayne-accdb_1Feb2013.docx

T__WayneWhitley

ScottChang

Hi Wayne, Thanks for your nice, valuable response.

Yes, I had a silimar problem in reading text file before.   It was my start to use ArcGIS 10.1 to read my input data in my Python-ArcPy programming. My target goal is to use ArcGIS 10.1 to read my input data from (1) Microsoft SQL Server 2012 Express and (2) regular Microsoft SQL Server 2012 recently installed in my "Windows 7" PC.

I used to employ Microsoft SQL Server 2008 Express (aka Micosoft SQL Server 2008 Management Studio) in ArcGIS 9.3/10.0 in Microsoft XP Pro PC briefly before.   Now I have ArcGIS 10.1, Microsoft SQL Server 2012 Express and the regular Microsoft SQL Server 2012 recently installed in my "Windows 7" PC. I don't have the icon of Microsoft SQL Server 2012 Express on the screen of my PC. But I saw the following things in my ArcCatalog 10.1:
             Database Servers
                    Add Database Servers
                    NAB-WK-01234567_SQLEXPRESS

I have a hard time to get my SQLEXPRESS started, and my input data stored and executed by Python scripts. Could you kindly please help and advise me how I can get the SQLEXPRESS of my Microsoft SQL Server 2012 Express started and my input data stored?

Please respond.

Thanks again,
Scott Chang

T__WayneWhitley

Quick example of what I am talking about--- (and this probably doesn't warrant any pictures)

Think of reading tables into ArcGIS from Excel as reading 'virtual tables'. For example, sheets 1, 2, and 3 are read in by default (I am using Excel 2007); however, I can add a print range that may or may not align with my data and, as well, I can add a name of a range as defined in Excel that can coincide with, say, a blank area on a worksheet. It doesn't matter, ArcGIS is going to assume there's data there and format extra columns/rows accordingly.

So, as a test, I put in a single row of data in 4 columns, then purposely defined a range by name in Excel 2007 in this fashion:

1- Activate Formulas tab...
2- In the Name Manager, select Define Name...
3- Type in desired text for a name to refer to a corresponding range...
4- Select the range on the worksheet (I limited the scope to Sheet1), either interactively or type it as a formula, e.g.:
=Sheet1!$A$2:$H$50

Then if using the named range in a Table To Table conversion, the 4 column headers will be read. Additionally, since reading the 'virtual table' as defined (=Sheet1!$A$2:$H$50) and referenced by name, then A - H is read and as a result, empty columns E - H (4 extra columns).

Again, due to the 'virtual table' read by the named range, in similar fashion rows 2-50 are read in (or 49 rows)-- even though, as I said, I only entered data for 1 row. So that is 48 'extra' rows.

Hope that is now clear. Interesting behavior.

Enjoy,
Wayne

T__WayneWhitley

Scott,
If I am not mistaken, this has to do with how ArcGIS is reading the table from Excel -- think you may have had similar problems before reading extra columns? Similarly, ArcGIS 'thinks' there are more rows in the table than there actually are - believe I have had similar experience in converting an excel spreadsheet using Table To Table, a number of 'extra' empty records.

If memory serves, you can 'define' the table extent in Excel, or do something like set the print area, then ArcGIS can pick up on those extents....and that is the 'table definition' you want to read into processing. Otherwise you can simply delete the extra records, but thought you deserved a kind of explanation. Remember Excel does not represent true database tables, not in the normal sense, so it is actually a kind of 'view' the drivers convert for you in ArcGIS....not a perfect process. ...just a little further info, if you were to read in an Access table where there is tighter conformity, I believe you'd never experience this phenomenon.

Enjoy,
Wayne

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.