Make XY event layer based on excel spreadsheet, result event layer doesn't show anything on the map

1994
6
04-18-2016 11:43 AM
Highlighted
New Contributor

Hello everyone,

I use MS access firstly build a query to format the excel data with lat lon fields and 32377 records and create a table and then export the table as excel spreadsheet (.xlsx) file.

After opening this exported spreadsheet in excel, I save it as .csv file.

Part of data shows as follows:

CLIENTCODEPROVIDERNAMEP_STREETNOP_ONSTREETP_CITYP_ZIPCODELATLONCountID
P100006WEST675W NORTH AVEMELROSE PARK6016041.90832-87.842221
P100006WEST3906GEORGINA LNBELLWOOD6010441.87987-87.881732
P100099SOUTH281COMMONS DRCHICAGO RIDGE6041541.71708-87.783313
P100099SOUTH8450S HARLEM AVEBRIDGEVIEW6045541.73791-87.799314
P100099SOUTH11800SOUTHWEST HWYPALOS HEIGHTS6046341.67812-87.813115
P100106SOUTH3060W 127TH STBLUE ISLAND6040641.66244-87.696326
P100106SOUTH17680KEDZIE AVEHAZEL CREST6042941.56843-87.694417
P100106SOUTH17780KEDZIE AVEHAZEL CREST6042941.56843-87.694418

Then I open it in ArcGIS, use Make XY event layer tool to create a point event layer, use GCS_WGS_1984 geographic coordinate system, when the message shows completed, however, only one point shows on the map (should show 32377 points totally) and only one related record in the attribute table.

I've tried export table from MS access to .dbf format, same situation.

Does anyone encounter similar situation and know how to solve this problem? Please help!

Thanks!

Reply
0 Kudos
6 Replies
Highlighted
MVP Honored Contributor

There are lots of possible problems moving Excel to ArcGIS, but I'd say the most common one is that your field names have either spaces (even trailing spaces) or special characters in any of your field names. Make the field names as simple as possible and try again.

Reply
0 Kudos
Highlighted
MVP Frequent Contributor

Have you tried using the ms access data directly. If this is all in a personal geodatabase, there is no need to export to anything. Just add the table to ArcMap and create a XY event layer, then export data.

If you have data in a db already there is no need to export to excel, dbf etc beforehand.

Have you zoomed in on your "1" point? Perhaps the extent is so zoomed out all you see is one tiny speck of data.

What is the coordinate system?

Reply
0 Kudos
Highlighted
MVP Honored Contributor

Where is the single point located? Is it in the approximate expected location? Are the latitude and longitude fields formatted as decimal degrees or degrees/minutes/seconds? Can you post a subset of your table?

Reply
0 Kudos
Highlighted
New Contributor
CLIENTCODEPROVIDERNAMEP_STREETNOP_ONSTREETP_CITYP_ZIPCODELATLONCountID
P100006WEST675W NORTH AVEMELROSE PARK6016041.90832-87.842221
P100006WEST3906GEORGINA LNBELLWOOD6010441.87987-87.881732
P100099SOUTH281COMMONS DRCHICAGO RIDGE6041541.71708-87.783313
P100099SOUTH8450S HARLEM AVEBRIDGEVIEW6045541.73791-87.799314
P100099SOUTH11800SOUTHWEST HWYPALOS HEIGHTS6046341.67812-87.813115
P100106SOUTH3060W 127TH STBLUE ISLAND6040641.66244-87.696326
P100106SOUTH17680KEDZIE AVEHAZEL CREST6042941.56843-87.694417
P100106SOUTH17780KEDZIE AVEHAZEL CREST6042941.56843-87.694418
Reply
0 Kudos
Highlighted
MVP Honored Contributor

And you're using LON=x, LAT=y, and not the other way around?

Highlighted
MVP Honored Contributor

These are how those points display when I imported them. Note that I set the coordinate system.

test.png