How can I import the contents of an uploaded csv file into a drupal managed table using rules custom php?

Question

I am using drupal 7 and have the data module managing/adopting a table I have manually added to my drupal db. What I was hoping was that when a csv file is uploaded via a specific content type I could use rules to execute some custom php and import the csv into the table (as feedsdataprocessor no longer exists from what I can see). I see there is a drupal_write_record function but the problem is first how do I get the array out of the file and secondly my file can be different to the table in structure - for example, sometimes the csv will have columns 1,2,3,4 and sometimes 3,4,5,6,7. How can I check the headers and import into the managed table?

Answer 1

So conceptual steps:

1. Extract CSV file path from the node.
2. Open CSV file, read fields from header.
3. Read each row, convert the keys, save row using drupal_write_record().
4. Close the CSV.

I'm not sure how the rules integration will work I'm assuming you can pass the node into it some how.

So for step one assuming it's stored into $node and your field is named field_csv_file we can hand the URL to fopen():

$csv_uri = $node->field_csv_file['und'][0]['uri'];

So now we can open fopen() to get a file handle:

$handle = fopen($csv_uri, 'r');

If there's no value we don't want to procede.

Read the header row:

$row = fgetcsv($handle);
$columns = array();
foreach ($row as $i => $header) {
  $columns[$i] = trim($header);
}

Then load each row and convert the format:

while ($row = fgetcsv($handle)) {
  $record = array();
  foreach ($row as $i => $field) {
    // This is pretty brittle... if someone screws up the field 
    // names the data won't be written. 
    $record[$columns[$i]] = $field;
  }
  drupal_write_record('your_table', $record);
}

Close the file:

fclose($handle);

So issues with this:

• All this code it totally un-test.
• What happens if someone re-uploads the file? You'll probably get duplicates.
• What happens if someone messes with the headers?