Powershell eh?
Don't threaten me with a good time!
I took a stab at it. Code below.
Here's what it looks like in action:
PS C:\code\Powershell> .\jil2xcel.ps1 .\jobs.jil
PS C:\code\Powershell> dir
Directory: C:\code\Powershell
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a--- 2/25/2017 4:30 PM 2922 jil2xcel.ps1
-a--- 2/25/2017 4:16 PM 1753 jobs.jil
-a--- 2/25/2017 4:30 PM 8533 jobs_201702251641.xlsx
PS C:\code\Powershell>
jil2xcel.ps1 |
---|
#* ------------------------------------------------------------------------- * #* * #* Copyright (c) 2017 CA. All rights reserved. * #* * #* This software and all information contained therein is confidential and * #* proprietary and shall not be duplicated, used, disclosed or disseminated * #* in any way except as authorized by the applicable license agreement, * #* without the express written permission of CA. All authorized * #* reproductions must be marked with this language. * #* * #* EXCEPT AS SET FORTH IN THE APPLICABLE LICENSE AGREEMENT, TO THE EXTENT * #* PERMITTED BY APPLICABLE LAW, CA PROVIDES THIS SOFTWARE WITHOUT WARRANTY * #* OF ANY KIND, INCLUDING WITHOUT LIMITATION, ANY IMPLIED WARRANTIES OF * #* MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT WILL * #* CA BE LIABLE TO THE END USER OR ANY THIRD PARTY FOR ANY LOSS OR DAMAGE, * #* DIRECT OR INDIRECT, FROM THE USE OF THIS SOFTWARE, INCLUDING WITHOUT * #* LIMITATION, LOST PROFITS, BUSINESS INTERRUPTION, GOODWILL, OR LOST DATA, * #* EVEN IF CA IS EXPRESSLY ADVISED OF SUCH LOSS OR DAMAGE. * #* * #* ------------------------------------------------------------------------- * # jil2xcel.ps1 param ( [Parameter(Mandatory=$true)][string]$jilFile ) function main { $oldVerbose = $VerbosePreference $VerbosePreference = "continue"
If (!(Test-Path $jilFile)) { Write-Verbose "Input file not found: $jilFile. Exiting..." exit(127) } # Get JIL and strip comment lines $jilDB = Get-Content $jilFile | select-string -pattern "`/`* -------" -notmatch | Out-String
# Create array of jobs $jobs = $JilDB -split 'insert_job: '
# Create Excel file $ExcelObject = new-Object -comobject Excel.Application $ExcelObject.visible = $false $ExcelObject.DisplayAlerts =$false
$ActiveWorkbook = $ExcelObject.Workbooks.Add() $ActiveWorksheet = $ActiveWorkbook.Worksheets.Item(1) # Write each job to Excel file $Row = 1 for ($i=0; $i -lt $jobs.Length; $i++) { $jobOneLiner ="" $jobs[$i].split("`r`n") | ForEach-Object { $jobOneLiner += "$_ " } #Write-Host $jobOneLiner $ActiveWorksheet.Cells.Item($Row, 1) = $jobOneLiner $Row++ } # Save Excel file and clean up $scriptDir = (Get-Location).Path $date= get-date -format "yyyyMMddHHss" $ActiveWorkbook.SaveAs("$scriptDir\jobs_$date.xlsx") $ExcelObject.Quit() $ExcelObject = $Null } # main # Entry point main
|
Regards,
Mark Hanson