ArcGIS fails to import all records from huge CSV file to file geodatabase table despite being within FGDB table size limits

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.

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.

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/