And now using the data to generate sql INSERT-statements

Before SQL-Serve 2008 sql bulks of SQL Insert-Statements are so redundant, that they are not readable.
Of corse, if you are too clever you ommit the column list after INSERT INTO.
Try it and learn it the hard way.

One of my common tasks is generating Insert Statements. Perhaps this way:

$list = (             
    ( 1, 'PS',      'PowerShell is the most inovative scripting language'),            
    ( 2, 'Python',  'has the most concise formating'),            
    ( 3, 'Algol60', 'the mother of quoted identifiers'),            
    ( 4, 'Algol68', 'the root of array slicing'),            
    ( 5, 'c#',      'if only you were caseinsentitiv casesensitivity is ORACLE''s greates fault'),            
    ( 6, 'SQL',     'has an Insert syntax which is worse than Street Basic''s Data-Statement 
                        (where we put the assembler code you remember)')             
)            
            
foreach ($item in $list)            
{            
    $id, $code, $note = $item            
    $note = $note -replace "'", "''"  # you have to double embedded apostrophes            
    "insert into Computer_Languages ( Id, Code, Note) values ( $id, '$code', '$note')"            
}

Background to problem with nested data structures

Thanks Jeff for the quick response. For some reasons I memorized the following pattern:

$list = @(            
    (1,2)            
    (3,4)            
)            
$list.count            
             

which flattens the structure and yields 4 instaed of the correct syntax

$list = (            
(1,2),            
(3,4)            
)            
$list.count            
            

which yields 2

Once using the wrong pattern there is no escape without help.

In Python it would be basic. in PowerShell ???

I hope there is a better solution in Powershell to build $list

$list = @()            
            
function add-Tupple            
{            
    $index = $list.count            
    $global:list += 1            
    $global:list[$index] = $args            
}            
            
            
add-Tupple 'Ps'     'has object pipelines'             
add-Tupple 'Python' 'has natural nested datastructures'                
            
$list.count            
            
foreach ($item in $list)            
{            
    $code, $name = $item            
    "Code: $code"            
    "Name: $name"            
}            
            
# in python (('Ps', 'has object pipelines'), ('Python', 'has natural nested datastructures'))               
            

Manage Modules The ISE Way

Hello again

Last month we got a lot of modules to try. Example given PowerShellPack or BSonPosh Powershell Module.

Of corse you do not want to add all the modules you find to your profile.
To be honest isepack is the only module I load in my profile.
I just added a smal script to my personal toolbox of ISE Add-On Extensions.

It adds and removes modules, list the currently loaded and shows the files of a module.

Pleae be warned, not all modules are writen to be removed, e.g. ispack does not remove its menus, when removed. But you have a starting point to play with.

And thanks again to James Brundage, whoes Copy-ColoredHTML (it's in the ISEpack module of the PowerShellPack) makes posting PowerShell code so easy.

function Update-ModuleMenu()            
{            
    $all_modules    = get-module -list            
    $loaded_modules = get-module             
            
    $remove_items =  @{}            
    $loaded_modules | % {            
        $name = $_.name            
        $remove_items[$_.name] = "Remove-module $name; Update-ModuleMenu"            
    }            
            
    $add_items = @{}            
    $all_modules | ? {! ($loaded_modules -contains $_ )} |            
    % {            
        $name = $_.name            
        $add_items[$_.name] = "Import-module $name; Update-ModuleMenu"            
            
    $list_items = @{}            
    $all_modules | % {            
        $list_items[$_.name] = "gci (split-path (get-module $_ -list).Path) | select Name"            
    }            
            
    $items = @{             
                "  loaded" = {Get-Module      | Sort-object | select name, path | fl}            
                " add"    = $add_items            
                " remove" = $remove_items             
                "files" = $list_items            
                }            
    }            
            
    #$items            
            
    Add-IseMenu -name 'Module' $items            
}            
            
            
Update-ModuleMenu            
             

Reload-ISE - Part III

I added some code to save using different encodings and to show the encoding of the current file.

I thought default for new files was utf8. Surprize it is BigEndianUnicode. But as long as it uses a BOM-Mark i will not complain. (Otherwise read my comments. ;-) )

Play and learn.

            
[cmdletbinding()]            
param(            
    # use this switch, when you have ISEpack installed and want a default menu added            
    # import-module isepack            
    # .  -ISEpackCX            
    [switch]$ISEpackCX            
    )            
            
            
function Reload-ISE ()             
{            
   <#
    .Synopsis
        Reload file asuming given encoding
    .Description
        Reload the file in the current editor asuming given encoding, 
        the file is not saved automatically. You can try other encondings on
        the unmodified file.
    .Example
        Reload-ISE utf8
        Reloads the file asuming utf8 encoding. This is useful when the file
        has no BOM
    .Example
        Reload-ISE
        Prompts for a codepage number and reloads the file using it
    .LINK
        Script posted to: 
        http://pauerschell.blogspot.com
        by Bernd Kriszio (twitter: bernd_k)
    .Parameter encoding
        encodings known to  Get-Content:
        Unknown, String, Unicode, Byte, BigEndianUnicode, UTF8, UTF7, Ascii
        for a list of valid codepages see
        http://msdn.microsoft.com/en-us/library/system.text.encodinginfo.aspx
     .NOTES
        use -verbose to show the current encoding of the reloaded file
        and RMFC  read my f... comments
    #>            
    [cmdletbinding()]            
    param(            
    # encoding to use for reloading            
    [Parameter(Position = 0, Mandatory=$True)]            
    $encoding            
    )            
    $path = $psise.CurrentFile            
    $fullpath = $path.FullPath            
            
    if ( ("String", "UTF8", "UTF7", "Ascii", "Unicode", "BigEndianUnicode") -contains $encoding)            
    {            
        $text = Get-Content -Encoding $encoding $fullpath -ReadCount 0  -totalcount -1            
        $text = $text -join   "`r`n"            
    }            
    else            
    {            
        $encoding = [int]$encoding            
        $bytes = get-content $fullpath -encoding byte            
        $encoding_obj = [System.Text.Encoding]::GetEncoding( $encoding  )            
        $text = $encoding_obj.GetString($bytes)            
    }            
            
    $loadToNewEditor = $False            
    if ($loadToNewEditor )            
    {            
        $count = $psise.CurrentPowerShellTab.Files.count            
        $psIse.CurrentPowerShellTab.Files.Add()            
        $Newfile = $psIse.CurrentPowerShellTab.Files[$count]            
        $Newfile.Editor.Text = $text            
    }            
    else            
    {            
        $psise.CurrentFile.Editor.Text = $text            
    }            
    $psISE.CurrentFile.Encoding | Write-Verbose            
}            
            
            
if ($ISEpackCX)            
{            
# I'm beginning to dislike Add-IseMenu because I can't control the order of the items, I'm using blanks as workaround. Dirty old...            
Add-IseMenu -name 'Reload/Save'  @{            
        " Show encoding"              = {$psISE.CurrentFile.Encoding| Select BodyName, IsSingleByte, EncodingName, CodePage| fl}            
        "Reload  Codepage..."         = { Reload-ISE }                         # in most cases you can't save back in this encoding, only export             
        "Reload Ascii"                = { Reload-ISE Ascii }            
        "Reload BigEndianUnicode"     = { Reload-ISE BigEndianUnicode }        # this seems to be the default for new files in ISE            
        "Reload OEM850"               = { Reload-ISE 850 }                     # you can't save back in this encoding, only export             
        "Reload String"               = { Reload-ISE String }                  # seems to be the same as unicode            
        "Reload Unicode"              = { Reload-ISE Unicode }                   
        "Reload utf7"                 = { Reload-ISE UTF7 }            
        "Reload utf8"                 = { Reload-ISE UTF8 }            
        # To save means use an encoding, which ISE can read back again            
        "Save File ASCII"             = {$psISE.CurrentFile.Save([Text.Encoding]::ascii)}   # if you use this, you are insulting most Europeans            
        "Save File ANSI"              = {$psISE.CurrentFile.Save([Text.Encoding]::default)} # my personal favorite ;-)            
        # I hope the following write BOM's. Unicode without BOM is more evil than OEM437 or OEM850            
        # I sentence everyone who uses unicode without BOM to 10 years VB3 coding             
        # (without PowerShell and on Vista to make it a real punishment  ;-)  )            
        "Save File utf7"              = {$psISE.CurrentFile.Save([Text.Encoding]::utf7)}            
        "Save File utf8"              = {$psISE.CurrentFile.Save([Text.Encoding]::utf8)}            
        "Save File unicode"           = {$psISE.CurrentFile.Save([Text.Encoding]::unicode)}            
        "Save File BigEndianUnicode"  = {$psISE.CurrentFile.Save([Text.Encoding]::BigEndianUnicode)} # as far as I know, that's ISE's default for new files            
        # Todo: Export in arbritary encodings into another file            
    }            
}

Reload-ISE - part II

I have extended my function, so that it now handles all Code Page numbers, like OEM 850, OEM 437 or whatever is listed in http://msdn.microsoft.com/en-us/library/system.text.encodinginfo.aspx. I even tried to make the header more standard.

This file is written in a special way, which I call ISEpackCX.
It can use ISEpack's Add-IseMenu to extend the Add-ons menu. If you wish, you can do without this and take care of the menu by your own.

Add the following lines to your profile

import-module isepack            
#. <your path>\Reload-ISE.ps1 -ISEpackCX            

and save the following as Reload-ISE.ps1.

Happy ISE-extending

Bernd

          
[cmdletbinding()]          
param(          
  # use this switch, when you have ISEpack installed and want a default menu added          
  # import-module isepack          
  # .  -ISEpackCX          
  [switch]$ISEpackCX          
  )          
        
        
function Reload-ISE ()           
{          
 <#     .Synopsis         Reload file asuming given encoding     .Description         Reload the file in the current editor asuming given encoding,          the file is not saved automatically. You can try other encondings on         the unmodified file.     .Example         Reload-ISE utf8         Reloads the file asuming utf8 encoding. This is useful when the file         has no BOM     .Example         Reload-ISE         Prompts for a codepage number and reloads the file using it     .LINK         Script posted to:          http://pauerschell.blogspot.com         by Bernd Kriszio (twitter: bernd_k)     .Parameter encoding         encodings known to  Get-Content:         Unknown, String, Unicode, Byte, BigEndianUnicode, UTF8, UTF7, Ascii         for a list of valid codepages see         http://msdn.microsoft.com/en-us/library/system.text.encodinginfo.aspx      .NOTES         use -verbose to show the current encoding of the reloaded file     #>          
  [cmdletbinding()]          
  param(          
  # encoding to use for reloading          
  [Parameter(Position = 0, Mandatory=$True)]          
  $encoding          
  )          
  $path = $psise.CurrentFile          
  $fullpath = $path.FullPath          
        
  if ( ("String", "UTF8", "UTF7", "Ascii", "Unicode", "BigEndianUnicode") -contains $encoding)          
  {          
      $text = Get-Content -Encoding $encoding $fullpath -ReadCount 0  -totalcount -1          
      $text = $text -join   "`r`n"          
  }          
  else          
  {          
      $encoding = [int]$encoding          
      $bytes = get-content $fullpath -encoding byte          
      $encoding_obj = [System.Text.Encoding]::GetEncoding( $encoding  )          
      $text = $encoding_obj.GetString($bytes)          
  }          
        
  $loadToNewEditor = $False          
  if ($loadToNewEditor )          
  {          
      $count = $psise.CurrentPowerShellTab.Files.count          
      $psIse.CurrentPowerShellTab.Files.Add()          
      $Newfile = $psIse.CurrentPowerShellTab.Files[$count]          
      $Newfile.Editor.Text = $text          
  }          
  else          
  {          
      $psise.CurrentFile.Editor.Text = $text          
  }          
  $psISE.CurrentFile.Encoding | Write-Verbose          
}          
        
        
if ($ISEpackCX)          
{          
# activate only those you need          
# this is not stupid SQL-Management-Studio with no selection or select from all posible encodings          
Add-IseMenu -name 'Reload'  @{          
      " Codepage..."     = { Reload-ISE }           
      "Ascii"            = { Reload-ISE Ascii }          
      "BigEndianUnicode" = { Reload-ISE BigEndianUnicode }             
      "String"           = { Reload-ISE String }          
      "Unicode"          = { Reload-ISE Unicode }                 
      "UTF7"             = { Reload-ISE UTF7 }          
      "UTF8"             = { Reload-ISE UTF8 }          
  }          
}

Reload-ISE

Just had to work with some data in utf-8 without BOM. I want to extend this to OEM-850 later, but here the first draft.

And if you are really using ISE for productive things please send me (bernd_k) a note at twitter.

Sometimes I believe I'm the only one. At least in https://twitter.com/bernd_k/ise-users. I want to add you.

Feedback will improve my presentation. Otherwise you get it quick and dirty.

The function Add-IseMenu is in ISEpack http://code.msdn.microsoft.com/PowerShellPack.

Have fun with ISE

Bernd

           
function Reload-ISE ($encoding = 'UTF8')            
{           
          
   # ToDo OEM 850 requires .NET           
   # Get-Content Encodings: Unknown, String, Unicode, Byte, BigEndianUnicode, UTF8, UTF7, Ascii           
          
   $path = $psise.CurrentFile           
#   $path           
   $fullpath = $path.FullPath           
#   $fullpath           
    $text = Get-Content -Encoding $encoding $fullpath -ReadCount 0  -totalcount -1           
          
     $text = $text -join   "`r`n"           
          
#     $count = $psise.CurrentPowerShellTab.Files.count           
#     $psIse.CurrentPowerShellTab.Files.Add()           
#     $Newfile = $psIse.CurrentPowerShellTab.Files[$count]           
#     $Newfile.Editor.Text = $text           
          
   $psise.CurrentFile.Editor.Text = $text           
}           
          
          
Add-IseMenu -name 'Reload'  @{           
       "String"           = {Reload-ISE "String"}           
       "UTF8"             = {Reload-ISE "UTF8"}           
       "UTF7"             = {Reload-ISE "UTF7"}           
       "Ascii"            = {Reload-ISE "Ascii"}           
       "Unicode"          = {Reload-ISE "Unicode"}                  
       "BigEndianUnicode" = {Reload-ISE "BigEndianUnicode"}              
       #"OEM850"           = {Reload-ISE "OEM850"}            
   }