#Export with many different delimiter choices, with or without double quote qualifiers, and with or without headers. import-module -name SQLPS cls ############ Variables You Change BEGIN ############ $DB = "DST17000AeriesDemo_ID" $Servername = "localhost" $OutPath = "c:\PowerShellExtracts\DISTRICTNAME\EXTRACTVENDOR\Output" $delimiter = "," #special characters may need an escape character of a back apostrophe (e.g. `t is a tab) ############ Variables You Change END ############ #Test if output path exists in next two lines and create if not. $TestPath = Test-Path $OutPath if ($TestPath -ne "True") {MD $OutPath} $ServerIP = [System.Net.Dns]::GetHostAddresses("$Servername") #This looks up IP in DNS and is more resilient than using a static IP provided the response is IPv4 #$ServerIP = "127.0.0.1" #unremark this line and remark out the line above if you wish to pull from localhost loopback IP $response = Read-host "What's your SQL User Name?" -AsSecureString $user = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($response)) #$user = "YourUserName" #remark out the prior two lines to avoid prompting for user and use this instead - ONLY ON SECURE COMPUTER!! $response = Read-host "What's your password?" -AsSecureString $pass = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($response)) #$Pass = "YourPassword" #remark out the prior two lines to avoid prompting for user and use this instead - ONLY ON SECURE COMPUTER!! ##### Begin Extract of 1 File. Invoke-Sqlcmd -serverinstance tcp:$ServerIP -QueryTimeout 600 -database $DB -username "$User" -password "$Pass" -query " --Your SQL Query goes here select * from stu where del = 0 and sc = 994 " | ConvertTo-Csv -NoTypeInformation -delimiter "$Delimiter" | #select -Skip 1 | #This line removes the header % {$_.Replace('"','')} | #Remove Double Quote Qualifiers out-file "$OutPath\StudentDataExport.txt" -Encoding UTF8 ##### End Extract of 1 File. ##### Copy the first extract and change the SQL query and output file as needed for more files.
Export a SQL query to a CSV file. Print
Modified on: Mon, Aug 27, 2018 at 11:15 AM
Previous Article: PowerShell Picture Export Script
Next Article: Attendance Percentage and Period Attendance Percentage with custom date ranges
Did you find it helpful? Yes No
Send feedbackSorry we couldn't be helpful. Help us improve this article with your feedback.