none
Using Powershell to export SQL table data to text files

    Question

  • Re: Using Powershell to export SQL table data to text files

    Disclaimer ... Powershell ignorant.

    I am trying to write a powershell script to export SQL table data (exact tables will be the result of a query) to individual flat files. The files must have a column heading, "|" pipe delimited. This will run in production for a SQL 2005, 2008, 2012 and the os count be ???. I am currently working in Windows Server 2012, SQL 2014, and PS 4.0.

    Found some scripts at the Script Center downloads and they used export-csv cmdlet which seems to be in SqlServerCmdletSnapin100, SqlServerProviderSnapin100. When I did a get_PSSnapin "Microsoft.PowerSHell.core was returned. a -registered returned nothing.

    To start - how do I get the cmdlets and how do i register them?

    Wednesday, August 27, 2014 8:38 PM

Answers

  • You are trying to use a snapin that does not exist on your system.  THe snapin is not used in SQLServer 2008 and later with PowerShell V4.

    Try it this way:

    Import-Module sqlps
    $SQLquery='select * from bank03.dbo.ifs_exporttables'
    $result=invoke-sqlcmd -query $SQLquery -serverinstance fidev360bi02 -database bank03 
    $result |export-csv c:\temp\THREADLIST.csv -notypeinformation


    ¯\_(ツ)_/¯

    • Proposed as answer by jrv Thursday, August 28, 2014 3:39 PM
    • Marked as answer by TheBrenda Friday, August 29, 2014 5:16 PM
    Thursday, August 28, 2014 2:43 PM

All replies

  • Use BCP as it does this very easily.  BCP was designed to export SQLServer data.

    In order to use SQLServer CmdLets you must install SQLServer Management Studio (SSMS).


    ¯\_(ツ)_/¯

    Wednesday, August 27, 2014 8:46 PM
  • I have SSMS installed. I am not allowed to use BCP because it requires xp_cmdshell.
    Wednesday, August 27, 2014 9:45 PM
  • I have SSMS installed. I am not allowed to use BCP because it requires xp_cmdshell.

    Who told you that?  BCP is a commandline utility.  Just go to a prompt and type BCP.  It does not require anything but the SQLClient to be installed.


    ¯\_(ツ)_/¯

    Wednesday, August 27, 2014 9:49 PM
  • Export-Csv is part of every installation of PowerShell.


    ¯\_(ツ)_/¯

    Wednesday, August 27, 2014 9:52 PM
  • at  a prompt type SQLPS

    Now try:

    Help Invoke-SqlCmd -full
    help Export-Csv -full


    ¯\_(ツ)_/¯

    Wednesday, August 27, 2014 9:55 PM
  • I have read and developed a tsql script that uses bcp to export data from SQL table to flat file. And xp_cmdshell had to be turned on to allow it in a tsql script. My company has a new security initiative and will not allow xp_cmdshell.

    Thursday, August 28, 2014 2:25 AM
  • I have read and developed a tsql script that uses bcp to export data from SQL table to flat file. And xp_cmdshell had to be turned on to allow it in a tsql script. My company has a new security initiative and will not allow xp_cmdshell.

    BCP does not use SQL.  It just dumps a table just like PowerShell.

    See:

    PS C:\scripts> bcp ?
    usage: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe {dbtable | query} {in | out | queryout | format} dat
    afile
      [-m maxerrors]            [-f formatfile]          [-e errfile]
      [-F firstrow]             [-L lastrow]             [-b batchsize]
      [-n native type]          [-c character type]      [-w wide character type]
      [-N keep non-text native] [-V file format version] [-q quoted identifier]
      [-C code page specifier]  [-t field terminator]    [-r row terminator]
      [-i inputfile]            [-o outfile]             [-a packetsize]
      [-S server name]          [-U username]            [-P password]
      [-T trusted connection]   [-v version]             [-R regional enable]
      [-k keep null values]     [-E keep identity values]
      [-h "load hints"]         [-x generate xml format file]
      [-d database name]        [-K application intent]

    Just export the table using a control file.

    If you try to use TSQL to directly write a file then you are correct.  YOu need to have command execution turned on.  BCP does not need SQL.  It candirectly dump a table to almost nay format.

    Using SQL query is a server side operation.  BCP can do this with no server side operations.

    Contact you DBA or vendor to learn how to use the SQLServer utilities for exporting and importing data.  You do not need PowerShell to do this.

    I am and have been a SQL DBA for years.  I have heard this same complaint many times.  REad the documentation.  Search with Bing or Google to find exampkkes of exporting tables.  Post in the SQLServer forum for assistance.

    You do not want to do this with PowerShell.  PowerShell requires advanced technical skills and an advanced understanding of how to extract BCP was designed to do what you asked.


    ¯\_(ツ)_/¯

    Thursday, August 28, 2014 2:36 AM
  • Detailed BCP instructions:

    http://msdn.microsoft.com/en-us/library/ms162802.aspx


    ¯\_(ツ)_/¯

    Thursday, August 28, 2014 2:37 AM
  • You can also use SQLCMD to export to a CSV

    sqlcmd -S . -d AzureDemo -E -s, -W -Q "SELECT * FROM dbo02.ExcelTest" > ExcelTest.csvI prefer BCP.


    ¯\_(ツ)_/¯

    Thursday, August 28, 2014 2:41 AM
  • We are getting a little off base here. When I run this on os Windows 2012 Server, Powershell 4.0, I get "SQLServerProviderSnapin100 is not registered with the system.". How do I get it registered?

    # Must be run on server with sql invoke-sqlcmd enabled 
    # This example grabs thread activity from MSDB and dumps it to a CSV in the same directory 
    # Requires SQL server snapins 
    # Load SqlServerProviderSnapin100 
    if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerProviderSnapin110'})) 
    { 
    if(Get-PSSnapin -registered | ?{$_.name -eq 'SqlServerProviderSnapin110'}) 
    { 
       add-pssnapin SqlServerProviderSnapin100 
       write-host "Loading SqlServerProviderSnapin100 in session" 
    } 
    else 
    { 
       write-host "SqlServerProviderSnapin100 is not registered with the system." -Backgroundcolor Red –Foregroundcolor White 
       break 
    } 
    } 
    else 
    { 
      write-host "SqlServerProviderSnapin100 is already loaded" 
    }  
     
    # Load SqlServerCmdletSnapin100 
    if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerCmdletSnapin100'})) 
    { 
    if(Get-PSSnapin -registered | ?{$_.name -eq 'SqlServerCmdletSnapin100'}) 
    { 
       add-pssnapin SqlServerCmdletSnapin100 
       write-host "Loading SqlServerCmdletSnapin100 in session" 
    } 
    else 
    { 
       write-host "SqlServerCmdletSnapin100 is not registered with the system." 
       break 
    } 
    } 
    else 
    { 
      write-host "SqlServerCmdletSnapin100 is already loaded" 
    } 
     
    Add-PSSnapin SqlServerCmdletSnapin100 
    Add-PSSnapin SqlServerProviderSnapin100 
     
    $SQLquery =@" 
     
    select * from bank03.dbo.ifs_exporttables
     
    "@ 
     
    $result = invoke-sqlcmd -query $SQLquery -serverinstance "fidev360bi02" -database bank03 
    $result |export-csv c:\temp\THREADLIST.csv -notypeinformation

    Thursday, August 28, 2014 1:25 PM
  • You have to install it.  We cannot help in a script.  Just install Management Studio and the PowerShell shell. 

    http://msdn.microsoft.com/en-us/library/hh231683.aspx

    If you cannot figure out how to do this ask your DBA or systemAdmin or post in the SQLServer forum.


    ¯\_(ツ)_/¯

    Thursday, August 28, 2014 1:29 PM
  • I have Windows Server 2012 R2, SQL Server 2014 (SSMS), PowerShell 4.0. All 64 bit. When I did a get_PSSnapin "Microsoft.PowerSHell.core was returned. a -registered returned nothing. What question is how do i register SqlServerCmdletSnapin100?

    I can run an Invoke-Sqlcmd from a PowerShell prompt. But I downloaded a script that I want to use SqlServerCmdletSnapin100 so that it can use export-csv and I cannot get it to run. ("Add-{SSma[om : No snap-ins have been registered for Windows PowerShell version4.)

    Could it have something to do with the 64 bit. I have read that all SSMS is 32 bit. Is there a 32 bit Powershell version 4.0? I cannot find it.

    Thursday, August 28, 2014 2:28 PM
  • You are trying to use a snapin that does not exist on your system.  THe snapin is not used in SQLServer 2008 and later with PowerShell V4.

    Try it this way:

    Import-Module sqlps
    $SQLquery='select * from bank03.dbo.ifs_exporttables'
    $result=invoke-sqlcmd -query $SQLquery -serverinstance fidev360bi02 -database bank03 
    $result |export-csv c:\temp\THREADLIST.csv -notypeinformation


    ¯\_(ツ)_/¯

    • Proposed as answer by jrv Thursday, August 28, 2014 3:39 PM
    • Marked as answer by TheBrenda Friday, August 29, 2014 5:16 PM
    Thursday, August 28, 2014 2:43 PM
  • OK, executed PowerShell from within SSMS and it is x86 but the same error "no snap-ins have been registered for Windows Powershell version 4".

    I have seen something about Windows 2012 needing to turn on the Command Line Tools feature. But cannot find how to do that. I know where Add Features is located, just cannot find a Command Line Tools feature.

    Thursday, August 28, 2014 3:03 PM
  • Removed the checks for SqlServerProviderSnapin100 and the Add-PSSnapin and it worked.

    Thursday, August 28, 2014 3:28 PM
  • BCP doesn't support column headers.
    Wednesday, December 5, 2018 7:41 PM
  • BCP doesn't support column headers.

    Since when.  It can dump in almost any format and the control file can specify any header mapping.  Go back and read the documents more carefully.


    \_(ツ)_/

    Wednesday, December 5, 2018 8:24 PM
  • BCP does not support column headers either with or without the format file. 

    https://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an

    https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

    https://docs.microsoft.com/en-us/sql/relational-databases/import-export/create-a-format-file-sql-server?view=sql-server-2017

    Wednesday, December 5, 2018 9:09 PM
  • Wednesday, December 5, 2018 10:02 PM
  • The point is that they are workarounds because bcp does not support column headers.
    Wednesday, December 5, 2018 10:23 PM
  • We can also use the SQLBulkCopy API to import/export to other file formats including CSV, TSV and fixed with headers.

    The command line is designed as a native format high speed utility that uses the API to copy tables between servers or to save data in a file for later import.  This is why it is called a "copy" utility and not an "export" utility.


    \_(ツ)_/

    Wednesday, December 5, 2018 10:29 PM