Feeds:
Posts
Comments

Archive for the ‘Uncategorized’ Category

I had a request to take data from a CSV file that was being automatically downloaded and copy a section of the data into an existing spreadsheet. The spreadsheet had 3 tabs and the copied data had to go to the tab labeled “Raw Data”. Then one of the other tabs would perform calculations and analysis on that raw data. I know I could manually copy the data but this process is already partially automated and the number of rows and columns involved were huge. There are at least 60 columns and over 10,000 rows of data that needed to be copied. So, to Powershell I go!!

I created a Powershell Script that would open both files and then select the data from the csv file and copy it.  Then the script would go to the Excel file and to the required tab before pasting the info.

Here is the final script.

$pathxls = “\\fileserver\data\myshare\sheets\workbook.xls”
$pathcsv = “\\fileserver\data\myshare\sheets\Overview.csv”
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $excel.Workbooks.open($pathcsv)
$Workbook2 = $excel.Workbooks.open($pathxls)
$Worksheet = $Workbook.WorkSheets.item(“Overview”)
$Worksheet.activate()
$range = $WorkSheet.Range(“A2:BC2”).CurrentRegion
$range.Copy() | out-null
$Worksheet2 = $Workbook2.Worksheets.item(“RAW DATA”)
$worksheet2.activate()
$range2 = $Worksheet2.Range(“A3:A3”)
$Worksheet2.Paste($range2)
$workbook2.Save()
$workbook.close($false)
$Excel.Quit()
[gc]::collect()
[gc]::WaitForPendingFinalizers()

Now let’s dissect it a bit.

First we declare the variables for the two files $pathxls and $pathcsv and assign the path and file to each variable.

Then we declare a new com object which is an excel application.

We tell it that we don’t want Excel to be visible during the run of the script.

And we then set two workbook variables for the two files.

In the first file (the CSV) we tell it to go to the worksheet labled “Overview” and then make it the activate sheet.  Once there we select a range and copy it.

Then we jump over to the second worksheet and select the tab called “Raw Data” and make it the active sheet.

Once that is done we can paste in the data we’ve copied and then we can save the Excel workbook.  We close the csv file and tell it NOT to save since we don’t need to and then we can quit Excel.

Rather simple and to the point.  It gets the job done.  Now I can either run this manually or set it as a scheduled task.

 

 

Read Full Post »

I recently got involved with a new website project and was trying to determine the best set up for our situation. I needed a Content Management System (CMS) so the end user could make the changes to the site without my intervention. So, I started looking into different solutions. My first attempt was DotNetNuke community edition. Since I am familiar with both MS SQL and ASP.NET I thought this would be the most logical choice. So, I set up a virtual machine running Windows Server 2008 R2 and set to work configuring DotNetNuke.

After a bit of wrangling I was able to get the site up and running with the DNN sample site running. It was pretty slick but there were a lot of confusing configuration settings. I was able to get a basic sample site running and let the end user mock up a few pages. It worked but it wasn’t great.

So, I remembered that I also have a BlogEngine.net site running on MSSQL and ASP.NET so I looked into that as well. Again not bad, but nothing I would say I absolutely loved.

Now, I am setting up another virtual machine and going through the steps of setting up a Windows installation of WordPress.

I found a great step by step tutorial from someone else that has helped me get up and running. http://www.vsysad.com/2012/04/create-a-wordpress-blog-on-windows-server-2008-r2-iis-7-5-and-mysql/

Now that the site is up and running I can choose a template and begin to customize the site.

So far, I have to say I am more comfortable with WordPress as a CMS and it seems easier to work with than the other systems I have tried. Of course the next big trick would be to create my own custom theme.

Read Full Post »

Merry Christmas

Merry Christmas to all! Hope it was a special day for everyone.

Read Full Post »