0

I'm trying to import an xlsx file into ArcGIS (10.2.1, everything is fully licensed and activated), and convert it to a shapefile for further analysis.

The xlsx file is ~180mb, and contains just under 600,000 records.

  1. I can add the xlsx file into ArcGIS. This generates the appropriate table correctly.
  2. I can create events from the x and y coordinates. This displays the data on the map correctly.

But...

  1. When I go to convert the events to a shapefile by exporting the data, everything fails on me. For some reason when it exports the events, it totally ruins the table (skips columns, misreads fields, ends up with data all over the place, etc.).

I can't see how it is a memory issue, as the computer has dealt with much larger pre-existing shapefiles before that have over 2 million points in. It just seems to be an issue with the export process. ArcGIS does not display any error message, it says it's completed the export process without any issues and asks if I would like to add the new file to the map. There is nothing to indicate that there's a problem until I go to open the table for the new shapefile it supposedly generated.

I've tried converting the xlsx file to csv, and run into the same issue. I can open the csv, generate the events to display the x,y data, but when I go to export the events to an actual shapefile, it destroys the table.

Any ideas? Is there an alternative way to generate the shapefile?

  • 1
    This is really common when using Excel data source. Do you have MS Access? Create a personal geodatabase with ArcCatalog, exit Catalog, open the database with Microsoft Access then import the XLSX table (be sure to not use bad table names when importing) - ensure the field names don't have spaces or punctuation marks etc.. then you can use the table from the geodatabase and it shouldn't flake out. If you don't then export the sheet to CSV format, it's plain text and isn't as likely to have the same problem - but is still sometimes unstable. – Michael Stimson Apr 14 '15 at 5:40
  • Neither method worked. Everything is fine until I try to create the feature class from the geodatabase, then it fails and corrupts the table. This happens regardless of the Access method you describe, using a csv, or using Excel. – Arianne Ford Apr 14 '15 at 6:47
  • What if you first export the Excel sheet to a gdb/dbf table with the Excel to Table tool? – GISGe Apr 14 '15 at 7:10
  • I will suggest you to save it as 'CSV'. then Do the things in what you got failure. – GIS Data Butcher Apr 14 '15 at 8:14
  • If you read the original post, you'd see I checked whether it worked for csv before making the request for help...It didn't work. – Arianne Ford Apr 14 '15 at 9:23
0

Can you try the converting the data in QGIS? or any other GIS software? this way you can make sure the problem really lies with ArcGIS rather than your data, and you have the shapefile to boot.

  • Ok, problem found. I installed QGIS and converted the file to a shapefile from within there. Despite the original xlsx/csv files only being ~200mb each, the dbf that is generated in the shapefile export process is over 16gb. It's a memory allocation issue. Seems I will not be doing this analysis in ArcGIS. – Arianne Ford Apr 14 '15 at 22:06
  • @ArianneFord Wait, you generated a (final) shapefile with a 16GB dbf? If so, no wonder it failed. A valid shapefile can't have a component file over 2GB. QGIS isn't going to solve that problem, it's inherent to the shapefile format. You'd need to be working in a geo/spatially-enabled database, regardless of what software you use. Arc can do it, just not with a shapefile (or a personal geodatabase, it'd have to be a file geodatabase). Some of the field types and definitions might be leading to bloat in that database. – Chris W Apr 15 '15 at 1:29
  • I imported the csv into QGIS, and exported it to a shapefile. The dbf it generated in the export process is over 16gb. I didn't even try to open it in ArcGIS once I noticed that. I'm working with the imported csv in QGIS to do the basic analysis, and can export the results as separate individual files which will be much smaller and manageable. That being said - one of the subsets of the original dataset that I queried out in QGIS and saved as a shapefile has a dbf of 6.5gb, and it opens in ArcGIS (working with it right now), so are you actually sure the 2gb limit is accurate? – Arianne Ford Apr 15 '15 at 1:38
  • This is open and working at 6.5gb: [link]dropbox.com/s/1vpfc217h21rb3k/Geochem.jpg? – Arianne Ford Apr 15 '15 at 1:52
  • @ArianneFord Yes, quite sure the 2GB limit is accurate. I have run into the issue when given a address dbf just over 2GB that couldn't be linked to the parcel shapefile it was supposed to accompany. I'm also aware that QGIS will do things even if they're invalid in some cases. I would be highly suspicious of that data and do a lot of checking to make sure it's all there and really doing what it appears to be - including during future analysis/geoprocessing. – Chris W Apr 16 '15 at 1:28
0

Just clean your spreadsheet. Convert to table and do the steps you just did. You don't need to convert to MS Access if you do it right. You can follow this workflow like this question I answered not so long ago: https://gis.stackexchange.com/a/135684/34877

  • 2
    Oh, and you need to be really careful with field names. No spaces, no strange characters... – Albert Apr 14 '15 at 6:42
  • Yes, thanks to both of the above responses. Neither worked. I've been working with GIS for years, so know about correct fieldnames etc. The Access method didn't work. When I went to convert the imported data to a feature class from the geodatabase, it ended up doing exactly the same thing. There's nothing wrong with the input data. I even converted it to csv and tried it. EXACT same problem. – Arianne Ford Apr 14 '15 at 6:44
  • @ArianneFord can you attach a link to the data? – dof1985 Apr 14 '15 at 7:23
  • No. It's unfortunately confidential company data. – Arianne Ford Apr 14 '15 at 9:22
  • 1
    If I were in your position, I would cut the file in half (either by rows or by columns), starting in Excel. Then see if you can reproduce the problem with either half of the file. If you can, keep halving the file and repeating until you find the offending bits. I know that you have solid field names, but with 600,000 rows, there could be something bad in there somewhere. – Andy Apr 14 '15 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.