Home > PHP, Tips > MsSQL Export to CSV

MsSQL Export to CSV

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($fparray_keys($row));
                }
                foreach (
$keys as $keyval) {
                    if (isset(
$row[$keyval])) {
                        
$row[$keyval] = date('Y-m-d'strtotime($row[$keyval]));
                    }
                }
                
fputcsv($fparray_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($value5) . ', ';
                } else {
                    switch ((string) 
$value) {
                        case 
'now()' :
                            
$query .= 'NOW(), ';
                            break;
                        case 
'null' :
                            
$query .= 'NULL, ';
                            break;
                        default :
                            
$query .= '\'' $this->input($value) . '\', ';
                            break;
                    }
                }
            }
            
$query substr($query0, -2) . ')';
        } elseif (
$action == 'update') {
            
$query 'UPDATE ' $table ' SET ';
            foreach (
$data as $columns => $value) {
                if (
strpos($value'func:') !== false) {
                    
$query .= $columns substr($value5) . ', ';
                } 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($query0, -2);
            if (
$parameters !== '')
                
$query .= ' WHERE ' $parameters;
        }
        
$res $this->query($query);
 
        return 
$res;
    }
 
    function 
fetch_array($result) {
        return 
mssql_fetch_array($resultMSSQL_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;
    }
 
}
 
?>

 

Categories: PHP, Tips Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.