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

[LishanChen1]

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:

CLIENTCODE	PROVIDERNAME	P_STREETNO	P_ONSTREET	P_CITY	P_ZIPCODE	LAT	LON	Count	ID
P100006	WEST	675	W NORTH AVE	MELROSE PARK	60160	41.90832	-87.8422	2	1
P100006	WEST	3906	GEORGINA LN	BELLWOOD	60104	41.87987	-87.8817	3	2
P100099	SOUTH	281	COMMONS DR	CHICAGO RIDGE	60415	41.71708	-87.7833	1	3
P100099	SOUTH	8450	S HARLEM AVE	BRIDGEVIEW	60455	41.73791	-87.7993	1	4
P100099	SOUTH	11800	SOUTHWEST HWY	PALOS HEIGHTS	60463	41.67812	-87.8131	1	5
P100106	SOUTH	3060	W 127TH ST	BLUE ISLAND	60406	41.66244	-87.6963	2	6
P100106	SOUTH	17680	KEDZIE AVE	HAZEL CREST	60429	41.56843	-87.6944	1	7
P100106	SOUTH	17780	KEDZIE AVE	HAZEL CREST	60429	41.56843	-87.6944	1	8

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!

[DarrenWiens2]

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.

[NeilAyres]

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?

[KenBuja]

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?

[LishanChen1]

CLIENTCODE	PROVIDERNAME	P_STREETNO	P_ONSTREET	P_CITY	P_ZIPCODE	LAT	LON	Count	ID
P100006	WEST	675	W NORTH AVE	MELROSE PARK	60160	41.90832	-87.8422	2	1
P100006	WEST	3906	GEORGINA LN	BELLWOOD	60104	41.87987	-87.8817	3	2
P100099	SOUTH	281	COMMONS DR	CHICAGO RIDGE	60415	41.71708	-87.7833	1	3
P100099	SOUTH	8450	S HARLEM AVE	BRIDGEVIEW	60455	41.73791	-87.7993	1	4
P100099	SOUTH	11800	SOUTHWEST HWY	PALOS HEIGHTS	60463	41.67812	-87.8131	1	5
P100106	SOUTH	3060	W 127TH ST	BLUE ISLAND	60406	41.66244	-87.6963	2	6
P100106	SOUTH	17680	KEDZIE AVE	HAZEL CREST	60429	41.56843	-87.6944	1	7
P100106	SOUTH	17780	KEDZIE AVE	HAZEL CREST	60429	41.56843	-87.6944	1	8

[DarrenWiens2]

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

[KenBuja]

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