#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.
Modified on: Mon, Aug 27, 2018 at 11:15 AM
Did you find it helpful? Yes No
Send feedbackSorry we couldn't be helpful. Help us improve this article with your feedback.