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

avatar image

chillw1nston
138 10 11 15

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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

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

more ▼

answered Apr 28, 2011 at 04:56 AM

avatar image

WilliamD
26.2k 18 34 48

(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

avatar 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:

x51

asked: Apr 28, 2011 at 03:21 AM

Seen: 9349 times

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

Copyright 2016 Redgate Software. Privacy Policy