0

I have the below piece of code that checks for Files to Tapes jobs for a database and gives the output in an excel sheet. 

$date = Get-Date
$day = $date.Day
$hour = $date.Hour 
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Excel.DisplayAlerts = $false
$Workbook = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
#Counter variable for rows and columns
$intRow = 1
$intCol = 1

$Sheet.Cells.Item($intRow,1) = "Tasks/Servers"     
$Sheet.Cells.Item($intRow,2) = "DateLastRun"
$Sheet.Cells.Item($intRow,3) = "PRX1CSDB01"
$Sheet.Cells.Item($intRow,4) = "PRX1CSDB02"
$Sheet.Cells.Item($intRow,5) = "PRX1CSDB03"
$Sheet.Cells.Item($intRow,6) = "PRX1CSDB11"
$Sheet.Cells.Item($intRow,7) = "PRX1CSDB12"
$Sheet.Cells.Item($intRow,8) = "PRX1CSDB13"
$Sheet.Cells.Item($intRow+1,1) = "File To Tape weekly Full Backup"
$Sheet.UsedRange.Rows.Item(1).Borders.LineStyle = 1

#FTT.txt contains the path for a list of servers
$path =  Get-Content D:\Raghav\DB_Integrated\FTT.txt

foreach ($server in $path)
{
If (Test-Path $server)
{
$BckpWeek = gci -path $server | select-object | where {$_.Name -like "*logw*"} | sort LastWriteTime | select -last 1
$Sheet.Cells.Item($intRow+1,$intCol+1) = $BckpWeek.LastWriteTime.ToString('MMddyyyy')
$Sheet.UsedRange.Rows.Item($intRow).Borders.LineStyle = 1
$x = (get-date) - ([datetime]$BckpWeek.LastWriteTime)
if( $x.days -gt 7){$status_week = "Failed"}
else{$status_week = "Successful"}
$Sheet.Cells.Item($intRow+1,$intCol+2) = $status_week
$intCol++
}
else
{
$Sheet.Cells.Item($intRow+1,$intCol+2) = "Path Not Found"
$intCol++
}
}
$Sheet.UsedRange.EntireColumn.AutoFit()
$workBook.SaveAs("C:\Users\Output.xlsx",51)
$excel.Quit()

However, when I try to import the contents of Output.xlsx into a variable say $cc, I get data in an unreadable format.

$cc = Import-Csv "C:\Users\Output.xlsx"

Attached is the image for what I get on exporting output.xlsx into $cc. I tried to put the output in csv format too. But that also doesnt seem to help.output fileAnybody having any idea on this or having faced any similar situation before?

Vivek Kumar Singh
  • 3,223
  • 1
  • 14
  • 27
  • 3
    Hi, why use `Import-CSV`? `xslx` is different from `CSV`. – sodawillow Dec 28 '16 at 13:31
  • Sorry, I am new to PowerShell. Is there any way by which we can import the contents of an xlsx file to a variable in powershell? – Vivek Kumar Singh Dec 28 '16 at 13:36
  • 1
    Sure, here is [an example](http://stackoverflow.com/questions/19211632/read-excel-sheet-in-powershell) – sodawillow Dec 28 '16 at 13:37
  • 1
    You might also want to consider using the ADO.NET [`OleDbConnection`](https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection(v=vs.110).aspx) class. – Zev Spitz Dec 28 '16 at 13:55
  • 3
    If you'd like to avoid doing all that work in .NET classes you can also use the [ImportExcel](https://www.powershellgallery.com/packages/ImportExcel/2.2.9) which is easier to work with and doesn't require Excel to be present on the machine. – Mike Garuccio Dec 28 '16 at 15:09

1 Answers1

0

@ZevSpitz - Looking for the OleDbConnection class, I landed up at https://blogs.technet.microsoft.com/pstips/2014/06/02/get-excel-data-without-excel/ . This is what I was looking for. Thank you for pointing me out in the right direction.

@MikeGaruccio - Unfortunately, I didn't find Import-Excel command in Get-Help menu. I am using Powershell 4.0. Anyways, thank you for the suggestion.

Vivek Kumar Singh
  • 3,223
  • 1
  • 14
  • 27