Powershell export multiple tables to csv

Question

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
   }

Answer 1

0

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
}

answered Apr 28 '11 at 04:33 AM

Grant Fritchey ♦♦
62.6k ● 12 ● 20 ● 66

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.

Apr 28 '11 at 05:09 AM chillw1nston

post seems to have removed the backslashes but you get the idea!

Apr 28 '11 at 05:10 AM chillw1nston

Yeah, just noticed that myself. I'll edit so that it's clear.

Apr 28 '11 at 05:15 AM Grant Fritchey ♦♦

@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:\

Apr 28 '11 at 06:08 AM Oleg

Alternatively, wrap text that you want to identify as "code" in ` characters

Feb 05 at 05:26 PM ThomasRushton ♦

(comments are locked)

Answer 2

0

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 to see a few examples.

answered Apr 28 '11 at 04:56 AM

WilliamD
25.3k ● 16 ● 18 ● 41

(comments are locked)

Answer 3

0

worked for me, I just had to add the brackets on line 6 [$TableName]

answered Feb 05 at 04:22 PM

shaiss
0

(comments are locked)

Powershell export multiple tables to csv

Follow this question

Related Questions