#requires -version 4
#requires -modules PSLogging
<#
.SYNOPSIS
Export in parallel sql server tables into csv files using bcp utility
.DESCRIPTION
Export in parallel sql server tables into csv files using bcp utility
bcp docs: https://docs.microsoft.com/en-us/sql/tools/bcp-utility
.PARAMETER CSVPath
Specifies path to out csv file(s) and log file
.PARAMETER ServerName
Specifies a SQL Server name
.PARAMETER DatabaseName
Specifies a database name
.PARAMETER CodePage
Specifies code page
.PARAMETER SchemaName
Specifies shema name. Use this parameter with parameter TableName
.PARAMETER TableName
Specifies table name.
.PARAMETER ExcludeColumns
Specifies which columns will be excluded.
.PARAMETER OrderByColumns
Sets order by specific columns.
.PARAMETER MinRowCount
Specifies a minimum row count for table(s) which will be unloaded
.PARAMETER MaxRowCount
Specifies a maximum row count for table(s) which will be unloaded
.PARAMETER FormatFile
Specify format file
.PARAMETER FieldTerminator
Specify field terminator
.PARAMETER RowTerminator
Specify row terminator
.PARAMETER FileExtension
Determines the file extension
.PARAMETER Collation
Specifies collation. Using collation parameter not allowed without format file parameter ('xml' or 'fmt')
.PARAMETER OutputColumnHeaders
Specifies presence of headers - true - export with headers; false - export without headers
.PARAMETER SavePhysicalOrder
Specifies column order. False - Alphabet column order type; true - physical column order type
.PARAMETER OrderByColumns_term
Sets order by specific columns
.OUTPUTS Log File
The script log file stored in C:\Windows\Temp\.log
.NOTES
Version:
Author: Konstantin Taranov [email protected]
Author Modified: Alexander Titenko [email protected]
Creation Date: 2017-09-26
Last modified: 2017-09-29
Purpose/Change: Add PSLogging module
.EXAMPLE
Export-SQLTableToCSV -CSVPath D:\12\ -ServerName NL-04 -DatabaseName NIIGAZ -MinRowCount 1 -MaxRowCount 3;
#>
#---------------------------------------------------------[Initialisations]--------------------------------------------------------
#Set Error Action to Silently Continue
$ErrorActionPreference = 'SilentlyContinue'
#Import Modules & Snap-ins
Import-Module PSLogging
#----------------------------------------------------------[Declarations]----------------------------------------------------------
#Script Version
$sScriptVersion = '1.1'
#Log File Info
$sLogPath = 'D:\1\'
$sLogName = 'Export-SQLTableToCSV.log'
$sLogFile = Join-Path -Path $sLogPath -ChildPath $sLogName
#-----------------------------------------------------------[Functions]------------------------------------------------------------
Function Export-SQLTableToCSV
{
Param(
# Specifies path to out csv file(s) and log file
[Parameter(Mandatory=$true)]
[string]$CSVPath,
# Specifies a SQL Server name
[Parameter(Mandatory=$true)]
[string]$ServerName,
# Specifies a database name
[Parameter(Mandatory=$true)]
[string]$DatabaseName,
[Parameter(Mandatory=$false)]
[string]$CodePage = '-C65001',
[Parameter(Mandatory=$false)]
[string]$ExcludeColumns,
[Parameter(Mandatory=$false)]
[string]$SchemaName,
[Parameter(Mandatory=$false)]
[string]$TableName,
[Parameter(Mandatory=$false)]
[string]$OrderByColumns,
[Parameter(Mandatory=$false)]
[string]$MinRowCount,
[Parameter(Mandatory=$false)]
[string]$MaxRowCount,
[Parameter(Mandatory=$false)]
[string]$FormatFile,
[Parameter(Mandatory=$false)]
[string]$FieldTerminator,
[Parameter(Mandatory=$false)]
[string]$RowTerminator,
[Parameter(Mandatory=$false)]
[string]$FileExtension,
[Parameter(Mandatory=$false)]
[string]$Collation,
[Parameter(Mandatory=$false)]
[switch]$OutputColumnHeaders,
[Parameter(Mandatory=$false)]
[switch]$SavePhysicalOrder,
[Parameter(Mandatory=$false)]
[string]$OrderByColumns_term = ','
)
Begin
{
Write-LogInfo -LogPath $sLogFile -Message "Srart export SQL Tables from $DatabaseName in $CSVPath with bcp utility"
$sw = [Diagnostics.Stopwatch]::StartNew()
Write-Host "[*] Start script at $(Get-Date -Format 'HH:mm:ss')" -foreground:yellow;
}
Process
{
Try
{
if (-not(Test-Path $CSVPath))
{
$message = "Path $CSVPath doesn't exist!"
Write-Host $message -ForegroundColor Red
Write-LogInfo -LogPath $sLogFile -Message $message
break
}
$alllowableFormatFileTypes = @('','xml','fmt')
if ($alllowableFormatFileTypes -notcontains $FormatFile)
{
$message = "Allowed values for parameter FormatFile is xml, fmt or blank value"
Write-Host $message -ForegroundColor Red
Write-LogInfo -LogPath $sLogFile -Message $message
break
}
if (-not($FormatFile) -and $Collation)
{
$message = "Using collation parameter not allowed without format file parameter ('xml' or 'fmt')"
Write-Host $message -ForegroundColor Red
Write-LogInfo -LogPath $sLogFile -Message
break
}
if ($ExcludeColumns[0] -notcontains '[' -and $ExcludeColumns -notcontains '')
{
$message = "Parameter orderByColumns must be in square brackets and separated by comma"
Write-Host $message -ForegroundColor Red
Write-LogInfo -LogPath $sLogFile -Message
break
}
if ($ExcludeColumns[0] -contains '[' -and $FormatFile -notcontains '')
{
$message = "Parameter ExcludeColumns must be blank, since parameter FormatFile is present"
Write-Host $message -ForegroundColor Red
Write-LogInfo -LogPath $sLogFile -Message
break
}
if ($ExcludeColumns -and ($SchemaName -or $TableName))
{
$message = "Parameter ExcludeColumns must be blank, since parameters SchemaName or TableName are present"
Write-Host $message -ForegroundColor Red
Write-LogInfo -LogPath $sLogFile -Message
break
}
if ($OrderByColumns -and ($SchemaName -or $TableName))
{
$message = "Parameter OrderByColumns must be blank, since parameters SchemaName or TableName are present"
Write-Host $message -ForegroundColor Red
Write-LogInfo -LogPath $sLogFile -Message
break
}
if (-not($OutputColumnHeaders))
{
$message = "Headers won't output. If you want headers run with switch parameter: -OutputColumnHeaders"
Write-Host $message -ForegroundColor Yellow
Write-LogInfo -LogPath $sLogFile -Message $message
}
if (-not($SavePhysicalOrder))
{
$message = "Alphabet column order type use. If you want column physical order run with switch parameter: -SavePhysicalOrder"
Write-Host $message -ForegroundColor Yellow
Write-LogInfo -LogPath $sLogFile -Message $message
}
else
{
$message = "Physical column order is used"
Write-Host $message -ForegroundColor Yellow
Write-LogInfo -LogPath $sLogFile -Message "Use physical column order"
}
if ($Collation)
{
$invokeCollation = Invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -Verbose -Query "
select COUNT(*) from sys.fn_helpcollations() WHERE name = '$Collation';";
if ($invokeCollation.Column1 -eq 0)
{
$message = "Defined collation not found in server collations list"
Write-Host $message -ForegroundColor Yellow
Write-LogInfo -LogPath $sLogFile -Message $message
$Collation = ''
}
}
if ($FieldTerminator)
{
$FieldTerminatorToHeaders = $FieldTerminator
$FieldTerminator = '-t"' + $FieldTerminator + '"';
}
if ($RowTerminator)
{
$RowTerminator = '-r"' + $RowTerminator + '"'
}
if ($CodePage)
{
$CodePage = '-c ' + $CodePage + ''
}
if ($FormatFile -and $FormatFile -eq 'xml')
{
$FormatFileForBcp = 'xml" -x'
}
else
{
$FormatFileForBcp = 'fmt"'
}
if ($ShemaName)
{
$tableDependencySchemaName = "and t.schema_id = (SELECT SCHEMA_ID('$ShemaName'))"
}
else
{
$tableDependencySchemaName = ''
}
if ($TableName)
{
$tableDependencyTableName = "and t.name = '$TableName'"
}
else
{
$tableDependencyTableName = ''
}
$tableDependecy = Invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -Verbose -Query "
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) AS SchemaName
, QUOTENAME(t.name) AS TableName
, SUM(ps.row_count) AS TableRowCount
FROM sys.tables t
INNER JOIN sys.dm_db_partition_stats ps
ON ps.object_id = t.object_id
WHERE index_id < 2
GROUP BY t.name
, t.schema_id
HAVING SUM(ps.row_count) >= $MinRowCount AND SUM(ps.row_count) <= $MaxRowCount $tableDependencySchemaName $tableDependencyTableName
ORDER BY SUM(ps.row_count) ASC
OPTION (RECOMPILE);
";
$tableDependecy
Foreach ($t in $tableDependecy)
{
$tableFullName = $t[0] + '.' + $t[1]
$ShemaName = $t[0].Split('[').Split(']')
$TableName = $t[1].Split('[').Split(']')
$ShemaName = ([string]$ShemaName).Trim()
$TableName = ([string]$TableName).Trim()
$tableFullNameAndFileExt = $TableFullName + '.' + $FileExtension
$filePath = $CSVPath + $TableFullName + '.' + $FileExtension
$filePathWithHeaders = $CSVPath + $TableFullName + '_headers.' + $FileExtension
$FormatFilePath = $CSVPath + $TableFullName + '.' + $FormatFile
$FormatFileDelPath = $CSVPath + $TableFullName + 'del.' + $FormatFile
Write-Debug "tableFullName:$tableFullName`
ShemaName:$ShemaName`
trimmedShemaName:$trimmedShemaName`
TableName:$TableName`
trimmedTableName:$trimmedTableName`
tableFullNameAndFileExt:$tableFullNameAndFileExt`
filePath:$filePath`
filePathWithHeaders:$filePathWithHeaders`
FormatFilePath:$FormatFilePath`
FormatFileDelPath:$FormatFileDelPath"
if ($OrderByColumns)
{
$CountOrderByColumns = $OrderByColumns.split($OrderByColumns_term).Count
$invokeCountOrderByColumns = Invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -Verbose -Query "
SELECT COUNT(*)
FROM sys.columns sac
WHERE sac.object_id = (SELECT OBJECT_ID('$tableFullName')) AND Name IN ('$OrderByColumns');
";
Write-Debug "OrderByColumns: $OrderByColumns`
CountOrderByColumns: $CountOrderByColumns"
if ($invokeCountOrderByColumns.Column1 -notin $CountOrderByColumns)
{
write-Host "Some columns in OrderByColumns not exists in table" -ForegroundColor Red
break
}
Write-Debug "invokeCountOrderByColumns.Column1: $invokeCountOrderByColumns.Column1"
}
if ($SavePhysicalOrder)
{
$columnsOrder = 'ORDER BY sac.column_id'
}
else
{
$columnsOrder = 'ORDER BY Name'
}
Write-Debug "SavePhysicalOrder: $SavePhysicalOrder`
columnsOrder: $columnsOrder"
$invokeOrderByColumns = Invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -Verbose -Query "
SELECT Name
FROM sys.columns sac
WHERE sac.object_id = (SELECT OBJECT_ID('$tableFullName')) AND Name NOT IN ('$ExcludeColumns') $columnsOrder;
";
Write-Debug "ExcludeColumns: $ExcludeColumns"
$Columns = ''
$ColumnsToHeaders = ''
foreach ($i in $invokeOrderByColumns.Name)
{
$Columns += '[' + $i + '],'
$ColumnsToHeaders += $i + $FieldTerminatorToHeaders
}
$Columns = $Columns -replace ".$"
$ColumnsToHeaders = "'" + ($ColumnsToHeaders -replace ".$") + "'"
Write-Debug "Columns: $Columns`
ColumnsToHeaders: $ColumnsToHeaders"
$bcpStatement = 'bcp "SELECT '+ $Columns +' FROM ['+ $ServerName +'].['+ $DatabaseName +'].['+ $ShemaName +'].['+ $TableName +']' + `
$OrdrByColumns + '" queryout "' + $CSVPath + '['+ $ShemaName +'].['+ $TableName +'].' + `
$FileExtension + '" -T -S ' + $ServerName + ' ' + $CodePage + ' ' + $FieldTerminator + ' ' + $RowTerminator + '';
Write-Debug "bcpStatement: $bcpStatement"
Invoke-Expression $bcpStatement
if ($OutputColumnHeaders)
{
$bcpStatement = 'bcp "SELECT ' + $ColumnsToHeaders + '" queryout "' + $CSVPath + '['+ $ShemaName +'].['+ $TableName +']_headers.' + `
$FileExtension + '" -T -S ' + $ServerName + ' ' + $CodePage + ' ' + $FieldTerminator + ' ' + $RowTerminator + '';
Write-Debug "bcpStatement: $bcpStatement"
Invoke-Expression $bcpStatement
}
cmd /C "copy /b $filePathWithHeaders + $filePath $filePathWithHeaders"
cmd /C "del $filePath"
cmd /C "ren $filePathWithHeaders `"$tableFullNameAndFileExt`""
if ($FormatFile -eq ('xml' -or 'fmt') -and $SavePhysicalOrder)
{
$bcpStatement = 'bcp ['+ $DatabaseName +'].'+ $tableFullName +' format nul -c -f "' + $CSVPath + `
'['+ $ShemaName +'].['+ $TableName +'].' + $FormatFileForBcp + ' ' + $FieldTerminator + ' -T ';
Write-Debug "bcpStatement: $bcpStatement"
Invoke-Expression $bcpStatement
}
if ($FormatFile)
{
$tmpSchemaAndTableName = '['+ $ShemaName +'].[tmp' + $TableName + ']'
if ($SavePhysicalOrder)
{
$bcpStatement = 'bcp ['+ $DatabaseName +'].'+ $tableFullName +' format nul -c -f "' + $CSVPath + `
'['+ $ShemaName +'].['+ $TableName +']del.' + $FormatFileForBcp + ' ' + $FieldTerminator + ' -T ';
Write-Debug "bcpStatement: $bcpStatement"
Invoke-Expression $bcpStatement
}
else
{
$invokeStatement = 'IF OBJECT_ID(''['+ $DatabaseName +'].'+ $tmpSchemaAndTableName +''') IS NOT NULL DROP TABLE ' +$tmpSchemaAndTableName +';
SELECT ' + $Columns + ' INTO ' +$tmpSchemaAndTableName +' FROM ['+ $DatabaseName +'].'+ $tableFullName +' WHERE 1=2;'
Write-Debug "invokeStatement: $invokeStatement"
Invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -Verbose -Query $invokeStatement
}
}
if ($FormatFile)
{
$invokeCollation = Invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -Verbose -Query "
SELECT CONVERT(VARCHAR(128), DATABASEPROPERTYEX('$DatabaseName', 'collation'));
;";
$CurrentCollation = $invokeCollation.Column1
Write-Debug "CurrentCollation: $CurrentCollation"
if ($FormatFile -eq 'xml')
{
$lob = 'SINGLE_NCLOB'
}
if ($FormatFile -eq 'fmt')
{
$lob = 'SINGLE_CLOB'
}
if ($Collation -eq '' -and $FormatFile -eq 'fmt')
{
$Collation = '""""'
}
if ($Collation -eq '' -and $FormatFile -eq 'xml')
{
$Collation = ''
}
$bcpStatement = 'bcp "SELECT REPLACE(BulkColumn, '''+ $CurrentCollation +''', '''+ $Collation +''') AS BulkColumn
FROM OPENROWSET(BULK '''+ $FormatFileDelPath +''', '+ $lob +') as x" queryout "'+ $FormatFilePath +'" -c -T'
Write-Debug "bcpStatement - $bcpStatement"
Invoke-Expression $bcpStatement
cmd /C "del $FormatFileDelPath"
}
}
}
Catch
{
Write-LogError -LogPath $sLogFile -Message $_.Exception -ExitGracefully
Break
}
}
End
{
If (-not $Error)
{
Write-Host 'Completed Successfully.' -ForegroundColor Green
Write-Host "[*] Stop script at $(Get-Date -Format 'HH:mm:ss')" -foreground:yellow;
$sw.Stop();
$sw.Elapsed | Format-Table -Property Minutes, Seconds, Milliseconds -AutoSize;
Write-LogInfo -LogPath $sLogFile -Message 'Completed Successfully.'
Write-LogInfo -LogPath $sLogFile -Message ' '
}
else
{
Write-Error -ErrorRecord
Write-LogInfo -LogPath $sLogFile -Message $Error
}
}
}
#-----------------------------------------------------------[Execution]------------------------------------------------------------
Start-Log -LogPath $sLogPath -LogName $sLogName -ScriptVersion $sScriptVersion
Export-SQLTableToCSV -CSVPath D:\1\ -ServerName NL-04 -DatabaseName NIIGAZ -MinRowCount 1 -MaxRowCount 2 -CodePage '-C65001' -FileExtension txt -FieldTerminator '|' -RowTerminator '\n' -OutputColumnHeaders -SavePhysicalOrder -Debug -FormatFile xml
Stop-Log -LogPath $sLogFile