Extracting data with headers using PowerShell

  • 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]

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

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

  • 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]

  • 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]

  • 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

    )

  • 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)
    Intro to Tally Tables and Functions

  • @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)
    Intro to Tally Tables and Functions

  • 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).

  • 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)
    Intro to Tally Tables and Functions

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

  • 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

  • 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]

  • 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]

  • 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]

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

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