ERROR 000212: Cannot create XY event source Failed to execute (MakeXYEventLayer)?

Question

I have a csv file that is 133mb in size and has over 1.3 million lines of data. Each line of data has its own lat/long and I was wondering what would be the best way to display those points based off the lat/longs in ArcGIS Desktop 10 and ultimately turn it into a point shapefile?

So far I have tried the "Make XY Event Layer" tool but that keeps failing...

ERROR 000212: Cannot create XY event source Failed to execute (MakeXYEventLayer).

The csv file is properly formatted and the lat/long fields are numeric so I have no idea why it keeps crashing.

Here is what the first 2 lines of my CSV look like, the first line is what should be the header:

"LAT","LONG","CUSTOMER_MASTER_ID","STORE_NBR","TRANSACTION_DT","SKU_DIVISION_ID","SKU_DEPARTMENT_ID","SKU_CLASS_ID","SKU_CATEGORY_ID","SKU_NBR","SALES_AMT"
"32.363544","-110.969778","2000000792627","2940","8/11/2010","2060","3920","5120","84021","5127866","13.99"

Any ideas?

Answer 1

Since the Lat and Long field values "32.363544","-110.969778" have quotes around them, they aren't really treated as numeric.

The display x-y data needs the x and y fields to be numeric.

Remove the surrounding quotes and it should work. If you have access to a unix machine grep/sed sure would make this job easier. There are also windows ports of these tools.

Also, I'd avoid using "Long" as a field name - there might be cases where it is treated as a reserved word.

Answer 2

Ths may be a bit more complicated, but if my two-cents are worth anything (and if you're using MS Office products), I'd recommend importing your .csv into an MS Access .mdb database as a table. (Note: there is a 2GB size limit for an .mdb database).

You can then add that Access .mdb table into your ArcMap document and do a right-click > "Display x,y data":

By keeping your data in a database and reading from that table to display your x,y data points, you can make changes to your data and those changes will automatically be reflected in the x,y data points the next time you refresh the map display instead of having to create a new shapefile or layer view every time. This also seems like a more robust way to manage such a large amount of data.

Answer 3

Do you have a header line in the CSV file that might be confusing ArcGIS?

Also, try copying just one of the lines from the CSV and see if it works - that way you can eliminate formatting errors. Use a text editor like UltraEdit or Notepad++ which are capable of opening large text files, to copy your sample line.

I don't know how the ArcGIS importer works, but you may find the file is too large for it to cope with, so it may need splitting into chunks.

Answer 4

If you've got SQL Server Express, you might be able to do some of the heaving lifting in there.

This is cobbled together from a couple of different queries (where the lat/long were text fields):

SELECT 
CONVERT(float,[latitude]) lat
,CONVERT(float,[longitude]) long
INTO TEMPPTS
FROM
OPENROWSET ('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\temp;',
'SELECT * from GPSQuery.txt') g
WHERE longitude IS NOT NULL AND len(longitude) > 0
and [latitude] IS NOT NULL AND len([latitude]) > 0

You might need to configure sql server for Ad Hoc Distributed Queries.

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

It might be overkill for 1 job, but worth it if you are frequently dealing with a lot of different files. Although with the big files, you might want to look at bcp.exe (http://msdn.microsoft.com/en-us/library/aa174646(v=sql.80).aspx) for the bulk import.

Answer 5

If you just want to create a shapefile from it, try QGIS www.qgis.org The Delimited text plugin that can be loaded from withing QGIS makes this easy. If you then want to bring it into ArcMap just ensure the projection first via ArcCatalog.

Answer 6

Also make sure you titles have no spaces. For example "UTM Zone" should be "UTM_Zone" Just something to keep in mind, as it was a problem for me when Importing.