How to download in CSV using API

[Exe]

Hello,

I'm trying to download a report in csv using API.
I found the endoit (I hope it's the right one):

POST /api/dataset/:export-format
Execute a query and download the result data as a file in the specified format.

PARAMS:
export-format value must be one of: api, csv, json, xlsx.

query value must be a valid JSON string.

visualization_settings value must be a valid JSON string.

I wrote it in PHP :

public function DownloadCSV($token, $query, $exportFormat) {
    if($exportFormat == "api" or $exportFormat == "csv" or $exportFormat == "json" or $exportFormat == "xlsx"){
        $ch = curl_init();
	//https://github.com/metabase/metabase/blob/master/docs/api/dataset.md
	var_dump($this->MetabaseURL.'/api/dataset/'.$exportFormat);
	curl_setopt($ch, CURLOPT_URL, $this->MetabaseURL.'/api/dataset/'.$exportFormat);
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
	curl_setopt($ch, CURLOPT_POSTFIELDS, "query=$query");

	$headers = array();
	$headers[] = 'Content-Type: application/json';
	$headers[] = 'X-Metabase-Session: '.$token;
	curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

	$result = curl_exec($ch);
	if (curl_errno($ch)) {
		echo 'Error:' . curl_error($ch);
	}
	curl_close($ch);
	file_put_contents("text.txt", $result);
	return $result;
 }
	 }

Output :

string 'http://localhost:3000/api/dataset/csv' (length=37)
string 'Malformed JSON in request body.' (length=31)

The query contain a SQL request.

How do I format query and visualization_settings ?

And i'll like to update the data before exporting, how can I update the data ?
Thanks

[flamber]

Hi @Exe
For reference: https://www.metabase.com/docs/latest/api/dataset.html#post-apidatasetexport-format

The best way to learn the API, is to just use Metabase while having your browser developer Network-tab open and looking at the request, and what data is being send/received.

Both query and visualization_settings can be fairly complex JSON objects. Look in your Network-tab to figure out what works for you.

I don't know what you mean about "update the data before exporting" - if you want to manipulate the results, then you'll either have to do that in the query or in your script (PHP) before sending the file to the end user.

[Exe]

The issue here, is that payload is empty when I download in CSV.

When I use the link used in request http://localhost:3000/api/card/1/query/csv I get an error :

"l'endpoint d'API n'existe pas."

[flamber]

@Exe Your are referring to different endpoints:
https://www.metabase.com/docs/latest/api/card.html#post-apicardcard-idqueryexport-format

Copy the request from the browser developer Network-tab as cURL. If it works in the browser, but not in your script, then you are doing something wrong in your script.

[Exe]

I get this endpoint when I download

The endpoint doesn't work on the browser

[flamber]

@Exe
Post "Diagnostic Info" from Admin > Troubleshooting.
And provide steps-to-reproduce, since you are now saying that Metabase doesn't work.

[Exe]

Here is the Diag :

{
  "browser-info": {
    "language": "fr-FR",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.4951.64 Safari/537.36 OPR/87.0.4390.35",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "18.0.1+10",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "18.0.1",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "18.0.1+10",
    "os.name": "Windows Server 2016",
    "os.version": "10.0",
    "user.language": "fr",
    "user.timezone": "Europe/Paris"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-05-16",
      "tag": "v0.43.1",
      "branch": "release-x.43.x",
      "hash": "7f1a1c4"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

Here is how I reproduce it :

I download the data in .csv format.

Then I check in the network tab :

Result :

Why is it poorly documented for :

• query value must be a valid JSON string.
• visualization_settings value must be a valid JSON string.

[flamber]

@Exe

1. Use an LTS version of Java: https://www.metabase.com/docs/latest/operations-guide/java-versions.html
2. Migrate away from H2 if you are using Metabase in production: https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

I don't understand what the problem is with your setup, but you're doing something wrong and I don't know what it is, but there's 40k+ installations of Metabase.

[Exe]

Thanks for the advice.

I managed to fix the function.

public function DownloadCSV($token, $card, $exportFormat) {
    if($exportFormat == "api" or $exportFormat == "csv" or $exportFormat == "json" or $exportFormat == "xlsx"){
        $ch = curl_init();
	    $payload = json_encode( array( "parameters" => '' ) );
	    curl_setopt( $ch, CURLOPT_POSTFIELDS, $payload );
	    curl_setopt( $ch, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));

	    curl_setopt($ch, CURLOPT_URL, $this->MetabaseURL.'/api/card/'.$card.'/query/'.$exportFormat);
	    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

	    $headers = array();
	    $headers[] = 'Content-Type: application/json';
	    $headers[] = 'X-Metabase-Session: '.$token;
	    curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

	    $result = curl_exec($ch);
	    if (curl_errno($ch)) {
	        echo 'Error:' . curl_error($ch);
	    }
	    curl_close($ch);
	    file_put_contents("export_".$card.".".$exportFormat, $result);
	    return $result;
    }
}