question

chillw1nston avatar image
chillw1nston asked

Powershell export multiple tables to csv

Good day all SQL professionals, I have a business need to export all data from tables that have a certain prefix. Im fairly new to powershell and am struggling to get this to work. I think the variable passed to the sql query is actually System.data.datarow instead of what that row contains. Plz help: $Tables = invoke-sqlcmd -query "SELECT name FROM sys.tables WHERE name LIKE 'ENUM_%'" -database ****** -serverinstance localhost foreach ($Table in $Tables) { write-host -ForegroundColor Green "Creating File $Table.csv" invoke-sqlcmd -query "SELECT * FROM $Table" -database ****** -serverinstance localhost |export-csv -path F:\ENUM_Tables\$Table.csv }
powershell
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

should probably have mentioned that I have been running this as a .ps1 script file from sqlps
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
How about a little adjustment like this: $Tables = invoke-sqlcmd -query "SELECT name FROM sys.tables WHERE name LIKE 'Movie%'" -database "MovieManagement" -serverinstance "MyServer" foreach ($Table in $Tables) { $TableName = $Table["name"] write-host -ForegroundColor Green "Creating File $TableName.csv" invoke-sqlcmd -query "SELECT * FROM $TableName" -database "MovieManagement" -serverinstance "MyServer" |export-csv -path c:\scripts\$TableName.csv }
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Awesome stuff grant, slight amendment though otherwise the script writes over the same file repeatedly; `|export-csv -path c:\scripts\$Table.csv` should be `|export-csv -path F:\ENUM_Tables\$TableName.csv` but im sure thats what you meant.
1 Like 1 ·
Yeah, just noticed that myself. I'll edit so that it's clear.
1 Like 1 ·
@chillw1nston Backslash is used as an escape character, so if you want the post to have your backslashes preserved then escape one backslash with another, much like you would have to do in C# or Java. Input: **c:\\\\temp** will then produce **c:\\**
1 Like 1 ·
post seems to have removed the backslashes but you get the idea!
0 Likes 0 ·
Alternatively, wrap text that you want to identify as "code" in \` characters
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
Although Grant's answer does the job (+1), there is more than one way to skin a cat: Take a look at [Getting Data out of SQL Server from Powershell][1] to see a few examples. [1]: http://www.sqlservercentral.com/articles/powershell/65324/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shaiss avatar image
shaiss answered
worked for me, I just had to add the brackets on line 6 [$TableName]
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.