StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBest practices for importing large CSV files
text
Body
copied!<p>My company gets a set of CSV files full of bank account info each month that I need to import into a database. Some of these files can be pretty big. For example, one is about 33MB and about 65,000 lines.</p> <p>Right now I have a symfony/Doctrine app (PHP) that reads these CSV files and imports them into a database. My database has about 35 different tables and on the process of importing, I take these rows, split them up into their constituent objects and insert them into the database. It all works beautifully, except it's <em>slow</em> (each row takes about a quarter second) and it uses a lot of memory.</p> <p>The memory use is so bad that I have to split up my CSV files. A 20,000-line file barely makes it in. By the time it's near the end, I'm at like 95% memory usage. Importing that 65,000 line file is simply not possible.</p> <p>I've found symfony to be an exceptional framework for building applications and I normally wouldn't consider using anything else, but in this case I'm willing to throw all my preconceptions out the window in the name of performance. I'm not committed to any specific language, DBMS, or anything.</p> <p>Stack Overflow doesn't like subjective questions so I'm going to try to make this as un-subjective as possible: for those of you have not just an opinion but <strong>experience importing large CSV files</strong>, what tools/practices have you <strong>used in the past</strong> that have been successful?</p> <p>For example, do you just use Django's ORM/OOP and you haven't had any problems? Or do you read the entire CSV file into memory and prepare a few humongous <code>INSERT</code> statements?</p> <p>Again, I want not just an opinion, but something that's actually worked for you in the past.</p> <p>Edit: I'm not just importing an 85-column CSV spreadsheet into one 85-column database table. I'm normalizing the data and putting it into dozens of different tables. For this reason, I can't just use <code>LOAD DATA INFILE</code> (I'm using MySQL) or any other DBMS's feature that just reads in CSV files.</p> <p>Also, I can't use any Microsoft-specific solutions.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload