PHP Export to CSV from SQL Server

Question

I need some help to export from a SQL Server query to csv.

I have the query but when I'm fetching the result I need to put it on an variable and export it.

This is what I have:

$query = 'select * from sqlserver_table';
$result = odbc_exec($conMsSql,$query);

// this gives me the columns
while(odbc_fetch_row($result)){
        $field1 = odbc_result($result, 1);
        $field2 = odbc_result($result, 2);
        $field3 = odbc_result($result, 3);
        $field4 = odbc_result($result, 4);
        $field5 = odbc_result($result, 5);
        $field6 = odbc_result($result, 6);
        $field7 = odbc_result($result, 7);
    }


// this is to export
$file = fopen("export.csv","w");

foreach ($list as $line){   // how can I put the result in this variable ($list)?
    fputcsv($file,explode(',',$line));
}

fclose($file);

I'm not sure what the question is exactly. Can you be more specific? What part of Explorting the query to a csv are you having trouble with? — Commander, Apr 07 '15 at 19:35
I need to put the query result into the $list variable and thats where my problem is, how to pass it to the $list variable. — , Apr 07 '15 at 19:37

score 1 · Answer 1 · answered Apr 07 '15 at 19:43

lets say this was your sql, you'd do the following to export data from mssql to .csv.

 $sql = "SELECT * FROM target_database_table_name";

$results = mssql_query($sql, $db);
//Generate CSV file - Set as MSSQL_ASSOC as you don't need the numeric values.
while ($l = mssql_fetch_array($results, MSSQL_ASSOC)) {
    foreach($l AS $key => $value){
        //If the character " exists, then escape it, otherwise the csv file will be invalid.
        $pos = strpos($value, '"');
        if ($pos !== false) {
            $value = str_replace('"', '\"', $value);
        }
        $out .= '"'.$value.'",';
    }
    $out .= "\n";
}
mssql_free_result($results);
mssql_close($db);
// Output to browser with the CSV mime type
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=table_dump.csv");
echo $out;

score 0 · Accepted Answer · answered Apr 07 '15 at 19:43

You'd want something like this:

$csvName = "export.csv"
$sqlQuery = 'select * from sqlserver_table';
$sqlRresult = odbc_exec($conMsSql, $sql);

$fp = fopen(csvName , 'w');

while ($export = odbc_fetch_array($sqlRresult)) {
    if (!isset($headings))
    {
        $headings = array_keys($export);
        fputcsv($fp, $headings, ',', '"');
    }
    fputcsv($fp, $export, ',', '"');
}
fclose($fp);

PHP Export to CSV from SQL Server

2 Answers2

Linked