Hi all

I am building a script that uploads a CSV file and I need to insert the content into a table. Seems simple!

The help i need is that I need to obtain the 'name' of the columns and use them to ensure I insert the correct column data into the correct corresponding database field.

The CSV files will always contain four columns:

Firstname
Lastname
Dob
Col

However the order of the columns may be different each time a CSV is uploaded so i can't just assume the Lastname column will be the second column of the CSV.

How can I parse the CSV file into my database using the names of the columns and if the names of the columns do not match as above, make the import fail?

Many thanks for reading.

Here is what I have so far:

$fcontents = file('documents/csvs/' . $file_name);
for($i=0; $i<sizeof($fcontents); $i++) {
	$line = trim($fcontents[$i]);
	$arr = explode("\t", $line);

$sql = "insert into mytable values ('". implode("','", $arr) ."')";
mysql_query($sql);
echo $sql ."<br>\n";
if(mysql_error()) {
	echo mysql_error() ."<br>\n";
}
}

kbc1

    Assuming the first (non-empty) line of the CSV file has the column names, I might do something like this (too much time on my hands today):

    <?php
$allowedColumns = array(
   'Firstname',
   'Lastname',
   'Dob',
   'Col'
);
mysql_connect('localhost', '#####', '#####') or die(mysql_error());
mysql_select_db('the_db') or die(mysql_error());
$file = 'documents/csvs/' . $file_name;
$lines = file($file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
foreach($lines as $key => $line) {
   $lines[$key] = explode("\t", $line);
}
$columns = array_shift($lines);
$intersect = array_intersect($columns, $allowedColumns);
if(count($intersect) != count($allowedColumns)) {
   die("Wrong columns");
}
array_walk(
   $columns,
   create_function(
      '&$value,$key',
      '$value = "`" . mysql_real_escape_string($value) . "`";'
   )
);
$sql = "INSERT INTO `mytable` (" . implode(', ', $columns) . ") VALUES\n";
foreach($lines as &$line) {
   array_walk(
      $line,
      create_function(
         '&$value,$key',
         '$value = (is_numeric($value)) ? $value : "\'".mysql_real_escape_string($value)."\'";'
      )
   );
   $line = "(".implode(', ', $line).")";
}
$chunks = array_chunk($lines, 10); // process 10 at a time
foreach($chunks as $chunk) {
   $thisSql = $sql . implode(",\n", $chunk);
   mysql_query($thisSql) or die(mysql_error());
}
      Write a Reply...