February 24, 2021 at 4:13 am
I'm exporting all tables in a databases to individual csv files using the code below and it's working well except that varbinary and timestamp datatypes are exporting as System.Byte[] instead of the value.
$server = "ServerName"
$database = "DatabaseName"
$tablequery = "SELECT schemas.name as schemaName, tables.name as tableName from sys.tables inner join sys.schemas ON tables.schema_id = schemas.schema_id"
#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection
#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()
# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
$queryData = "SELECT * FROM [$($Row[0])].[$($Row[1])]"
#Specify the output location of your dump file
$extractFile = "C:\ExportCsv\$($Row[0])_$($Row[1]).csv"
$command.CommandText = $queryData
$command.Connection = $connection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()
$DataSet.Tables[0] | Export-Csv $extractFile -NoTypeInformation
}?
Any ideas on how to return the actual value? I've dome some searching and know there are options but I can't seem to get the to work. For example:
$user = Get-ADUser -Identity 12345 -Properties *
$user.msExchMailboxGuid.GetType().FullName
$user?
Returns: System.BYTE[]
$string = (Get-ADUser -Identity '12345' -Properties *)
$bytes = [System.Text.Encoding]::Unicode.GetBytes($string.msExchMailboxGuid)
[System.Text.Encoding]::ASCII.GetString($bytes)?
Returns the actual value.
I can't figure out how to combine them.
February 24, 2021 at 8:25 am
1. If by timestamp you mean rowversion I see no point in exporting them. If you mean date types cast them to strings first.
2. CSV files contain text not binary so any binary needs to be encoded as a string. Look at ToBase64String().
February 24, 2021 at 9:06 pm
Thanks for the reply Ken, I'll exclude the rowversion columns and look into using the ToBase64String function.
February 25, 2021 at 10:06 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply