Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

Exporting records to a text file


In this recipe, we will export records to a pipe-delimited text file in a SQL Server table.

Getting ready

For this recipe, choose a table with records that you want to export to a text file.

How to do it...

The following steps will import a text file into a SQL Server table:

  1. Open PowerShell ISE as an administrator.

  2. Import the SQLPS module as follows:

    #import SQL Server module
    Import-Module SQLPS -DisableNameChecking
  3. Add the following script and run it:

    $instanceName = "localhost"
    $databaseName = "SampleDB"
    $filename = "C:\DATA\Customers.txt"
    $delimiter = "|"
    
    Invoke-SqlCmd -Query "SELECT * FROM SampleText" -ServerInstance $instanceName -Database $databaseName |
    Export-Csv -Delimiter $delimiter -NoType $fileName

Once the script finishes the execution, check the text file that was created.

How it works...

As with the import recipe, there are a few ways to export records to text files. Here are some of the ways to do this:

  • The bcp utility

  • The Import/Export Wizard from SSMS...