none
PowerShell to Dynamically download SQL table data to csv RRS feed

  • Question

  • Re: PowerShell to Dynamically download SQL table data to csv

    I worked on my first powershell today. My goal is to dynamically download SQL Table data to csv files. There is a ifs_ExportTables that is a controller table. If you want to download a table, then enter a row into ifs_ExportTables. I do not have all the bells and whistles yet, but would appreciate any feed back. (1) is there an easier way to reference the column data (2) is the loadwithpartialname the best way to load (3) will the loadwithpartialname work with sql 2005 - 2014 (4) is there a specific version of PS that is required (I am working with 4.0) .

    Utilmately this will go out to several clients that have SQL 2005 - 2014, who knows what OS, and I can require a certain version of

    CREATE DATABASE aaatest2
    CREATE TABLE [ifs_ExportTables](
    	[RecID] INT IDENTITY(1,1),
    	[ExportGroup] INT NULL, 
    	[TableName] VARCHAR(128) NULL,
    	[ExportPath] VARCHAR(4000),
    	[Delimiter] VARCHAR (128) NULL,
    	[ColumnHeaders] INT NULL,
    	[OutFileName] VARCHAR (128) NULL,
    	[OutFileAddDate] INT NULL,
    	[Enabled] INT NULL)
    CREATE TABLE [aatable1](
    	[RecID] INT IDENTITY(1,1),
    	[field1] INT NULL, 
    	[field2] VARCHAR(128) NULL)
    CREATE TABLE [aatable2](
    	[RecID] INT IDENTITY(1,1),
    	[field21] INT NULL, 
    	[field22] VARCHAR(128) NULL)
    INSERT INTO [ifs_ExportTables]([ExportGroup],[TableName],[Delimiter],[ColumnHeaders],[OutFileName],[OutFileAddDate],[Enabled])
    	VALUES (1, 'aatable1', '|', 0,'',1,1)
    	
    INSERT INTO [ifs_ExportTables]([ExportGroup],[TableName],[Delimiter],[ColumnHeaders],[OutFileName],[OutFileAddDate],[Enabled])
    	VALUES (1, 'aatable2', '|', 0,'',1,1)	
    INSERT INTO [aatable1]([field1],[field2])
    VALUES ('11', 'aaa')
    INSERT INTO [aatable1]([field1],[field2])
    VALUES ('111', 'bbb')
    INSERT INTO [aatable1]([field1],[field2])
    VALUES ('111', 'ccc')
    INSERT INTO [aatable2]([field21],[field22])
    VALUES ('22', 'ddd')
    INSERT INTO [aatable2]([field21],[field22])
    VALUES ('222', 'eee')
    INSERT INTO [aatable2]([field21],[field22])
    VALUES ('2222', 'fff')

    This is the PS code. Set your server and output path.

    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    $sqlsvr = 'fidev360bi02\fidev360bi02'
    $dir = 'c:\temp\pssmo\'
    $database = 'aaatest'
    $diagnostics = 1
    $sqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlsvr)
    $db = $sqlserver.databases[$database]
    $Tables = $db.ExecuteWithResults("Select * FROM ifs_exporttables")
    foreach ($DataRow in $Table[0].Tables[0].Rows)
        {
        $TableName = $DataRow[2].tostring()    # ifs_ExportTables.TableName
        $Delimiter = $DataRow[4].tostring()    # ifs_ExportTables.Delimiter
        $Header = $DataRow[5].tostring()       # ifs_ExportTables.ColumnHeaders 
        if ($diagnostics = 1) 
            {write-host -ForegroundColor Green "Creating Table $TableName to path $dir$TableName.csv" }
        $sqlserverR = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlsvr)
        $dbR = $sqlserverR.databases[$database]
        $TablesR = $dbR.ExecuteWithResults("Select * FROM $TableName")
        $result = $TablesR[0].tables.item(0)
        if ($Header -eq '1')
        	{$result |export-csv -Delimiter $Delimiter –notype -path $dir$TableName.csv  -Encoding ASCII}
        else
    	    {$result | ConvertTo-Csv -NoTypeInformation -Delimiter $Delimiter | Select-Object -Skip 1 | Out-File -FilePath $dir$TableName.csv  -Encoding ASCII}
        }

    Friday, August 29, 2014 11:12 PM

Answers

All replies

  • All of this is already built into SQLServer. Why are you trying to re-invent the wheel.

    You cannot implement this generally without pre- installing all of the correct modules.

    normally we would build export tasks into the database when it is built.  If you wan an external export then we would use BCP which is available on every version of SQLServer client tools.

    You are using SMO which is not the easiest way and is certainly not portable.  If you want portability use ADO,Net and the SQLServer Native Client.  It simplifies the code and does not require anything to be installed.  It also works on all versions of PowerShell.

    I am a certified MSSQLServer DBA and have been building databases with MSSQLServer for over 20 years.  Believe me when I say you are making this all way too complicated. I recommend taking a bit more time to study how MSSQL is delivered and how it is intended to be managed, backed up and exported.  SQLServer has native export to CSV methods and we can use bulk copy in PowerShell.

    Here is a PowerShell example of BulkCopy: http://1drv.ms/1pS3e6P

    The example is to SQL table from Excel but it can be to and from any data including CSV.  We can just change the connect string and it works. It is also very fast when the tables are very large.  Export-Csv from a datatable can be quite slow.

    BulkCopy works on all versions of PowerShell and all versions of SQLServer.


    ¯\_(ツ)_/¯

    • Proposed as answer by Sam Boutros Saturday, August 30, 2014 6:55 AM
    • Unproposed as answer by TheBrenda Saturday, August 30, 2014 1:50 PM
    Friday, August 29, 2014 11:57 PM
  • BulkCopy only writes to a destination table. It cannot be used to export SQL table data to a flat file. 

    BCP does not have an option to include header row.

    My ExportTables will be run as a SQL Agent Job from SQL 2005 - SQL 2014.

    In what way is SMO not portable? I thought all version of SQL from 2005 were shipped with SMO? 

    So ADO.Net and SQLServer Native client are more portable? In what way?

    Saturday, August 30, 2014 1:50 PM
  • Use the text provider as the destination connection and it will generate any kind of text file.  Comma delimited, pipe delimited, tab, or fixed.

    Load and study the provider reference.  BCP is built around the BulkCopy provider. BCP is actually the easiest and fastest way to export CSV files and is installed with the SQLClient in all versions of MSSQLServer.


    ¯\_(ツ)_/¯

    Saturday, August 30, 2014 2:04 PM
  • I cannot use BCP. It does not provide headers.

    Your link above references SqlBulkCopy. Please provide any kind of code sample, reference, article where it is exporting SQL Table data to a flat file.  During your 20 years you must have an example.

    Saturday, August 30, 2014 2:44 PM
  • I cannot use BCP. It does not provide headers.

    Your link above references SqlBulkCopy. Please provide any kind of code sample, reference, article where it is exporting SQL Table data to a flat file.  During your 20 years you must have an example.

    What gives you that idea.  It always provides headers when you give it a CSV spec.


    ¯\_(ツ)_/¯

    Saturday, August 30, 2014 2:46 PM
  • Google "bcp column headers" and your result pages will fill with people asking how to return column headers and people responding that either it cannot be done or giving some awkward work around. But try it yourself.

    Just change it to your server.

    $bcp2 = 'bcp.exe "SELECT top 5 * from sys.all_columns" queryout "C:\psbcpout.txt" -T -Sfidecc10 -c -t "|"'
    Invoke-Expression $bcp2

    Saturday, August 30, 2014 5:22 PM
  • Hi,

    This thread includ the answers for [thebrenda] questions (with the answer to how show headers in BCP, which by default do not write headers):

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/837d6fc6-6cf5-4435-a4da-e62198a726dd/exporting-table-data-into-text-files?forum=sqlgetstarted


    [Personal Site] [Blog] [Facebook]signature

    Thursday, September 4, 2014 2:17 PM
  • This thread (and the other referenced by pituach) sounds like this to me: "I have a specific requirement for retrieving data from SQL Server tables into CSV files, but I don't like the answers I've received so far." Sometimes technology is like this: You have to make do with what's possible/feasible. As pituach rightly pointed out, forum participants are volunteers who help for free.

    Also, please note that this is a scripting forum. The purpose of this forum is to answer specific scripting questions, not to design custom scripting solutions for specific scenarios. If you need that, I recommend hiring a consultant.


    -- Bill Stewart [Bill_Stewart]

    Thursday, September 4, 2014 2:31 PM
    Moderator
  • I appreciate the help that I receive on forums and am grateful that volunteers spend the time to help out. JRV and my discussion went a bit south. I disagreed with some of his statements and he disagreed with some of mind. As far as disagreements go, it was pretty civil. But if you want to rap me on the knuckles, go ahead.
    Thursday, September 4, 2014 3:57 PM
  • Here is the simplest form of an Export that will run on any version of Windows with PowerShell and the SQLClient installed.

    Here is the table "getter"

    # Get-SqlTable.ps1
    Param(
        $server=$env:COMPUTERNAME,
        $instance='SQLExpress',
        $dbname='issue',
        $tablename='Table_1',
        $schema='dbo'
    )
    
    $sqlConnection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$dbname;Data Source=$server\$instance"
    
    Try{
        $conn = New-Object System.Data.SqlClient.SqlConnection($sqlConnection)
        [void]$conn.open()
        $adapter=New-Object Data.Sqlclient.SqlDataAdapter("select * from [$schema].[$tablename]", $conn)
        $ds=new-object System.Data.DataTable
        [void]$adapter.Fill($ds)
        $ds
    }
    
    Catch{
        Write-Host "$_" -ForegroundColor red
    }
    
    Finally{
        $conn.Close()
    }
    
    

    Here is how to export a table:

    .\Export-SQLTable.ps1 -tablename MyTable -server Server1 -instance . -dbname MyDB -schema dbo | Export-Csv MyTable.Csv -NoType

    As long as the local account has access to the server this will export a table.  It is not as fast as BulkCopy but works well and does not require installing Management Studio or other tools.


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, September 4, 2014 4:12 PM
    Thursday, September 4, 2014 4:12 PM
  • I appreciate the help that I receive on forums and am grateful that volunteers spend the time to help out. JRV and my discussion went a bit south. I disagreed with some of his statements and he disagreed with some of mind. As far as disagreements go, it was pretty civil. But if you want to rap me on the knuckles, go ahead.

    Not rapp9ing you on the knuckles or wrapping you in anything ;)  Most of us have been using BCP to export to CSV when necessary however.  With SSIS this has become unnecessary.  Just define a set of Export tasks.  The SSIS built in Wizard will build the tasks then you can save them and load them into any copy of SQLServer 2005 and later.

    With the advent of PowerShell we can easily use ADO to grab a table and export it or use SQLBulkCopy to optimize the export.  I will try and do an example later if I get time.

    I found and posted an example of the ADO export version.


    ¯\_(ツ)_/¯

    Thursday, September 4, 2014 4:24 PM
  • Thanks for the example. That will really help. and I do appreciate your time.
    Thursday, September 4, 2014 5:36 PM
  • Thanks for the example. That will really help. and I do appreciate your time.

    You are welcome.

    You might also take a look at LogParser.  I believe it can query SQL databases to CSV and it is always very fast.


    ¯\_(ツ)_/¯

    Thursday, September 4, 2014 5:39 PM
  • I ended up using a combo of Powershell and SQL. My requirements were to dynamically download SQL Table Data to flat files. Provide the process with a list of tables/views and then process will download the data. I could not use bcp in SQL because my shop does not allow xp_cmdshell in SQL. I ended up with bcp in a powershell script. At first I tried a streamwriter and export-csv, but bcp was soo much faster. But bcp was not very flexible with column headers, datetime formatting, encloseing fields in quotes, numeric null values, etc. So I wrote a SQL proc that is called from powershell. The SQL proc takes in the table name a varioud parameters and creates a view of the data. Then the powershell script uses the view to bcp query out. below is some of my code (it is not perfect because i ripped out some company stuff, ripped out our internal logging system and renamed many of the objects. also not showing the ExportTableDefaultSettings table)

    1) table ExportTableDefaultSettings has the default settings for the Export GroupID (1 in this case since it is my first export group). ExportTableList will override the defaultsettings on a per table bases.

    1) table ExportTableList where you enter a row for each table/view to export along with settings for that table: columnheader, file path, file extension, doublequotes around fields, etc.

    3) Powershell script reads ExportTableDefaultSettings, then reads each row in ExportTableList. For each row it calls the SQL Proc to create the view and then execs bcp using the view.

    4) SQL proc read in a tablename or viewname with parameters and create a view v_ExportTable of the data. The view will typically be read by bcp to download SQL table data to flat files.  bcp does not have the options to include column headers, include fields in double quotes, format dates or use '0' for integer NULLS. Also, bcp have a limit of varhar 4000 and  wider tables could not be directly called by bcp. So create the v_ExportTable and have bcp SELECT v_ExportTable instead of the original table or view.

    Monday, September 8, 2014 9:08 PM
  • powershell code.

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo')
    # Server and Database Settings    
    $SQLServer = 'YOUR SERVER' 
    $Database = 'YOUR DATAQBASE'
    $ExportGroup = 1   #Number to identify this export group
    # Build Connection String
    $ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True"
    # Date
    $DateStr = Get-Date 
    $Date = $DateStr.ToString("yyyy-MM-dd")
    # Development Diagnostics
    $Diagnostics = 1
       
    # Function GetExportTablesDefaultSettings**************************************************************************
    Function GetExportTablesDefaultSettings
        {
        
        $global:SQLServer = $global:SQLServer.Trim()
        $global:Database = $global:Database.Trim()
          
        $connS = new-object Microsoft.SqlServer.Management.Common.ServerConnection($SQLServer)
        $connS.DatabaseName = $Database
        $costDataS = $connS.ExecuteWithResults("SELECT * from  ExportTableDefaultSettings  WHERE itemname like 'ExportTables%' and NumberInfo = $ExportGroup")
        $infoTableS = $costDataS.Tables[0]
        # For Each row in ifs_exportables result set
        foreach ($r in $infoTableS.Rows)
        {
        
            IF ($Diagnostics -eq 1)
                {echo $r.itemname.ToString(), $r.textinfo.ToString(), $r.dateinfo}
            
            Switch ($r.itemname.ToString())
            {
                'ExportTablesDefaultPath'                 {$global:DefaultExportPath = $r.TextInfo.ToString().Trim()} 
                'ExportTablesErrorPath'                   {$global:ExportErrorPath   = $r.TextInfo.ToString().Trim()} 
                'ExportTablesDestinationPath'             {$global:SharePath = $r.TextInfo.ToString().Trim()}                          
                'ExportTablesBackupPath'                  {$global:BackupPath = $r.TextInfo.ToString().Trim()}    
                'ExportTablesDefaultFileNameAddDate'      {$global:DefaultFileNameAddDate = $r.TextInfo.ToString().Trim()} 
                'ExportTablesDefaultDelimiter'            {$global:DefaultDelimiter = $r.TextInfo.ToString().Trim()}              
                'ExportTablesDefaultExtension'            {$global:DefaultFileExtension = $r.TextInfo.ToString().Trim()}              
                'ExportTablesZipFileName'                 {$global:ZipFileName  = $r.TextInfo.ToString().Trim()}              
                'ExportTablesDefaultColumnHeaders'        {$global:DefaultColumnHeaders = $r.TextInfo.ToString().Trim()}              
                'ExportTablesDefaultDoubleQuoteFields'    {$global:DefaultDoubleQuoteFields = $r.TextInfo.ToString().Trim()}               
                'ExportTablesDefaultEmbeddedDoubleQuotes' {$global:DefaultEmbeddedDoubleQuotes = $r.TextInfo.ToString().Trim()}        
                'ExportTablesDefaultNumberNULLValue'      {$global:DefaultNumberNULLValue = $r.TextInfo.ToString().Trim()}   
                'ExportTablesDefaultDateTimeFormat'       {$global:DefaultDateTimeFormat = $r.TextInfo.ToString().Trim()}     
                'ExportTablesLastBossDate'                {$global:LastBossDate = $r.DateInfo}                           
             }   
        }
        
        IF ("\" -ne $global:DefaultExportPath.Substring($global:DefaultExportPath.Length-1,1))
            {$global:DefaultExportPath = $global:DefaultExportPath + "\"}
        IF ("\" -ne $global:ExportErrorPath.Substring($global:ExportErrorPath.Length-1,1))
            {$global:ExportErrorPath = $global:ExportErrorPath + "\"}        
        IF ("\" -ne $global:SharePath.Substring($global:SharePath.Length-1,1))
            {$global:SharePath = $global:SharePath + "\"}
        IF ("\" -ne $global:BackupPath.Substring($global:BackupPath.Length-1,1))
            {$global:BackupPath = $global:BackupPath + "\"}
        IF ($global:DefaultFileNameAddDate -ne 1)
            {$global:DefaultFileNameAddDate =  0} 
        IF ($global:DefaultFileExtension.Contains("."))
            {$global:DefaultFileExtension =  $global:DefaultFileExtension -replace '.',''}   
        IF (".7z" -ne $global:ZipFileName.SubString($global:ZipFileName.Length-3,3))
            {$global:ZipFileName = $global:ZipFileName + ".7z"}
        IF ($global:DefaultColumnHeaders -ne 1)
            {$global:DefaultFileExtension =  0}     
        IF ($global:DefaultDoubleQuoteFields -ne 1)
            {$global:DefaultDoubleQuoteFields =  0}     
        IF ($global:DefaultEmbeddedDoubleQuotes -ne 1)
            {$global:DefaultEmbeddedDoubleQuotes =  0}    
        }
    # Function Edit_ifs_ExportTableColumns *******************************************************************************
    Function Edit_ifs_ExportTableColumns
        {
        IF ($global:FileName -eq '')    
            {$global:FileName = $global:TableName} 
        IF (($global:FilePath -eq '') -or (!$global:FilePath))
            {$global:FilePath = $global:DefaultExportPath}   
        IF ("\" -ne $global:FilePath.Substring($global:FilePath.Length-1,1))
            {$global:FilePath = $global:FilePath + "\"}  
        IF (($global:FileNameAddDate -ne 1) -and ($global:FileNameAddDate -ne 0))
            {$global:FileNameAddDate = $global:DefaultFileNameAddDate}   
        IF ($global:FileNameAddDate -eq 1)    
            {$global:FileName = $global:FileName + "_" + $global:Date}         
        IF (($global:Delimiter -eq '') -or (!$global:Delimiter))
            {$global:Delimiter = $global:DefaultDelimiter}          
        IF (($global:FileExtension -eq '') -or (!$global:FileExtension))
            {$global:FileExtension = $global:DefaultFileExtension}         
        IF ($global:FileExtension.Contains("."))
            {$global:FileExtension =  $global:FileExtension -replace '.',''}           
        IF (($global:ColumnHeaders -ne 0) -and ($global:ColumnHeaders -ne 1))
            {$global:ColumnHeaders = $global:DefaultColumnHeaders}
        IF (($global:DoubleQuoteFields -ne 0) -and ($global:DoubleQuoteFields -ne 1))
            {$global:DoubleQuoteFields = $global:DefaultDateTimeFormat}
        IF (($global:Double_EmbeddedDoubleQuotes -ne 0) -and ($global:Double_EmbeddedDoubleQuotes -ne 1))
            {$global:Double_EmbeddedDoubleQuotes = $global:DefaultEmbeddedDoubleQuotes}
        IF ($global:NumNULLValue -eq '') 
            {$global:NumNULLValue = $global:DefaultNumberNULLValue}     
        IF ($global:DateTimeFormat -eq [System.DBNull]::Value) 
            {$global:DateTimeFormat = $global:DefaultDateTimeFormat}   
        
        }
    # Function FileMaintenance ****************************************************************************
    Function FileMaintenance
        {
        if (-not (test-path "$env:ProgramFiles\7-Zip\7z.exe")) {throw "$env:ProgramFiles\7-Zip\7z.exe needed"} 
        set-alias sz "$env:ProgramFiles\7-Zip\7z.exe" 
        
        # 7zip all files in the $directory
        sz a -t7z "$DefaultExportPath$ZipFileName" "$DefaultExportPath*.*" | Out-Null 
        #Copy Zip file to Share
        Copy-Item "$DefaultExportPath$ZipFileName" "$SharePath$ZipFileName"
        #Move Zip file to Backup
        $BackupFileName = $ZipFileName.Replace(".7z","_$Date.7z")
        IF (Test-Path "$BackupPath$BackupFileName")
            {Remove-item "$BackupPath$BackupFileName"}
        Move-Item "$DefaultExportPath$ZipFileName" "$BackupPath$BackupFileName"
        #Keep only 10 days of backups
        get-childitem "$BackupPath\*.*"  | where {$_.lastwritetime -lt (get-date).adddays(-10) -and -not $_.psiscontainer} |% {remove-item $_.fullname -force }
        }
    # Function CreateViewForExportTable *******************************************************************************
    Function CreateViewForExportTable ($TableName, $ColumnHeaders, $DoubleQuoteFields, $Double_EmbeddedDoubleQuotes, $NumNULLValue, $DateTimeFormat)
        {
        $SqlConnectionSV = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
        $SqlCmdSV = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmdSV.Connection = $SqlConnectionSV
        $Command = "EXECUTE ifs_ExportTablesBuildView '$TableName', '$ColumnHeaders', '$DoubleQuoteFields', '$Double_EmbeddedDoubleQuotes', '$NumNULLValue', '$DateTimeFormat'"
        $SqlCmdSV.CommandText = $Command
        $SqlAdapterSV = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapterSV.SelectCommand = $SqlCmdSV
        $DataSetSV = New-Object System.Data.DataSet
        $SqlAdapterSV.Fill($DataSetSV) | Out-Null 
        $SqlConnectionSV.Close()     
        $DataSetSV.Tables[0]
        }  
    # MAIN **********************************************************************************
    GetExportTablesDefaultSettings
    Remove-Item $DefaultExportPath\*.* | Where { ! $_.PSIsContainer }
    Remove-Item $ExportErrorPath\*.* | Where { ! $_.PSIsContainer }
    $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($SQLServer)
    $conn.DatabaseName = $Database
    $costData = $conn.ExecuteWithResults("SELECT * from ifs_exporttables WHERE [Enabled] = 1 AND ExportGroup = $ExportGroup AND (TableName <> '' AND TableName IS NOT NULL) ORDER BY RECID")
    $infoTable = $costData.Tables[0]
    # For Each row in ifs_exportables result set
    foreach ($r in $infoTable.Rows)
    {
       	$TableName = $r.TableName.ToString().Trim()
        IF ($Diagnostics -eq 1) 
            {echo $TableName    }
        $ColumnHeaders = $r.ColumnHeaders
        $FileName = $r.FileName.ToString().Trim() 
        $FileNameAddDate = $r.FileNameAddDate       
        $FileExtension = $r.FileExtension.ToString().Trim()    
        $FilePath = $r.FilePath.ToString().Trim() 
        $Delimiter = $r.Delimiter.ToString().Trim()
        $DoubleQuoteFields = $r.DoubleQuoteFields  
        $Double_EmbeddedDoubleQuotes = $r.Double_EmbeddedDoubleQuotes
        $NumNULLValue = $r.NumNULLValue.ToString().Trim()
        $DateTimeFormat = $r.DateTimeFormat
      
        Edit_ifs_ExportTableColumns
            
        CreateViewForExportTable "$TableName" "$ColumnHeaders" "$DoubleQuoteFields" "$Double_EmbeddedDoubleQuotes" "$NumNULLValue" "$DateTimeFormat"
        
        $FullFilePath = $FilePath + $FileName + "." + $FileExtension
        $FullErrorFilePath = $ExportErrorPath + $FileName + ".txt" 
        
        $bcpString = """SELECT * from v_ExportTable"" queryout ""$FullFilePath"" -T -S ""$SQLServer"" -d ""$Database"" -c -t ""$Delimiter"" -e ""$FullErrorFilePath"""
        Invoke-Expression "bcp.exe $bcpString" | Out-Null 
       
    } #foreach ($r in $infoTable.Rows)
    FileMaintenance

    Monday, September 8, 2014 9:08 PM
  • sql code to create view
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExportTablesCreateView]') AND type in (N'P', N'PC'))
    	DROP PROCEDURE [dbo].[ExportTablesCreateView]
    GO
    CREATE PROCEDURE [dbo].[ExportTablesCreateView]
    /****************************************************************************************
    ExportTablesCreateView
    Description:
    Read in a tablename or viewname with parameters and create a view v_ExportTable of the
    data. The view will typically be read by bcp to download SQL table data to flat files. 
    bcp does not have the options to include column headers, include fields in double quotes,
    format dates or use '0' for integer NULLS. Also, bcp have a limit of varhar 4000 and 
    wider tables could not be directly called by bcp. So create the v_ExportTable and have
    bcp SELECT v_ExportTable instead of the original table or view. 
    Parameters:
    @pTableName VARCHAR(128) - table or view to create v_ExportTable from
    @pColumnHeader INT =1 - include column headers in the first row
    @pDoubleQuoteFields INT = 1 - put double quotes " around all column values including column headers
    @pDouble_EmbeddedDoubleQuotes INT = 1 - This is usually used with @pDoubleQuoteFields INT = 1. 'ab"c"d would be 'ab""c""d. 
    @pNumNULLValue VARCHAR(1) = '0' - NULL number data types will export this value instead of bcp default of ''
    @pDateTimeFormat INT = 121 - DateTime data types will use this format value
    Example:
    EXEC ExportTablesCreateView 'custname', 1, 1, 1, '0', 121
    *****************************************************************************************/
    @pTableName VARCHAR(128),
    @pColumnHeader INT = 1,
    @pDoubleQuoteFields INT = 1,
    @pDouble_EmbeddedDoubleQuotes INT = 1,   
    @pNumNULLValue VARCHAR(1) = '0',
    @pDateTimeFormat INT = 121
    AS
    BEGIN
    DECLARE @columnname varchar(128)
    DECLARE @columnsize int
    DECLARE @data_type varchar(128)
    DECLARE @HeaderRow nvarchar(max) 
    DECLARE @ColumnSelect nvarchar(max)
    DECLARE @SQLSelect nvarchar(max)
    DECLARE @SQLCommand nvarchar(max)
    DECLARE @ReturnCode INT
    DECLARE @Note VARCHAR(500)
    DECLARE db_cursor CURSOR FOR  
    	SELECT COLUMN_NAME, ISNULL(Character_maximum_length,0), Data_type
    	FROM [INFORMATION_SCHEMA].[COLUMNS] 
    	WHERE TABLE_NAME = @pTableName AND TABLE_SCHEMA='dbo'
    	
    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @ColumnName, @ColumnSize, @Data_type
    SET @HeaderRow = ''
    SET @ColumnSelect = ''
     
    -- Loop through each of the @pTableColumns to build the SELECT Statement
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
    	BEGIN TRY
    	-- Put double quotes around each field - example "MARIA","SHARAPOVA"
    	IF @pDoubleQuoteFields = 1
    		BEGIN
    		
    		-- Include column headers in the first row - example "FirstName","LastName"
    		IF @pColumnHeader = 1
    				SET @HeaderRow = @HeaderRow +  '''"' + @ColumnName + '"'' as ''' + @columnname + ''',' 
    		-- Unsupported Export data type returns "" - example "",
    		IF @Data_Type in ('image', 'varbinary', 'binary', 'timestamp', 'cursor', 'hierarchyid', 'sql_variant', 'xml', 'table', 'spatial Types')  
    			SET @ColumnSelect = @ColumnSelect + '''""'' as [' + @ColumnName + '],'
    			
    		-- Format DateTime data types according to input parameter			
    		ELSE IF	@Data_Type in ('datetime', 'smalldatetime', 'datetime2', 'date', 'datetimeoffset')       
    			-- example - CASE when [aaa] IS NULL THEN '""' ELSE QUOTENAME(CONVERT(VARCHAR,[aaa], 121), CHAR(34)) END AS [aaa],			
    			SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN ''""'' ELSE QUOTENAME(CONVERT(VARCHAR,[' + @columnname + '],' + CONVERT(VARCHAR,@pDateTimeFormat) + '), CHAR(34)) END AS [' + @ColumnName + '],'
    			
    		-- SET Numeric data types with NULL value according to input parameter
    		ELSE IF	@Data_Type in ('bigint', 'numeric', 'bit', 'smallint', 'decimal', 'smallmoney', 'int', 'tinyint', 'money', 'float', 'real')       
    			-- example - CASE when [aaa] IS NULL THEN '"0"' ELSE QUOTENAME([aaa], CHAR(34)) END AS [aaa],			
    			SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN ''"' + @pNumNULLValue + '"'' ELSE QUOTENAME([' + @columnname + '], CHAR(34)) END AS [' + @ColumnName + '],'
    			
    		ELSE	
    		
    		-- Double embedded double quotes - example "abc"d"ed" to "abc""d""ed". Only applicible for character data types.		
    		IF @pDouble_EmbeddedDoubleQuotes = 1
    			BEGIN
    				-- example - CASE when [aaa] IS NULL THEN '""' ELSE '"' + REPLACE([aaa],'"','""') + '"' END AS [aaa],
    				SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @ColumnName + '] IS NULL THEN ''""'' ELSE ''"'' + REPLACE([' + @ColumnName + '],''"'',''""'') + ''"'' END AS [' + @ColumnName + '],'
    			END
    		-- DO NOT PUT Double embedded double quotes - example "abc"d"ed" unchanged to "abc"d"ed"
    		ELSE
    			BEGIN	
    				-- example - CASE when [aaa] IS NULL THEN '""' ELSE '"' + [aaa] + '"' END AS [aaa],
    				SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @ColumnName + '] IS NULL THEN ''""'' ELSE ''"'' + [' + @ColumnName + '] + ''"'' END AS [' + @ColumnName + '],'
    		 	END	
    		END
    	
    	-- DO NOT PUT double quotes around each field - example MARIA,SHARAPOVA
    	ELSE
    		BEGIN
    		
    		-- Include column headers in the first row - example "FirstName","LastName"
    		IF @pColumnHeader = 1
    	 		SET @HeaderRow = @HeaderRow + '''' +  @ColumnName + ''' as ''' + @columnname + ''',' 
    	 		
    		-- Unsupported Export data type returns '' - example '',
    		IF @Data_Type in ('image', 'varbinary', 'binary', 'timestamp', 'cursor', 'hierarchyid', 'sql_variant', 'xml', 'table', 'spatial Types')  
    			SET @ColumnSelect = @ColumnSelect + ''''' as [' + @ColumnName + '],'
    		-- Format DateTime data types according to input parameter		
    		ELSE IF	@Data_Type in ('datetime', 'smalldatetime', 'datetime2','date', 'datetimeoffset')       
    			-- example - CASE when [aaa] IS NULL THEN '''' ELSE CONVERT(VARCHAR,[aaa], 121) END AS [aaa],					
    			SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN '''' ELSE CONVERT(VARCHAR,[' + @columnname + '],' + CONVERT(VARCHAR,@pDateTimeFormat) + ') END AS [' + @ColumnName + '],'
    		
    		-- SET Numeric data types with NULL value according to input parameter
    		ELSE IF	@Data_Type in ('bigint', 'numeric', 'bit', 'smallint', 'decimal', 'smallmoney', 'int', 'tinyint', 'money', 'float', 'real')       
    			-- example - CASE when [aaa] IS NULL THEN '"0"' ELSE CONVERT(VARCHAR, [aaa]) END AS [aaa],			
    			SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN ''' + @pNumNULLValue + ''' ELSE CONVERT(VARCHAR,[' + @columnname + '])  END AS [' + @ColumnName + '],'
    		ELSE 	
    			BEGIN
    			-- Double embedded double quotes - example "abc"d"ed" to "abc""d""ed". Only applicible for character data types.		
    			IF @pDouble_EmbeddedDoubleQuotes = 1
    				-- example - CASE when [aaa] IS NULL THEN '' ELSE CONVERT(VARCHAR,REPLACE([aaa],'"','""')) END AS [aaa],
    				SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN '''' ELSE CONVERT(VARCHAR,REPLACE([' + @columnname + '],''"'',''""''))  END AS [' + @ColumnName + '],'
    			ELSE		
    				-- example - CASE when [aaa] IS NULL THEN '' ELSE CONVERT(VARCHAR,[aaa]) END AS [aaa],
    				SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN '''' ELSE CONVERT(VARCHAR,[' + @columnname + '])  END AS [' + @ColumnName + '],'
    			END				
    		END
    			
        FETCH NEXT FROM db_cursor INTO @ColumnName, @ColumnSize, @Data_Type 
        END TRY
        
        BEGIN CATCH
    		RETURN (1)
        END CATCH
        
    END   
    CLOSE db_cursor   
    DEALLOCATE db_cursor
     
    BEGIN TRY 
    	-- remove last comma
    	IF @pColumnHeader = 1
    		SET @HeaderRow  = SUBSTRING(@HeaderRow , 1, LEN(@HeaderRow ) - 1)
    		
    	SET @ColumnSelect = SUBSTRING(@ColumnSelect, 1, LEN(@ColumnSelect) - 1)
    	-- Put on the finishing touches on the SELECT
    	IF @pColumnHeader = 1
    		SET @SQLSelect = 'SELECT ' + @HeaderRow  + ' UNION ALL ' +
    						 'SELECT ' + @ColumnSelect + ' FROM [' + @pTableName + ']'
    	ELSE
    		SET @SQLSelect = 'SELECT ' + @ColumnSelect + ' FROM [' + @pTableName + ']'
    		
    	---- diagnostics
    	---- PRINT truncates at 4k or 8k, not sure, my tables have many colummns
    	--PRINT @SQLSelect
    	--DECLARE @END varchar(max) = RIGHT(@SQLSelect, 3000)
    	--PRINT @end
    	--EXECUTE sp_executesql @SQLSelect
    	-- drop view if exists -- using view because some tables are very wide. one of my tables had a 33k select statement
    	SET @SQLCommand = '
    	IF EXISTS (SELECT * FROM SYS.views WHERE name = ''v_ExportTable'')
    	BEGIN
    		DROP VIEW v_ExportTable
    	END'
    	EXECUTE @ReturnCode = sp_executesql @SQLCommand
    	IF @returncode = 1
    		BEGIN	
    		RETURN (1)		
    		END
    	-- create the view
    	SET @SQLCommand = '
    	CREATE VIEW v_ExportTable AS ' + @SQLSelect
    	-- diagnostics
    	--print @sqlcommand
    	EXECUTE @ReturnCode = sp_executesql @SQLCommand
    	IF @returncode = 1
    		BEGIN	
    		RETURN (1)		
    		END
    	
    END TRY
    BEGIN CATCH
    	RETURN (1)
    END CATCH
    RETURN (0)
    END -- CREATE PROCEDURE [dbo].[ExportTablesCreateView]
    GO

    Monday, September 8, 2014 9:09 PM
  • You are still doing it the absolute hardest way.

    Just query the table with ADO and use Export-Csv.  The problem is that you have no relation information and now way to understand the data.  It is really only good for a simple dump but needs to be tested to reload.

    We can use XML to do a relational dump with embedded schema.

    Apparently you are more comfortabl ewith SQL than with PowerShel but remember that you may be build a huge support headache by doing a non-standard dump.

    Also note that sqlcmd can dump pure CSV files directly from a sql "SELECT" and can dump headers too.


    ¯\_(ツ)_/¯

    Monday, September 8, 2014 9:16 PM
  • You underestimate me. I am sure that i could find a harder way if I tried. :-)

    The total size of my output files are about 4gig. Not really large, but not tiny either.

    streamwriter spent over one hour on one of my 75 tables.

    invoke-sqlcmd got out of memory errors. never could get it to finish.

    export-csv  does not have a switch toexclude a column header. I tried using ConvertTo-Csv but it is not supported in PS 2.0.

    my solution takes under 20 minutes to download 75 tables that result in 4 gig of data (it is taking  longer to zip than for me to download.) I have total control over the output as there are 13 different settings to control the formatting of the output and I can add more if needed.

    my script also created the sql agent job with the PS code included and it dynamically sets the server, and database name. so for each installation, all they have to do is run the script and the job is created and scheduled. downloads the list of tables, zips, copies to the customer share, keeps a self maintaining backup. Works for me.

    Monday, September 8, 2014 11:40 PM
  • Unfortuanately al lof your assesments are based in incorr4ect or incomplete analysis.

    Export-Csv does have a NOHEADFER switch.  It can also define custom headers or just use the field names:

    Invoke-SqlCmd .... | Export-Csv file.csv


    ¯\_(ツ)_/¯

    Monday, September 8, 2014 11:52 PM
  • Here is a demo of how easy this is.

    PS SQLSERVER:\sql\omega\sqlexpress\databases\issue> Invoke-SQlCmd -Query 'select * from table_1'|Export-Csv c:\temp3\table_1.csv -NoTypeInformation
    WARNING: Using provider context. Server = OMEGA\SQLEXPRESS, Database = issue.
    PS SQLSERVER:\sql\omega\sqlexpress\databases\issue> cat c:\temp3\table_1.csv
    "se_test1","se_test2","test3","se_text4"
    "1         ","2         ","3         ","4         "
    "2         ","2         ","2         ","2         "
    "3         ","3         ","3         ","3         "
    "4         ","4         ","4         ","4         "
    "5         ","5         ","5         ","5         "
    PS SQLSERVER:\sql\omega\sqlexpress\databases\issue>

    Now the table I used for a test has char(20) fields.  This can be easily adjusted.    All other vchar fields will not be padded. Dates and all will be in DB formats that are reloadable or we can design a query that extracts as we need.  I usually produce a simle query builder that adjusts the char fields as needed and use it on all data extractions.  We can do the same thing if you don't want headers but almost all CSV specs use headers.

    the commandline tool bcp exports to CSV with no headers by default which is what you originally complained about so all of this makes little sense to me.  It seems like you just want to make work for your self.

    '


    ¯\_(ツ)_/¯

    Tuesday, September 9, 2014 12:05 AM
  • Yeah, i replied without my notes in front of me. the export-csv does not have an easy way to get ride of the underlines if you want headers.

    invoke-sqlcmd got out of memory errors. never could get it to finish.

    Tuesday, September 9, 2014 12:50 AM
  • Yeah, i replied without my notes in front of me. the export-csv does not have an easy way to get ride of the underlines if you want headers.

    invoke-sqlcmd got out of memory errors. never could get it to finish.

    There are no underlines in a CSV file.  That is only the screen display formatter it is not in the file.  I think you need to learn what a CSV file is and a little about PowerShell.


    ¯\_(ツ)_/¯

    Tuesday, September 9, 2014 1:18 AM
  • Actually, i was right the first time. Invoke-Sqlcmd cmdlet does not have an option for no headers. If you believe that you can turn off column headers please show me in your emo example above.

    It was straight SQLCMD that had the column headers and then the dashed lines.

    I want one solution that can accomodate any table/view, and whatever the client wants. some want headers, some don't. I want a solution that can accomodate the client without me having to do any additional coding.

    Tuesday, September 9, 2014 2:26 AM
  • Actually, i was right the first time. Invoke-Sqlcmd cmdlet does not have an option for no headers. If you believe that you can turn off column headers please show me in your emo example above.

    It was straight SQLCMD that had the column headers and then the dashed lines.

    I want one solution that can accomodate any table/view, and whatever the client wants. some want headers, some don't. I want a solution that can accomodate the client without me having to do any additional coding.

    You said you couldn't use BCP because it had no headers.  now you are saying the opposite.  Why? 

    I showed you how to use Invoke-SqlCmd to Export a table.

    I told you that SQLCMD could export with headers.

    I told you that BCP could export with no header.

    I also noted that I use a custom filter to export Invoke-SqlCmd data any way I want.  You too can learn to use PowerShell and the MSSQLServer tools to do all of this. 


    ¯\_(ツ)_/¯

    Tuesday, September 9, 2014 2:42 AM
  •  If you believe that you can turn off column headers please show me in your demo example above.
    Tuesday, September 9, 2014 3:04 AM
  • For others who may need this solution I took some time to test a working example.  I also used a table that exisits in every database so the demo obly needs to have the file path and the server instance set to run correctly.

    Remember that, when running under the default of SQLPS, you must specify complete file paths or set the current drive to a file system drive.

    PS SQLSERVER:\>$instance='.\sqlexpress'
    PS SQLSERVER:\>$csvfile='c:\temp\columns.csv'
    PS SQLSERVER:\>$query='SELECT TOP 10 [name],[column_id],[system_type_id],[user_type_id],[max_length] FROM [master].[sys].[all_columns]'
    PS SQLSERVER:\>invoke-sqlcmd -ServerInstance $instance -Query $query | Export-Csv $csvfile -NoType
    PS SQLSERVER:\>cat $csvfile
    "name","column_id","system_type_id","user_type_id","max_length"
    "rsid","1","127","127","8"
    "rscolid","2","56","56","4"
    "hbcolid","3","56","56","4"
    "rcmodified","4","127","127","8"
    "ti","5","56","56","4"
    "cid","6","56","56","4"
    "ordkey","7","52","52","2"
    "maxinrowlen","8","52","52","2"
    "status","9","56","56","4"
    "offset","10","56","56","4"
    PS SQLSERVER:\>

    This is the easiest method and the most flexible method for dumping to CSV.

    The second easiest method is SSIS which can be built on any serve Management Studio and copied to othere servers.  SQLServer since 2005 has included the execution utility for non-SSIS instances.  SSIS has native CSV export facilities. An SSIS task can be built that will dump all tables of a database even if the table definitions are changed


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, September 10, 2014 12:44 PM
    Wednesday, September 10, 2014 12:39 PM
  • To add headers to an sql with tools just turnoff headers (-h-1) and use a union.

    SELECT 'FirstName' as FirstName, 'LastName' as LastName
      UNION
        SELECT FirstName, LastName FROM Person.Contact

    OR ---

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
    SELECT c1, c2, c3 FROM [table]

    We can use the schema to create a stored procedure that auto-generates these statements pre table requested.  The T-SQL for thisis not very difficult and can be found easily on the Internet.


    ¯\_(ツ)_/¯

    Wednesday, September 10, 2014 12:53 PM
  • To clarify - you cannot union non-character data types to character data types. For example, the following UNION will not work. That is part of the reason why I wrote ExportTablesCreateView. It references every datatype and handles them appropriately. Plus it gives you control over headers, double quoted fields, embedded double quotes, etc.

    SELECT 'IntGroupID' as IntGroupID, 'DateModified' as DateModified  UNION
        SELECT FirstName, LastName FROM Person.Contact

    Also, -h -1 will not work on SQLCMD when it is used in invoke-sqlcmd and piped to Export-CSV. So Export-CSV does not have a way to turn off column headers. There is a work around on the Internet to use ConvertTo-Csv and skip the header line when you do not want headers. but Convertto-csv does not work with Powershell 2.0.

    OK ... I am done. You will outlast me. I admit defeat.

    Wednesday, September 10, 2014 1:31 PM
  • A CSV is character type.  That rule only works when you are selecting or uni0oning tables.  We will select only as character type through conversion in the stored procedure.

    SQLCMD is NOT Invoke-SQLCMD.  YOu seem to need to learn your PowerShell a bit.

    Invoke SQLCMD exports with headers by default.  It needs no other help.  You haven't even tried any of these things yet.  Go back and study how Invoke-SQLCMD works. 

    You said you needed headers.  THat is how we get headers easily.  NOw it sonds like you are saying you really don't want headers.  If that is the case use BCP.


    ¯\_(ツ)_/¯

    Wednesday, September 10, 2014 2:18 PM