nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Convert Large Tables (CSV) to FBGDB Tables

EricMahaffey1

I've been trying all sorts of methods for converting some very large CSV files (~20 million records) to FGDB Tables. Every method results in max RAM being exceeded (16GB @ 64-bit). Through ArcGIS Desktop I've tried Table to Table, Copy Rows, and Load Data. Through ArcPy I've tried the same tools, as well as Cursors. The only thing that I think will work is reading the data in chunks (around 100,000 records at a time), and inserting those "chunks" into the table. I've managed to use the Python Pandas library to read the data in chunks. I just need to figure out how to write the chunks to the GDB table. Any ideas?

Finde mehr Beiträge mit dem Tag

Accepted answers

All comments

Anonymous User

Hi Eric Mahaffey‌,

If you firstly set up an empty table in the file gedoatabase containing fields named as per your CSV file, you could insert the records from the pandas dataframe into you the file geodatabase table using an arcpy.da.InsertCursror - something like this:

<SPAN class="keyword token">import</SPAN> pandas <SPAN class="keyword token">as</SPAN> pd
<SPAN class="keyword token">import</SPAN> arcpy

filename<SPAN class="operator token">=</SPAN>r<SPAN class="string token">'C:\path\to\file.csv'</SPAN>
fc_table <SPAN class="operator token">=</SPAN>  r<SPAN class="string token">'C:\path\to\file.gdb\table'</SPAN>

chunksize <SPAN class="operator token">=</SPAN> <SPAN class="number token">100000</SPAN>
<SPAN class="keyword token">for</SPAN> chunk <SPAN class="keyword token">in</SPAN> pd<SPAN class="punctuation token">.</SPAN>read_csv<SPAN class="punctuation token">(</SPAN>filename<SPAN class="punctuation token">,</SPAN> chunksize<SPAN class="operator token">=</SPAN>chunksize<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
    keys <SPAN class="operator token">=</SPAN>  chunk<SPAN class="punctuation token">.</SPAN>keys<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>tolist<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
    insert_cur <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>InsertCursor<SPAN class="punctuation token">(</SPAN>fc_table<SPAN class="punctuation token">,</SPAN> keys<SPAN class="punctuation token">)</SPAN>
    <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> chunk<SPAN class="punctuation token">.</SPAN>itertuples<SPAN class="punctuation token">(</SPAN>index<SPAN class="operator token">=</SPAN><SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
        insert_cur<SPAN class="punctuation token">.</SPAN>insertRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
    <SPAN class="keyword token">del</SPAN><SPAN class="punctuation token">(</SPAN>insert_cur<SPAN class="punctuation token">)</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

EricMahaffey1

James,

Thank you so much for the quick response. I've integrated your solution into my script, and I'm getting an error "CParserError: Error tokenizing data. C error: Expected 22 fields in line 68, saw 23". I believe it's because the CSV file does not have an OBJECTID field. How do I tell the InsertCursor function to either skip the first field, or identify the specific fields to insert to?

~Eric

JoshuaBixby

The insert cursor will never raise a CParserError about tokenizing data. That error is common to the pandas read_csv function. You have a row in your CSV file that isn't being parsed correctly.

EricMahaffey1

Joshua,

I believe you're right. This data has been nothing but problems. I managed to force it by adding the usecols paramater within the prd.read_csv function. Then I started having "bad value" errors. So I converted all of my target fields to text with a large width just the data to go in. This worked. However, when I changed a couple of columns (i.e. LAT and LONG) back to Double type fields it caused a "RuntimeError: The value type is incompatible with the field type error". I can't seem to catch a break.

quincybatten

"CParserError: Error tokenizing data. C error"

In most cases, it might be an issue with:

the delimiters in your data.
confused by the headers/column of the file.

The error tokenizing data may arise when you're using separator (for eg. comma ',') as a delimiter and you have more separator than expected (more fields in the error row than defined in the header). So you need to either remove the additional field or remove the extra separator if it's there by mistake. The better solution is to investigate the offending file and to fix it manually so you don't need to skip the error lines.

Nimm an der Diskussion teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.