I had a request this morning for something I though was actually very simple:
Client: “Can you extract all data for these particular tables including column headers to a tab delimited .txt file?”
Chris: “Sure, no problem, I’ll just run bcp querying sys.tables using a COALESCE loop to output the statements”
Client: “Top stuff, let me know when it’s done”
So, away I went generating my script which took a matter of minutes and run it…….where’s the column headers? Bugger, forgot that bcp doesn’t output column headers without doing some funky stuff by creating a header record in a separate file and merging that with the file of data.
With this in mind I knew creating a SSIS package (or using export data to generate – very manual unless I delved into the realms of BIML) could do this but I thought I’d have a look at powershell invoking sqlcmd.
Again, this all seemed to be going very well until I came to outputting the data to a tab delimited .txt file. As far as I’m aware Powershell does not have an Export-Txt so I had to look into how I can use the Export-Csv to actualy output to .txt tab delimited as opposed to comma separated and found the parameter -delimiter “`t” – Excellent!!! Added this in and run the script…………and the first row consisted of “#TYPE System.Data.DataRow” – wft!?!?!?!?!
Quick search on my search engine of choice showed that there is a parameter that you can pass in to remove this from the export -NoTypeInformation.
Run it again with -NoTypeInformation and everything worked as expected apart from all column headers and data had quotes (“) around them which was not part of the requirement. Unfortunately (as far as I know) there is no switch, parameter or the likes that does this so I had to change the Export-Csv to ConvertTo-Csv and run a Replace on ‘”‘ with ” which managed to do the trick.
I’ve included the script below which can be tailored to your needs:
$server = 'ServerInstanceHere'
$database = 'DBNameHere'
$path = 'c:\work\ToDelete\'
$query = "SELECT name FROM sys.tables WHERE name in (
'TableNameHere_1',
'TableNameHere_2'
--etc etc
)"
$queryToOut = "SELECT * FROM $TableName"
#Get list of table names to output data
$Tables = invoke-sqlcmd -query $query -database $database -serverinstance $server
foreach ($Table in $Tables)
{
$TableName = $Table["name"]
write-host -ForegroundColor Green "Creating File $TableName.txt"
invoke-sqlcmd -query $queryToOut -database $database -serverinstance $server | `
#Convert as opposed to Export to replace quotes if required
ConvertTo-Csv -NoTypeInformation -delimiter "`t" | `
ForEach-Object {$_ -Replace('"','')} | `
Out-file $path$TableName.txt
#Export-Csv -NoTypeInformation -delimiter "`t" -path $path$TableName.txt
}
Apologies for the formatting but the powershell script tag doesn’t seem to format it the way I’m wanting it to so here is a screen shot of the code:
