Renaming MP3 Files with Powershell


I recently bought ZZ Tops album “Rancho Texicano, The Very Best of ZZ Top” from Amazon. When I downloaded the album it had the Track number preceding the song name. Like this:
20 – I’m Bad, I’m Nationwide (Remastered).mp3
I prefer that the track numbers not be part of the file name, so I wrote this little script to strip the track number from the file name.

$dir = Get-ChildItem "C:\MUSIC\Rancho Texicano The Very Best of ZZ Top" -include *.mp3 -recurse
 foreach($x in $dir)
 { $FileName = $x.name
 $N = $FileName.tostring()
 $E = $N.split("-")
 $F = $E[1]
 $newname = $F.Trim()
 Rename-Item $x $newname
 }

It worked so fast I thought I had made an error, and now my ZZ Top songs appear in alphabetical order.
Two problems with this:

1. The song Bar-B-Que.mp3 came out Bar with no extension. So be careful.

2. My original script left a leading space in the name of each song.  I fixed that by adding the trim function: $newname = $F.trim()

Advertisements

More Fun with Dates (and Times)

# COMMENT: More Fun with Dates (and Times)
#  I got this from the Microsoft TechNet Site, but I thought it was too good not to share.

# ===================================
# $a = Get-Date# Accessing Other Date Properties Using Get-Date
$a = Get-Date
"Day: " + $a.Day
"Month: " + $a.Month
"Year: " + $a.Year
"Hour: " + $a.Hour
"Minute: " + $a.Minute
"Second: " + $a.Second$a = (Get-Date).DayFunction ShortDate {
$a = Get-Date
"Date: " + $a.ToShortDateString()
"Time: " + $a.ToShortTimeString()

}

Function DST {
$a = Get-Date
"Daylight Saving Time: " + $a.IsDaylightSavingTime()
"Day of Week: " + $a.DayOfWeek
"Day of Year: " + $a.DayOfYear
}

Function Zulu {
$a = Get-Date
"Universal Time: " + $a.ToUniversalTime()
}
# Simple Date Arithmetic
Function AddDays {
$a = Get-Date
$a.AddDays(90)
}
Function SubDays {
$a = Get-Date
$a.AddDays(-90)
}
# PowerShell Bonus: Getting an Array of Day/Month Names
Function DayNames {
$a = New-Object system.globalization.datetimeformatinfo
$b = $a.DayNames
$b
}

Function MonthNames {
$a = New-Object system.globalization.datetimeformatinfo
$b = $a.MonthNames
$b
}

$Date = (Get-Date -format "MM-dd-yyyy") #<-- Date looks like 03-09-2011. Gr8 for file names

How do I Import a Text file to Excel via Powershell

You can try this:

$XL.workbooks.Open($file)

But that does not always work, Especially if you have some delimiter that you want to use.
I opened up Excel and recorded a macro to see how Excel does it.
Here is the Macro it recorded with some editing:
 

# Sub Macro2()
# ‘
# ‘ Macro2 Macro
# ‘
#
# ‘
#     Workbooks.OpenText Filename:=”Z:\MBSA_ReportExcluded.txt”,
#     Origin:=437, _
#     StartRow:=1,
#     DataType:=xlDelimited,
#     TextQualifier:=xlDoubleQuote, _
#     ConsecutiveDelimiter:=[True,False],
#     Tab:=True,
#     Semicolon:=False,
#     Comma:=False,
#     Space:=False,
#     Other:=False,
#     FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
#     TrailingMinusNumbers:=True
# End Sub

I looked up the Excel constants (xlDelimited, xlDoubleQuote) and they are both equal to 1.
So I tried several different ways in Powershell and this one works for a Tab delimited file.
 

$oXL = New-Object -comobject Excel.Application
$oXL.Visible = $true
$oXL.DisplayAlerts = $False
$file = “Z:\MBSA_ReportExcluded.txt”
$oXL.workbooks.OpenText($file,437,1,1,1,$True,$True,$False,$False,$False,$False)

The first $True sets whether you want consecutive delimiters to be treated as one.

(This is useful mostly with space delimited files.)

The last five $True/$False are the ones that select the delimiter.

#  1   Tab:=True,
#  2   Semicolon:=False,
#  3   Comma:=False,
#  4   Space:=False,
#  5   Other:=False,

How do I change the size or position of my chart with Powershell

 
# <---- Start Code ---------------------------------------> 
$xl = New-Object -comobject Excel.Application
# Show Excel
$xl.visible = $true
$xl.DisplayAlerts = $False
# Open a workbook
$wb = $xl.workbooks.add() 
#Create Worksheets
$ws = $wb.Worksheets.Item(1) # Opens Excel and 3 empty Worksheets
1..8 | % { $ws.Cells.Item(1,$_) = $_ } # adds some data
1..8 | % { $ws.Cells.Item(2,$_) = 9-$_ } # adds some data
$range = $ws.range("a${xrow}:h$yrow") # sets the Data range we want to chart
# create and assign the chart to a variable
#$ch = $xl.charts.add() # This will open a new sheet
$ch = $ws.shapes.addChart().chart # This will put the Chart in the selected WorkSheet
$ch.chartType = 58
$ch.setSourceData($range)
$RngToCover = $ws.Range("D5:J19") # This is where we want the chart
$ChtOb = $ch.Parent # This selects the current Chart
$ChtOb.Top = $RngToCover.Top # This moves it up to row 5
$ChtOb.Left = $RngToCover.Left # and to column D 
$ChtOb.Height = $RngToCover.Height # resize This sets the height of your chart to Rows 5 - 19
$ChtOb.Width = $RngToCover.Width # resize This sets the width to Columns D - J
<------------- End Code --------------------------------------> 

Thanks to Marco Shaw and to this article he pointed me to:
Read more: Resizing and Moving Excel Charts with VBA http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html#ixzz0seQImNEL