CSV file from SQL Server

Question

I need to run a series of queries and export the results to a flat file, but this file is kind of special:

1. The column separator has to be a |
2. I need to place " around char or varchar columns BU if any char or varchar column is NULL for a particular row, I must not place ". If the value is an empty string, then yes, i need to place them.

I have found a workaround by building the entire record in T-SQL, but I need to do this for each new query I have to build.

Any ideas

Answer 1

Here's a solution using PowerShell, as you'd tagged that in your question.

• Execute the SQL using the .Net library (i.e. as opposed to Invoke-SqlCmd). This avoids the path changing (side effect of the SQLPS module being imported), and means you can spot nulls by looking for DbNulls in the data.
• Remove those properties which are not requested data, but rather properties of the DataRow object.
• For each property on our result, check to see if it's a DBNull. If it is, replace it with a marker string (this string contains a GUID, so is highly unlikely to appear in your source data by chance).
• Convert the data to CSV, using the pipe delimeter
• Replace all occurrences of your marker enclosed in quotes with a blank (i.e. without quotes).
• Write the results to file

.

clear-host
function Invoke-SQLQuery {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]$ServerInstance 
        ,
        [Parameter(Mandatory = $true)]
        [string]$Database 
        ,
        [Parameter(Mandatory = $true)]
        [string]$Query
        ,
        [Parameter(Mandatory = $false)]
        [int]$CommandTimeoutSeconds = 30 #30 is the SQL default
    )
    begin {
        $connectionString = ("Server={0};Database={1};Integrated Security=True;" -f $DbInstance,$DbCatalog)
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = $connectionString
        $connection.Open()    
    }
    process {
        $command = $connection.CreateCommand()
        $command.CommandTimeout = $CommandTimeoutSeconds
        $command.CommandText = $query
        $result = $command.ExecuteReader()
        $table = new-object "System.Data.DataTable"
        $table.Load($result)
        $table | Convert-DataRowToPSCustomObject
    }
    end {
           $connection.Close()
    }
}
function Convert-DataRowToPSCustomObject {
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline = $true)]
        [System.Data.DataRow]$Row
    )
    process {
        [PSObject]($Row | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors)
    }
}
#this gives us a nice way to distinguish nulls from blanks in csv rows
function Convert-DbNullToMarker {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [PSObject]$Data
    )
    process {
        $Data | Get-Member -MemberType NoteProperty | % Name | %{
            if($Data.$_ -is [DbNull]) {
                $Data.$_ = 'DbNullMarker{fdb653bf-0810-4893-a076-e3d935d9e6ba}'
            }
        }
        $Data
    }
}

#this gives us a nice way to distinguish nulls from blanks in csv rows
function Convert-DbNullMarkerToNull {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [string]$CsvRow
    )
    process {
        $CsvRow -replace '"DbNullMarker{fdb653bf-0810-4893-a076-e3d935d9e6ba}"',''
    }
}


Invoke-SqlQuery -ServerInstance "." -Database "master" -Query "select * from (values (null, 'hello'), ('hi', 'world')) x(a, b)" | 
    Convert-DbNullToMarker -Verbose |
    ConvertTo-Csv -Delimiter '|' -NoTypeInformation |
    Convert-DbNullMarkerToNull |
    Out-File -FilePath (Join-Path $PSScriptRoot 'SqlCsvDemo.csv') -Encoding utf8 -Force

Answer 2

you can use SQLPS module and select your table

Import-Module Sqlps -DisableNameChecking;
Invoke-Sqlcmd -ServerInstance "yourservername" -Database "yourdatabasename" -Query "select top 2 zonetable1, zonetable2 from dbo.yourtable"  | 
    Export-Csv "c:\temp\result.csv" -NoTypeInformation

Answer 3

There are different options here:

• You can use the BCP tool to extract the data
• You may use Integration Services if you have to do it maybe once a day and schedule the package in SQL Agent.
• You can use the Export Wizard in the management Studio and choose the delimiter you need:

Right click on the database and click on Export Data. Keep going on the data source, and in the data destination put your file, Delimited as the Format and the (") as the text qualifier:

Then select the table where you are extracting the data (or a query) and the specify the "Vertical Bar" as the column delimiter.

The next step is just to run the task (here it also gives you the option to save the SSIS package to run it again whenever you want).

Hopes this helps you.