php - What is quicker for search and replace in a csv file? In a string or in an array?
<p>I am dealing with csv files that usually have between 2 million to 5 million rows.  I have (for example) 3000 specific values that need to be replaced by 3000 different values.  I have two arrays of 3000 items called $search and $replace.  Note: The search and replace phrases are complete values (e.g. ...,search,... -> ...,replace,...).  Also, I'll eventually be importing this into a mysql database.</p>

<p>Which would be the most efficient/quickest way to accomplish this?</p>

<ol>
<li><p>Load the entire contents of the csv file into a string and run str_replace using the arrays and the string</p></li>
<li><p>Load the csv file into arrays and use array_search() to replace the values</p></li>
<li><p>Load the csv file into a mysql database and then search and replace using queries</p></li>
<li><p>Use python instead</p></li>
<li><p>Other</p></li>
</ol>

<p>I know I could setup some tests and compare their runtimes, but I'm more looking to understand why one is better than the other, or the mechanism by which they search (ex: O(n), binary search, etc.?)</p>

<p>If your csv file is that big (> 1 million rows), it might not be the best idea to load it all at once unless memory usage is of no concern to you.</p>

<p>Therefor, I'd recommend running the replace line by line. Here's a very basic example:</p>

<pre><code>$input = fopen($inputFile, 'r');
$output = fopen($outputFile, 'r+');

while (!feof($input)) {
    $input = fgets($input);

    $parsed = str_replace($search, $replace, $input);

    fputs($output, $parsed);
}
</code></pre>

<p>This should be fast enough, and it allows you to easily track progress as well. If you would ever like to replace only specific column, you can use <code>fgetcsv</code> and <code>fputcsv</code> instead of <code>fgets</code> and <code>fputs</code>.</p>

<p>I definitely wouldn't try to do this using mysql, as simply inserting this much data into a database will take a while.</p>

<p>As for python, I'm not sure whether it can actually benefit the algorithm in any way.</p>

php python mysql arrays csv