Xenophane's Blog

At work we have a spreadsheet, where we keep track of our mobile phones, and 3G dongles, in it we keep info about the IMEI code, PUK/PIN codes etc.

Today we found a few SIM cards lying around, the only thing on them was the SIM card number (19 digits). When I went to compare that to the info in the file, I realised that that was not noted in the file, so I had no way to match the SIM with a Phone number, PIN/PUK code.

So I called the telco, they were willing/able to supply me with an csv file containing the phone number, SIM card number, and PUK code.

So I took on the task to update our document, and check that the info in our original document was correct, but doing this for 45+ subscriptions, I thought I would write a script, and what better language than PowerShell.

Here is what I ended up with.

The CSV file contains 3 headers
MSISDN1,UICCID,PUK1

#Location of the Excel file I want to edit
$FileLoc = "C:\temp\test.xls"
#Create Excel Com Object, and display it
$excel = new-object -com Excel.Application
$excel.visible = $true

#Open Workbook,
$workbooks = $excel.workbooks.Open($FileLoc)
$worksheets = $workbooks.Worksheets
$worksheet = $worksheets.Item(1)
#Select the range we want to look at
#In this example, I am only checking within one Column
$range = $worksheet.Range("M3", "M150")
#Load CSV file
$PhoneInfo = Import-Csv c:\Temp\TlfPUK.csv
#Run through the colums
foreach($col in $range.Columns){
        foreach($row in $col.Rows){
                Foreach ($Phone in $PhoneInfo){
				#In the CSV file,  MSISDN1 represents the phone number, but with the international dial code in front
				#that is why I use substring to get the last 8 chars and compare it the value of the current cell.
				#If the phone numbers match, do something
				If ( $Phone.MSISDN1.substring($Phone.MSISDN1.length -8,8) -eq $row.Text) {
				#The two colums what will contain the data is set, this is just 4 & 5 columns to the right of the column that contains the phone number
				$ColIMEI = $row.Column + 5
				$ColPUK = $row.Column + 4
				#Here I fill in the SIMCard number (UICCID) and PUK code
        $worksheet.Cells.Item($row.Row, $ColIMEI) = $Phone.UICCID
		$worksheet.Cells.Item($row.Row, $ColPUK) = $Phone.PUK1
				#$Phone.UICCID
				}
 				}

		}

} 

The other day I was tasked with creating a spreadsheet containing a list of all servers in our network, manager wanted a “sheet” for each computer, and an “index” sheet with links to all the other sheets.

Since we have more than 150 servers, there was no way I was going to create this list by hand… So I wrote a quick and dirty little Powershell function.

Function Fill-Excel {
BEGIN {
   $xl = new-object -comobject excel.application
   $xl.Visible = $true
   #I specify an existing filename, since creating the link in Excel requires a filename
   $wb = $xl.Workbooks.Open("C:\Temp\test.xlsx")
   $ws = $wb.Worksheets.Item(1)
   $xl.ActiveSheet.Name = "List"
   $row = 1
  }

Process {
$_
$q = $_
$z = $q.ToUpper()
$xl.Sheets.Add()
$xl.ActiveSheet.Name = $z
$wh = $xl.ActiveSheet
$wh.Cells.Item(1,1) = "=HYPERLINK(`"[test.xlsx]List!A1`";`"Back To List`")"
$ws.Cells.Item($row,1) = "=HYPERLINK(`"[test.xlsx]$z!A1`";`"$z`")"
$row++
}
}

$s= "Computer1","CompPUTER2","comPUTer3"
$s | Fill-Excel

This will open the xslx filed called test.xlsx under c:\temp and create an index sheet called “list”, and a sheet for each object that is passed to the function, it also creates a Link in A1 that refers back to the index sheet. On the Index sheet a link to each new sheet is created.

In the above example I “manually” pipe in some text string, but it could also be something like this:

#
#
$s = Get-QADComputer | Where {$_.OSname -match "Server"} | select name

So since it is a function, you can pass virtually any string to it, and it will populate the excel sheet for you.