Extracting data with headers using PowerShell

Question

Post reply

Extracting data with headers using PowerShell

Louis Li

Right there with Babe

Points: 798
More actions
June 3, 2014 at 12:01 am

#307788

Comments posted to this topic are about the item Extracting data with headers using PowerShell
----------------------------------------------------------------------------------------------
Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

it.ma SSC Enthusiast Points: 113 More actions · Answer 1

where do we need to keep this master.sql script file? same directory where the PowerShell script resides?

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 2

Will it properly extract .csv file and show in excel if some columns are nvarchar and contain data in Russian for example?

Louis Li Right there with Babe Points: 798 More actions · Answer 3

it.ma (6/3/2014)
where do we need to keep this master.sql script file? same directory where the PowerShell script resides?

Yes, same directory.

----------------------------------------------------------------------------------------------
Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

Louis Li Right there with Babe Points: 798 More actions · Answer 4

sql_er (6/3/2014)
Will it properly extract .csv file and show in excel if some columns are nvarchar and contain data in Russian for example?

One good way to find out is to try it.

----------------------------------------------------------------------------------------------
Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

andycadley SSCertifiable Points: 5296 More actions · Answer 5

Useful, but I'd avoid using the args[] variable and do it "the Powershell way" with a Param block at the start of the script, that way you get things like autocomplete for free and named arguments, as well as getting guarantees that mandatory parameters have to be supplied.

param(

[Parameter(Mandatory=$true, HelpMessage='Enter name of server')]

[String]$ServerName,

[Parameter(Mandatory=$true, HelpMessage='Enter the name of the database')]

[String]$Database,

[Parameter(Mandatory=$true, HelpMessage='Enter the path to the source file')]

[String]$Sourcefile

)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

andycadley (6/3/2014)
Useful, but I'd avoid using the args[] variable and do it "the Powershell way" with a Param block at the start of the script, that way you get things like autocomplete for free and named arguments, as well as getting guarantees that mandatory parameters have to be supplied.
param(
[Parameter(Mandatory=$true, HelpMessage='Enter name of server')]
[String]$ServerName,
[Parameter(Mandatory=$true, HelpMessage='Enter the name of the database')]
[String]$Database,
[Parameter(Mandatory=$true, HelpMessage='Enter the path to the source file')]
[String]$Sourcefile
)

I'm thinking that would make the process batch-job resistant.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

@louis Li,

I realize that $dt is a loop variable but what did you intend that "dt" stand for? I'd also like to know why the use of $1? Why not give it a more readable name or is that required to be numerically name due to position or???

Also, why any abbreviations? Why not make the code more self documenting with only slightly longer names?

How does this handle embedded delimiters?

Last but not least, I know this was meant to be a short "spackle" style article but a lot of people could end up using something like this. Do you have any performance figures for this method?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andycadley SSCertifiable Points: 5296 More actions · Answer 8

Jeff Moden (6/3/2014)

I'm thinking that would make the process batch-job resistant.

How so? You can still specify the parameters on the command line, you can even specify them positionally and it'll work exactly the same (though in that case I'd also add Position(x) to each of the Parameter attributes to making positional parameter order explicit).

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

andycadley (6/3/2014)

Jeff Moden (6/3/2014)
I'm thinking that would make the process batch-job resistant.
How so? You can still specify the parameters on the command line, you can even specify them positionally and it'll work exactly the same (though in that case I'd also add Position(x) to each of the Parameter attributes to making positional parameter order explicit).

Didn't know that. I thought it was going to popup questions that required answers that couldn't be answered by providing them in a batch file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dhruva_51 SSChampion Points: 10521 More actions · Answer 10

Can the script.sql file can have a select statement with joins? or this can be only used to export for just plain tables?

it.ma SSC Enthusiast Points: 113 More actions · Answer 11

tried the same. (with master.sql in the same directory as .ps1 script)

It says

"You cannot call a method on a null-valued expression.

Line:16 / Char:18

+$ds = $server.Databases[$database].ExecuteWithResults("$(Echo $1.OpenText().ReadToEnd())")

+

+ CategoryInfo : InvalidOperation: (:) [], ParentContainsErrorRecordException

+ FullyQualifiedErrorId : InvokeMethodOnNull

Louis Li Right there with Babe Points: 798 More actions · Answer 12

Jeff Moden (6/3/2014)
@Louis Li,
I realize that $dt is a loop variable but what did you intend that "dt" stand for? I'd also like to know why the use of $1? Why not give it a more readable name or is that required to be numerically name due to position or???
Also, why any abbreviations? Why not make the code more self documenting with only slightly longer names?

Thanks for pointing out the abbreviations. That's my bad personal habit, I will pay attention to them next time.

How does this handle embedded delimiters?

This script relies on PowerShell cmdlet export-csv to generate csv file, please refer to to MSDN for more details:

http://technet.microsoft.com/en-us/library/ee176825.aspx

Last but not least, I know this was meant to be a short "spackle" style article but a lot of people could end up using something like this. Do you have any performance figures for this method?

I haven't run performance tests on this script.

----------------------------------------------------------------------------------------------
Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

Louis Li Right there with Babe Points: 798 More actions · Answer 13

it.ma (6/3/2014)
tried the same. (with master.sql in the same directory as .ps1 script)
It says
"You cannot call a method on a null-valued expression.
Line:16 / Char:18
+$ds = $server.Databases[$database].ExecuteWithResults("$(Echo $1.OpenText().ReadToEnd())")
+
+ CategoryInfo : InvalidOperation: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Looks like either it couldn't find your server or your database. Double check your connection to the database.

----------------------------------------------------------------------------------------------
Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

Louis Li Right there with Babe Points: 798 More actions · Answer 14

muthyala_51 (6/3/2014)
Can the script.sql file can have a select statement with joins? or this can be only used to export for just plain tables?

There is no restriction on what SQL script you execute.

----------------------------------------------------------------------------------------------
Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]