In the near past I got frustrated searching for a tool to export from MsSQL express to proper CSV. As always the frustration lead me to google, and found the post on stackoverflow and to digital point forums and a lot of other places. All these had some or the other issues with us. One being the MsSQL not hosted on our servers and we dont have access to the GUI tools, the second is that I am a bit towards the fag end when Microsoft Technologies are in the anvil. Finally there was no other way and what I did was to migrate our PHP MySQL wrapper to support MsSQL.
This was not a big task as the MySQL wrapper was already being used in several projects. The porting was done in less than an hour or so. The full source of the same is replicated here for easy copy and use. Then using that and the php fputcsv the matter was very simple. I should be banging my head not to try this and going the wild way of exporting from MsSQL. Will add a download later..
The Export
<?php
/**
* DSNACC is defined as mssql://user:pass@host/db
* $a is an array of the tables
* $keys are fields in our table which should be converted to date
*/
function csv($a) {
$keys = array('Chq_Date', 'IDATE', 'SRV_DATE');
$db2 = new mssqlDb(DSNACC);
foreach ($a as $k => $table) {
$file = $table . date("-Y-m-d") . '.csv';
$ds = $db2->query("SELECT * FROM $table ORDER BY DOC_NOS ASC, DOC_SRNO ASC");
$line = 0;
$fp = fopen($file, 'w');
while ($row = $db2->fetch_array($ds)) {
if ($line == 0) {
fputcsv($fp, array_keys($row));
}
foreach ($keys as $keyval) {
if (isset($row[$keyval])) {
$row[$keyval] = date('Y-m-d', strtotime($row[$keyval]));
}
}
fputcsv($fp, array_values($row));
$line++;
}
fclose($fp);
}
}
The Wrapper
<?php
class mssqlDb {
private $link;
private $lastResult;
private $error;
function __construct($dsn) {
$pdsn = parse_url($dsn);
if ($pdsn['scheme'] !== 'mssql')
die("System is designed for MSSQL only.. Please Correct the dsn");
$mssql_db = preg_replace("@^\/@", '', $pdsn['path']);
if(!empty($pdsn['port'])){
$pdsn['host'] .= ':' . $pdsn['port'];
}
$this->link = mssql_connect($pdsn['host'], $pdsn['user'], $pdsn['pass']);
(!$this->link) && die("Could not connect MSSQL with $dsn!");
mssql_select_db($mssql_db, $this->link);
$this->lastResult = false;
}
public function __destruct() {
if ($this->link)
mssql_close($this->link);
$this->link = false;
}
public function close() {
if ($this->link)
mssql_close($this->link);
$this->link = false;
}
/**
* Sends a query to the database
*
* @param sqlquery $query
* @return result-resource
*/
function query($query) {
//if ($this->lastResult !== false) {
// $this->clearResult($this->lastResult);
//}
$this->lastResult = mssql_query($query);
if (!$this->lastResult) {
$this->error = mssql_get_last_message();
}
return $this->lastResult;
}
/**
* Perform a modification query on database
*
* @param string $table
* @param object $data
* @param string $action
* @param string $parameters
* @return data resource
*/
function perform($table, $data, $action = 'insert', $parameters = '') {
reset($data);
if ($action == 'insert') {
$query = 'INSERT INTO ' . $table . ' (' . join(', ', array_keys($data)) . ') VALUES (';
foreach ($data as $value) {
if (strpos($value, 'func:') !== false) {
$query .= substr($value, 5) . ', ';
} else {
switch ((string) $value) {
case 'now()' :
$query .= 'NOW(), ';
break;
case 'null' :
$query .= 'NULL, ';
break;
default :
$query .= '\'' . $this->input($value) . '\', ';
break;
}
}
}
$query = substr($query, 0, -2) . ')';
} elseif ($action == 'update') {
$query = 'UPDATE ' . $table . ' SET ';
foreach ($data as $columns => $value) {
if (strpos($value, 'func:') !== false) {
$query .= $columns . substr($value, 5) . ', ';
} else {
switch ((string) $value) {
case 'now()' :
$query .= $columns . ' = NOW(), ';
break;
case 'null' :
$query .= $columns . ' = NULL, ';
break;
case '++' :
$query .= $columns . ' = ' . $columns . ' + 1, ';
break;
default :
$query .= $columns . ' = \'' . $this->input($value) . '\', ';
break;
}
}
}
$query = substr($query, 0, -2);
if ($parameters !== '')
$query .= ' WHERE ' . $parameters;
}
$res = $this->query($query);
return $res;
}
function fetch_array($result) {
return mssql_fetch_array($result, MSSQL_ASSOC);
}
function fetch_object($result) {
return mssql_fetch_object($result);
}
function fetch_row($result) {
return mssql_fetch_row($result);
}
function num_rows($result) {
return mssql_num_rows($result);
}
function data_seek($result, $row_number) {
return mssql_data_seek($result, $row_number);
}
function insert_id() {
//return mssql_insert_id($this->link);
}
function affected_rows() {
return mssql_rows_affected($this->link);
}
function free_result($result) {
mssql_free_result($result);
}
function fetch_fields($result) {
return mssql_fetch_field($result);
}
function output($string) {
return htmlspecialchars($string);
}
function input($string) {
return addslashes($string);
}
function next_result() {
return mssql_next_result($this->link);
}
function store_result() {
return mssql_store_result($this->link);
}
function prepare_input($string) {
if (is_string($string)) {
return trim(stripslashes($string));
} elseif (is_array($string)) {
reset($string);
while (list ($key, $value) = each($string)) {
$string[$key] = $this->prepare_input($value);
}
return $string;
} else {
return $string;
}
}
/*
@Functions added from functions in admin folder by niju
*/
function getMulipleData($query, $fetch_array = false) {
$rs = $this->query($query);
if (!$rs)
return false;
if ($this->num_rows($rs) == 0)
return false;
$retval = array();
if ($fetch_array) {
while ($row = $this->fetch_array($rs)) {
if (count($row) > 1)
$retval [] = $row;
else{
$keys = array_keys($row);
$retval [] = $row [$keys[0]];
}
}
} else {
while ($row = $this->fetch_row($rs)) {
if (count($row) > 1) {
$retval [] = $row;
} else {
$retval [] = $row [0];
}
}
}
$this->clearResult($rs);
return $retval;
}
function getItemFromDB($query) {
$rs = $this->query($query);
if (!$rs)
return false;
if ($this->num_rows($rs) == 0)
return false;
$retval = $this->fetch_row($rs);
$this->clearResult($rs);
return $retval [0];
}
function getFromDB($query, $fetchArray = false) {
$rs = $this->query($query);
if (!$rs)
return false;
if ($this->num_rows($rs) == 0)
return false;
if ($fetchArray) {
$retval = $this->fetch_array($rs);
} else {
$retval = $this->fetch_row($rs);
}
$this->clearResult($rs);
return $retval;
}
function getArrayFromDB($query) {
$rs = $this->query($query);
if (!$rs)
return false;
if ($this->num_rows($rs) == 0)
return false;
else {
$retval = array();
while ($row = $this->fetch_row($rs)) {
$retval [$row [0]] = $row [1];
}
}
$this->clearResult($rs);
return $retval;
}
/**
* Function to clear the resultset to avoid the commands out of sync error
*
* @param resource $result
* @author Niju N B
*/
function clearResult($result) {
if (is_resource($result)) {
$this->free_result($result);
}
$this->lastResult = false;
}
}
?>