StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow would I check the csv file for a specifc value in the row and then use that as the header row AND if there is duplicates remove them?
text
Body
copied!<p>I am using the following function to import csv files to mysql:</p> <pre><code>function csv_2_mysql($source_file, $target_table, $max_line_length=10000) { if (($handle = fopen("$source_file", "r")) !== FALSE) { $columns = fgetcsv($handle, $max_line_length, ","); foreach ($columns as &$column) { $column = preg_replace('/[^a-z0-9]/i', '', $column); } $insert_query_prefix = "INSERT INTO $target_table (".join(",",$columns).")\nVALUES"; while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) { while (count($data)<count($columns)) array_push($data, NULL); $query = "$insert_query_prefix (".join(",",quote_all_array($data)).");"; mysql_query($query); } fclose($handle); } } function quote_all_array($values) { foreach ($values as $key=>$value) if (is_array($value)) $values[$key] = quote_all_array($value); else $values[$key] = quote_all($value); return $values; } function quote_all($value) { if (is_null($value)) return "NULL"; $value = "'" . mysql_real_escape_string($value) . "'"; return $value; } </code></pre> <p>The problem is, that sometimes the headers are not on the first row due to cutting and merging of csv files at the source so for example it may end up looking like this:</p> <pre><code>value1,value2,value3,value4 value1,value2,value3,value4 value1,value2,value3,value4 header1,header2,header3,header4 value1,value2,value3,value4 value1,value2,value3,value4 value1,value2,value3,value4 value1,value2,value3,value4 header1,header2,header3,header4 value1,value2,value3,value4 value1,value2,value3,value4 value1,value2,value3,value4 </code></pre> <p>value1 is unique so I know that there is never a duplicate row except for headers. How can I adjust the function so that the duplicate header rows are removed if they exist and to ensure that the remaining header row is used for $columns? I would just set the column values manually except each csv may have a different number of columns (except header1 and value1 which are always present as it is a unique timestamp).</p> <p>UPDATE:</p> <p>Well, I figured it out but it feels wrong using both fopen and file_get_contents. Will I run into problems with this on large csv's?</p> <pre><code>function csv_2_mysql($source_file, $target_table, $uid, $nid, $max_line_length=10000) { if (($handle = fopen("$source_file", "r")) !== FALSE) { $handle2 = file_get_contents($source_file) or exit; $handle_row = explode("\n", $handle2); foreach ($handle_row as $key => $val) { $row_array = explode(',', $val); foreach ($row_array as $key => $val) { $row_array[$key] = trim(str_replace('"', '', $val)); } if(!in_array('header1', $row_array)) { unset ($row_array); } else { $columns = $row_array; } } foreach ($columns as &$column) { $column = preg_replace('/[^a-z0-9]/i', '', $column); } $insert_query_prefix = "INSERT INTO $target_table (".join(",",$columns).")\nVALUES"; while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) { while (count($data)<count($columns)) array_push($data, NULL); $query = "$insert_query_prefix (".join(",",quote_all_array($data)).");"; mysql_query($query); } fclose($handle); } } function quote_all_array($values) { foreach ($values as $key=>$value) if (is_array($value)) $values[$key] = quote_all_array($value); else $values[$key] = quote_all($value); return $values; } function quote_all($value) { if (is_null($value)) return "NULL"; $value = "'" . mysql_real_escape_string($value) . "'"; return $value; } </code></pre>

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