Extracting data with headers using PowerShell

  • Louis Li

    Right there with Babe

    Points: 798

    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]

  • it.ma

    SSC Enthusiast

    Points: 113

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

  • sql_er

    SSCarpal Tunnel

    Points: 4135

    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

    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

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

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

    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)
    Forum FAQ

  • Jeff Moden

    SSC Guru

    Points: 996475

    @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)
    Forum FAQ

  • andycadley

    SSCertifiable

    Points: 5291

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

    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)
    Forum FAQ

  • Dhruva_51

    SSChampion

    Points: 10510

    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

    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

    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

    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

    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