11

I'm using ArcGIS 10.0 on Windows 7 64-bit with 4GB of RAM.

I have some very large tables in CSV format to import to ArcGIS, they all have about 30 fields, upwards of 5 million records per table (a few have double that or more), and file sizes up to about 5 GB. I am trying to import each of them into a file geodatabase as separate tables so I can, ultimately, link them to a feature class and analyze the results in the tables according to their location.

The problem is that ArcGIS seems to just quit importing records at a certain point. I'm using the "Table to Table" tool under Conversion > To Geodatabase, but the "Copy Rows" tool has the same problem. Even if I just add the CSV file directly to ArcGIS without trying to convert it to an FGDB table first, the problem is the same. One of my tables has about 11 million records, and ArcGIS only imports about 10 million of them. ArcGIS doesn't tell me that any error has occurred, the tool just finishes as if nothing is wrong.

I've tried it a few times now and the number of records that make it into the FGDB table is always the same, and doesn't appear to be a file size limit I've ever heard of (not a square of 2 or 16). ArcGIS was able to import another CSV with about 6 million records and all the records came through (though with the problems I'm having with the larger table, the smaller one is kind of suspect now too). ESRI's web site lists the following size limits in a file geodatabase, and I'm far from hitting any of them:

  • File geodatabase size: No limit
  • Table or feature class size: 1 TB (default), 4 GB or 256 TB with keyword
  • Number of feature classes and tables: 2,147,483,647
  • Number of fields in a feature class or table: 65,534
  • Number of rows in a feature class or table: 2,147,483,647
  • Geodatabase name length: Number of characters the operating system allows in a folder
  • Feature class or table name length: 160 characters
  • Field name length: 64 characters
  • Text field width: 2,147,483,647

All I really need to do to these tables are add a couple fields, delete a couple others, and generate values for the new fields (sums of a few of the existing fields). I'm using ArcGIS for it because I'm familiar with the field calculator and I know (or knew, until now) that it could handle tables consisting of millions of records, whereas most other desktop software I have handy (MS Access/Excel) chokes on that many records. So I'm open to using some other piece of software to manipulate the original table and then exporting the (much smaller) resulting table to ArcGIS. Really, the fact that I'm having this problem and that ArcGIS is not giving me any errors or warnings that the problem is even occurring makes me want to handle this data outside ArcGIS as much as possible.

Improve this question
7
  • 2
    If "the number of records that make it into the FGDB table is always the same" then I would take a look at the last and next records to see whether they might have something in them that looks inconsistent compared to the millions imported successfully previously.
    – PolyGeo ♦
    Sep 8, 2012 at 9:08
  • 1
    Good idea. I can't see any difference between the last record in the truncated FGDB table and the record after it (from the CSV). I just tried removing all the successfully imported records from the source CSV, then importing the remainder into another FGDB table, and it worked. So it doesn't appear to be a problem with any one record. To make things worse, I merged the two FGDB tables (between the two of them I have all the source records), and once again ArcGIS pretends everything went fine, but the merged table contains only 9.6 million of the 10.9 million records of the two FGDB tables.
    – Dan C
    Sep 8, 2012 at 15:28
  • Have you opened a support incident with ESRI? It seems at this point, you've discovered what could potentially be a rather serious problem. If nothing else, the support staff would be interested in knowing about it simply because they may know a solution already, or would be willing to help with testing.
    – Get Spatial
    Sep 9, 2012 at 8:23
  • I agree with Get Spatial but one last test you may want to run is generating a CSV file with one field into which you place identical values (perhaps "test"). If your theory is that 9.6 million is the maximum then this limit would be hit anytime 10 million lines of "test" are used, but not when 9.5 million lines are.
    – PolyGeo ♦
    Sep 9, 2012 at 11:45
  • I've now tried with a different, but also large (over 10 million records) CSV and it fails the same way, but at a different line (about 8.9 million records get in). So it doesn't seem to be a specific number of records, or a specific table size. I'll try a test CSV with two fields and see what happens. I'll call ESRI on Monday either way though, this process failing with no error message is unacceptable and makes even the records that do make it in suspect.
    – Dan C
    Sep 9, 2012 at 16:27

5 Answers 5

Reset to default
9

I did call ESRI support about this and their answer wasn't encouraging, but it did explain the problem. Paraphrasing ESRI: The problem is that ArcGIS Desktop, being 32-bit software, is limited to using 4GB of RAM at the most. The text file has to be processed in RAM before being stored as a table, so at some poing during processing ArcGIS was hitting the RAM limit and just stopping there. The file I was importing was around 6GB in size. Apparently the fact that it failed without giving an error message is unique to me, I tried having other people in my office do it and the import still failed, but it gave an error message (an unhelpful one, but at least something that let the user know something went wrong), and the ESRI rep said that it should give an error.

My solution was to split the file into two smaller CSVs using a text editor (I used EditPad Pro), import each of them into an FGDB as a separate table, then merge the two FGDB tables. For some reason this failed the first time I tried it but worked later on. I may get around to testing this a little more fully, I'm going to be dealing with files this size on an ongoing basis.

I'm using ArcGIS 10.0, but ArcGIS 10.1 service pack 1 was just released and adds the ability to use a 64-bit background geoprocessor, which will let the geoprocessor use more than 4GB RAM, that may fix this problem but I can't test that.

UPDATE: I am now using ArcGIS 10.1 SP1 (with the 64-bit background geoprocessing addon) and it does successfully import these giant .CSVs, at least the ones I've dealt with so far. On a machine with 14GB of RAM (yes, 14), a 6GB .CSV with about 10.5 million rows successfully imports to an FGDB table.

Improve this answer
1
  • 1
    I would be curious if you could try running it in a 64bit build of GDAL. I bet it would work fine.
    – Ragi Yaser Burhum
    Nov 10, 2012 at 0:41
7

For data load purposes, reading a huge CSV file into memory is rather silly. It only really ever needs to read 1 line at time.

I would suggest writing a Python script and use the csv module to read it line by line and insert rows into the table using an InsertCursor (or preferably an arcpy.da.InsertCursor as it is faster, but only available at 10.1).

Edit: Just read your last paragraph. It sounds like you could actually probably do this all inside Python pretty easily, even exporting the results back out to CSV or some other format.

If you could describe exactly what you need to do with each row and column that would be helpful.

Improve this answer
4

Have you tried to split the 5GB csv files into small.

there is a tool to split the csv based on rows or file count.

Split the files and then try to import.. But there is a limitation in this tool, i think it will work only for table in a file (i think so). pls. give a try.

http://www.shivaranjan.com/2008/11/06/how-to-split-csv-file-into-multiple-parts-easily-and-quickly/

Improve this answer
2
  • I plan to try that if I have to, there aren't that many CSVs to deal with so I'll probably just split them manually with my text editor. I'd still like to find out if anyone else has had this problem though, if ArcGIS is going to make a habit of misunderstanding large tables and not even having the common courtesy to throw a useless error message, that's going to be a problem.
    – Dan C
    Sep 7, 2012 at 20:40
  • OK, I just tried this and it work partway. After splitting the CSV into two smaller ones (manually, with a text editor), they imported successfully to two separate FGDB tables and all the records are there. But when I try to merge those two FGDB tables into one, ArcGIS once again runs through the process as if nothing is wrong, and then the merged table is missing 1.3 million records.
    – Dan C
    Sep 8, 2012 at 15:32
2

I was running into this error (001156) on the same line of a large pipe-delimited text file (2,712,391) rows about a quarter of the way through.
So I thought there was something wrong with that line, but it was identical to the remainder of the rows.
I ended up deleting the rows from the partial import and then loading the data (Load>Load Data...) and was able to get all 2M+ lines.

I, too, am using 10.1 SP1 w/ 64 bit background geoprocessing on 16GB of RAM and it is a process that will utilize the RAM (not every process is enabled in 64-bit yet).
Slow, klunky workaround, but it works consistently.
You may have to set up the empty table first if you are having no success with any degree of import.

Improve this answer
1

ArcGIS Pro runs using 64BIT and will accept adding CSV as input file without truncating large datasets.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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