x

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
}
more ▼

asked Apr 28, 2011 at 03:21 AM in Default

chillw1nston gravatar image

chillw1nston
138 10 10 12

should probably have mentioned that I have been running this as a .ps1 script file from sqlps
Apr 28, 2011 at 03:21 AM chillw1nston
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
}
more ▼

answered Apr 28, 2011 at 04:33 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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, 2011 at 05:09 AM chillw1nston
post seems to have removed the backslashes but you get the idea!
Apr 28, 2011 at 05:10 AM chillw1nston
Yeah, just noticed that myself. I'll edit so that it's clear.
Apr 28, 2011 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, 2011 at 06:08 AM Oleg
Alternatively, wrap text that you want to identify as "code" in ` characters
Feb 05, 2013 at 05:26 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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/
more ▼

answered Apr 28, 2011 at 04:56 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
worked for me, I just had to add the brackets on line 6 [$TableName]
more ▼

answered Feb 05, 2013 at 04:22 PM

shaiss gravatar image

shaiss
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x29

asked: Apr 28, 2011 at 03:21 AM

Seen: 7103 times

Last Updated: Feb 05, 2013 at 05:26 PM