3

I'm contributing data to the opencellid.org website. They have several API options to submit data but the only bulk method requires an HTTP POST file upload with the API key as a POST field. Formats acceptable are CSV, JSON and CLF3. I store the data in an SQL database for internal use and periodically submit data to opencellid.

At the moment the script that I use to submit the data to opencellid queries the SQL DB for the most recent measurements and then saves it as a CSV file on the server and then immediately uploads it via HTTP POST. In my eyes this is inelegant.

So my question is, can you POST upload a CSV file directly from an array without first having to actually create a CSV file on the server?

Here's the code snippet we currently use.

//Create and save CSV
$output = fopen("opencellid.csv","w") or die();
fputcsv($output, array_keys($celldata[0]));

foreach($celldata as $cell) {
    fputcsv($output, $cell);
}   

fclose($output) or die();

//Upload CSV
$target_url = 'http://opencellid.org/measure/uploadCsv';

$file_name_with_full_path = realpath('./opencellid.csv');

$post = array('key' => 'opencellidapikey',
              'datafile'=>'@'.$file_name_with_full_path.";type=text/plain");

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL,$target_url);
curl_setopt($ch, CURLOPT_POST,1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $post);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);

$postresult = curl_exec ($ch);

Can anyone suggest a way to directly upload a CSV from an array?

Peter Pudaite
  • 406
  • 8
  • 18
  • Your script converts the array in to a csv file and posts it. I'm not sure that you can get much simpler than that. You need to create the csv data somehow otherwise how would you submit it? – Phil Mar 13 '15 at 08:00
  • I was thinking there may be two conceptual ways, first is to create a string that contains the CSV and echo it. The second would be to pass some sort of filepointer reference in to fputcsv which would pass in to curl or file_get_contents... What I'm doing now works fine. But I'm OCD like that so asked the question. – Peter Pudaite Mar 13 '15 at 11:23
  • Ah i see. You might be able to use a combination of fputcsv to php://output + output buffering to capture the data to a variable. Then post that variable. I will be able to write a full answer later on if noone else has. – Phil Mar 13 '15 at 11:45
  • if they accept json as input, it would be easier to just get the data from the db as an array and call json_encode($row) – Carlos Campderrós Mar 14 '15 at 19:02
  • Yes they do accept JSON but the "weird" thing is they expect it as an uploaded file so the same problem arises. As I'm uploading some 120,000 rows a day I prefer CSV as it is more data dense than JSON. – Peter Pudaite Mar 15 '15 at 13:24

2 Answers2

1

Intreesting question. The only thing I can think of is that you echo the "csv data" with headers of a post and that it's a csv file. That should not create a file on the server afaik. Not sure fully how to approach it, but if you run set apache headers or whichever server system you're using. Give it a shot and let me know if it works.

Medda86
  • 1,582
  • 1
  • 12
  • 19
  • I'm thinking the same thing. It's somewhat involved but they could create a `.htaccess` file that redirects "requests" for `opencellid.csv` to something like `opencellid.php` which simply parses the databases, sets the headers, and echoes the content. – rfoo Mar 13 '15 at 07:53
  • The way curl is used at the moment requires a filename & path. If it used a filepointer there may be a way to do it. The only other way I can think of is the using file_get_contents to do the HTTP POST and create the CSV as a string. I had a quick look at how it could be done but the code seemed "messy" such as having to manually define mime multi-part boundaries etc. Will keep on trying. – Peter Pudaite Mar 13 '15 at 11:30
1

I managed to capture the csv data in to a variable using fputcsv to php://output with output buffering. Since the service only allows multipart format for the submission, you need to construct the payload like this.

<?php

//Construct your csv data
$celldata = array(
    array(
        "heading1",
        "heading2",
        "heading3",
    ),
    array(
        1,
        2,
        3,
    ),
    array(
        4,
        5,
        6,
    )
);

//Output to php://output
ob_start();
$outstream = fopen("php://output", 'w');
foreach($celldata as $cell) {
    fputcsv($outstream, $cell);
}   
fclose($outstream) or die();
$csv_data = ob_get_clean();

$url = 'http://opencellid.org/measure/uploadCsv';
// Taken from http://stackoverflow.com/questions/3085990/post-a-file-string-using-curl-in-php

// form field separator
$delimiter = '-------------' . uniqid();
// file upload fields: name => array(type=>'mime/type',content=>'raw data')
$fileFields = array(
    'datafile' => array(
        'type' => 'text/plain',
        'content' => $csv_data,
    ),
);
// all other fields (not file upload): name => value
$postFields = array(
    'key'   => 'opencellidapikey', //Put your api key here
);

$data = '';

// populate normal fields first (simpler)
foreach ($postFields as $name => $content) {
    $data .= "--" . $delimiter . "\r\n";
    $data .= 'Content-Disposition: form-data; name="' . $name . '"';
    $data .= "\r\n\r\n";
    $data .= $content;
    $data .= "\r\n";
}
// populate file fields
foreach ($fileFields as $name => $file) {
    $data .= "--" . $delimiter . "\r\n";
    $data .= 'Content-Disposition: form-data; name="' . $name . '";' .
             ' filename="' . $name . '"' . "\r\n";
    $data .= 'Content-Type: ' . $file['type'] . "\r\n";
    $data .= "\r\n";
    $data .= $file['content'] . "\r\n";
}
$data .= "--" . $delimiter . "--\r\n";

$handle = curl_init($url);
curl_setopt($handle, CURLOPT_POST, true);
curl_setopt($handle, CURLOPT_HTTPHEADER , array(
    'Content-Type: multipart/form-data; boundary=' . $delimiter,
    'Content-Length: ' . strlen($data)));  
curl_setopt($handle, CURLOPT_POSTFIELDS, $data);
curl_setopt($handle, CURLOPT_RETURNTRANSFER,1);
$result = curl_exec($handle);
var_dump($result);

I get API key error, but it should work.

Phil
  • 1,996
  • 1
  • 19
  • 26