Archive for category Powershell

Powershell to write bunch of data from sql to the file system

I recently had need to write a bunch of xml we had stored in sql out to a bunch of files. So we have one xml file per row, and I wanted to out put these into a folder, each one in it’s own file. After some Googling I got this working;

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Data Source=localhost;Initial Catalog=testDB;User Id=sa;Password=thepassword;Persist Security Info=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = “select top 500 id, [xml] from xmlLog order by id desc”
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
for ($i=0;$i -le $DataSet.Tables[0].Rows.Count – 1;$i++) { $DataSet.Tables[0].Rows[$i][1] >> (“c:\xml\file” + $i + “.xml”) }

This gave me a folder called xml in my c:\ containing 500 xml files!

No Comments

Powershell folder/ sub folder renaming

I had my first go at powershell today to rename a bunch of folders in a way which I figured would be too fiddly at the standard command line.  I had a bunch of log files in a structure like this;

/RootFolder
  /foldera
    /logs
      /a-random-log.txt
      /some-log-file.txt
  /folderb
    /logs
      /a-file.txt
      /b-file.txt

I basically wanted to recurse each folder and give the files a sensible name, with a view to eventually moving them all into one folder- so I wanted this;

/RootFolder
  /foldera
    /logs
      /foldera-1.txt
      /foldera-2.txt
  /folderb
    /logs
      /folderb-1.txt
      /folderb-2.txt

After some fiddling I came up with this, which I ran straight from the power shell prompt, in the root folder location;

Get-ChildItem | foreach { $id = 1; Get-ChildItem (($_.fullname) + “\logs\”)| foreach { Rename-Item -Path $_.fullname -NewName ($_.Directory.Parent.name + “-” + ($id++).toString() +”.txt”) } }

  1. Get-ChildItem grabs a directory listing of the root folder- the resulting array I then pipe into a foreach loop.
  2. Each iteration of this first loop I reset the counter variable I will use for naming the files.  Then I grab an array of the files inside each of the log folders within the folders I just listed (!) — so this should be a list of the files in foldera/log/ and folderb/log
  3. Finally I perform the rename – I build up the new file name based on this file (the $_ represents the current object in this iteration of a foreach in powershell) directory property (which will = logs) then I do .parent (to grab the parent dir of logs) which gets us to the directory name we actually want.  I string this together with the index number and hard code an extension, as I know all my files are .txt files.

It took a little fiddling, but it was nice to finally use powershell, as there are many people bigging it up on various tech blogs and twitters!

Once I’m this far, the move is just a matter of tweaking what I already have a little..

Get-ChildItem | foreach { Get-ChildItem (($_.fullname) + “\logs\”)| foreach { Move-Item $_.fullname -Destination ($_.Directory.Parent.Parent.fullname) } }

 

Useful; To find the properties available to you, pipe your collection to Select-Object *, which will iterate through each element, giving you a full print out of all of it’s available properties, and their current values.

No Comments