Automatically convert an Excel table to a checklist for JIRA

3161323938_3bb7be6248_m
JIRA supports a subset of Wiki Markup to add tables and other formatting to fields like Description or Comments. Writing the Wiki Markup manually is a bit time consuming though. To make the process a bit easier I ended up writing a Macro that converts an Excel based task tracker list into JIRA.
From this:
jiraTable
Into that:
jiraWikiMarkup
Running the Macro will copy the Wiki Markup to the clipboard from where it can be pasted into JIRA.
The Macro relies on a mapping between Status values and supported Markup symbols and assumes that the selected cell is within the range that needs to be converted before running the Macro. The VBA project requires the following additional references:

  1. Microsoft Scripting Runtime (for the Dictionary object)
  2. Microsoft Forms 2.0 Object Library (for the DataObject to provide the copy to clipboard functionality)


A Workbook containing the example can also be downloaded from GitHub.

shareThoughts


Photo Credit: chantrybee via Compfight cc

Advertisements

Automatically clean-up excel data with multiple entries per cell separated by comma or line-breaks

tree
This is a follow-up from a previous post where I did the same using PowerShell.
A short recap first. The goal is to turn something like this:
CleanUpData
Into this:
CleanUpData2
In the original state some of the cells have multiple entries that are either separated by comma or line-breaks (via Alt+Enter). Furthermore several of those entries contain extraneous spaces. In order to tabulate the data the columns for those rows that contain multiple entries per cell also need to be cross-joined (or Cartesian product) to ensure all possible combinations for the entries are accounted for.
Rather than merely translating the recursive CartesianProduct function from the previous post into VBA I decided to follow a different approach.
Utilizing ADO to build a cross-join (without duplicates) across columns for rows that contain multiple entries. In order do that (I’m not really good at VBA and there might be better ways, that I’d love to hear about) the columns need to be copied to separate sheets so that the ADODB adapter recognizes them as separate tablesI actually found that there is no need to copy the columns to separate sheets since ADO also accepts range references.The SQL for the cross-join with only unique entries is very simple. Assuming the following setup (for the separated entries of the second row from our example):
cross-join3
The Macro to build the cross-join looks like this:

If you like to follow along here are the steps:

  1. Setup up the workbook as in the screenshot and save it (as .xlsm)
  2. Press Alt+F11 to open the Visual Basic Editor (VBE)
  3. Locate the project for your workbook (VBAProject(NAME.xlsm)) within the project tree view on the left hand side
  4. Right-Click the project entry and pick ‘Insert -> Module’
  5. Copy and paste the code into the new window
  6. From the menu at the top select Tools -> References…
  7. Tick the box for the entry ‘Microsoft ActiveX Data Objects 2.x Library’ and click ‘OK’
  8. Close the VBE window
  9. Run the Macro by hitting Alt+F8 and picking the entry for ‘CrossJoinRanges’ and clicking on ‘Run’

If everything worked out (in case it didn’t you can download the workbook via GitHub) the workbook should now contain a new sheet with the cross-joined content of the other sheets:
cross-join2
To turn this into a re-usable generic Macro the columns for the rows containing cells with multiple-entries need to be copied to a temporary sheet and the SQL statement needs to be build dynamically based on the number of columns and rows. Furthermore the Macro should also take care of rows that do not contain multiple entries, add the header to the output, and remove extraneous spaces from all entries. The final result uses two Subs one for the cross-join (CrossJoinRangesWithoutDupes) and another one that acts as the main entry point and to do the rest of the job (CleanData) and a little helper function (isSaved) to determine whether the workbook has ever been saved (otherwise I didn’t get the ADODB connection to work):

A workbook containing the Macro that produces the output shown at the top of the post can be downloaded from my GitHub repo. If you’d like to use the function more frequently I would recommend adding it to the Personal Macro Book as outlined here in order to have it available across all Excel files.

shareThoughts


Photo Credit: byb64 via Compfight cc

Using the Excel intersection operator

tree
One of the lesser-known features of Excel is the intersection operator which can be used to simplify look-up operations. An intersection is the overlap of two or more cell ranges within excel. For instance:
In the screenshot below the ranges C1:C5 and B3:D3 (Cell ranges in Excel are written by using the range operator “:”) overlap in the cell C3.
intersection1
The intersection operator ” ” (a space) can be used to find the intersection of ranges. To find the intersection of the two ranges one can just use the following formula “=C1:C5 B3:D3”:
intersection2
Combining the intersection operator with named ranges yields to pretty intuitive look-ups in Excel. Let’s take up another example using monthly revenue data by region:
intersection3
Highlighting the table and pressing CTRL+SHIFT+F3 will bring up the “Create Names from Selection” dialog. We can go with the defaults (Top row, Left column) in order to create named ranges for each column and row within the table based on their labels:
intersection4
Now, in order to retrieve the March Results for the East region we can simply use “=March East”:
intersection5
Getting the Sum of the revenues from January-April for the West region is equally simple “=Sum(January:April West)”:
intersection6
Even non-consecutive ranges can be easily referred to. Pulling up the Sum of revenues for the month of January, March, and May for the South region is as easy as typing “=Sum((January,March,May) South)”:
intersection7

shareThoughts


Photo Credit: 1 brian via Compfight cc

How to convert Excel 97-03 (.xls) to new format (.xlsx) using office file converter (ofc.exe)

tree13

Usually one would use something like the code below in order to convert Excel 97-03 (.xls) files to the new format (.xlsx) via PowerShell through the Excel COM Interop interface:

function Remove-ComObject {
 # Requires -Version 2.0
 [CmdletBinding()]
 param()
 end {
         Start-Sleep -Milliseconds 500
         [Management.Automation.ScopedItemOptions]$scopedOpt = 'ReadOnly, Constant'
         Get-Variable -Scope 1 | where {
             $_.Value.PSTypenames -contains 'System.__ComObject' -and -not ($scopedOpt -band $_.Options)
         } | Remove-Variable -Scope 1 -Verbose:([Bool]$PSBoundParameters['Verbose'].IsPresent)
         [GC]::Collect()
     }
}

function Convert-XLStoXLSX($inputXLS, [switch]$keep){
	Add-Type -AssemblyName Microsoft.Office.Interop.Excel
	$xlFormat=[Microsoft.Office.Interop.Excel.XLFileFormat]::xlWorkbookDefault
	#remove old output file if existent
	$outputXLSX=[IO.Path]::ChangeExtension($inputXLS,'xlsx')
	if (test-path "$outputXLSX"){Remove-Item "$outputXLSX" -Force}
	$xl = New-Object -com "Excel.Application"
	$xl.displayalerts = $False 
        $xl.ScreenUpdating = $False
	$wb = $xl.workbooks.open($inputXLS) 
	$ws = $wb.worksheets.Item(1)
	$wb.SaveAs($outputXLSX,$xlFormat) 
	$wb.Close()
	$xl.Quit()
	Remove-ComObject
        if (!$keep){
	     Remove-Item $inputXLS -Force
        }
}

In my case since I’ve switched to Excel 2013 the above method (and actually all excel automation via COM Interop) is much slower than compared to Excel 2010. This caused me to look for alternative methods on how to automatically convert Excel files from the old format to the new format. The first promising candidate I found was excelcnv.exe, which comes as part of the office installation and can be found under ‘C:\Program Files (x86)\Microsoft Office\Office14’ (replace 14 with the appropriate version number). This is a tool to convert between old and new Excel format and vice versa. Here are some lines of PowerShell that utilize excelcnv.exe:

function ConvertTo-XLSX($xlsFile){
    $xlsxFile = [IO.PATH]::ChangeExtension($xlsFile,'xlsx')
    Start-Process -FilePath 'C:\Program Files (x86)\Microsoft Office\Office14\excelcnv.exe' -ArgumentList "-nme -oice ""$xlsxFile"" ""$xlsFile"""
}
#usage
ConvertTo-XLSX "$env:USERPROFILE\Desktop\Book1.xls"

While this converted the file successfully it also popped up a dialog after running it to convert multiple files that Excel did not launch correctly and asking whether to start in SafeMode. Not really something I was willing to accept.
The next option I came across was the Office File Converter (ofc.exe) which comes as part of Office Migration Planning Manager (OMPM). It requires you to install the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats. Ofc.exe can be used to convert any of the older office formats (i.e. ppt, doc, xls) to the new format details about the usage can be found here. The settings are controlled via an .ini file (ofc.ini). For my purpose I wanted it to convert all .xls files in a folder of my choice to .xlsx keeping the same file name and using the same folder without having to manually modify the .ini file for every conversion.
Those are the steps to setup ofc.exe to do just that through a PowerShell script:

  1. Download and install the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats
  2. Download OMPM
  3. Extract the OMPM download (comes as a self extracting archive)
  4. Copy the ‘Tools’ folder to a location of your choice (the script assumes C:\Scripts\ps1)
  5. Optionally: Rename the file ofc.ini in the Tools folder to something else (e.g. ofc_default.ini in order to keep it as a reference

Having the setup described above in place allows us to use PowerShell to create the .ini with the appropriate settings on the fly in order to instruct ofc.exe to copy the files .xlsx to the same folder along with some other default settings. The .xls files are deleted unless the -keep switch is used.:

function ConvertTo-XLSX($xlsFolder, [switch]$keep){
    $ofcFolder = 'C:\Scripts\ps1\Tools'
    $xlsxFile = [IO.PATH]::ChangeExtension($xlsFile,'xlsx')
@"
[RUN]
LogDestinationPath=$env:Temp
TimeOut=300
[ConversionOptions]
FullUpgradeOnOpen=1
MacroControl=0
CABLogs=1
[FoldersToConvert]
fldr=$xlsFolder
[ConversionInfo]
SourcePathTemplate=$('*\' * $xlsFolder.Split('\').Count)
DestinationPathTemplate=$xlsFolder
"@ | Out-File "$ofcFolder\ofc.ini"
    & "$ofcFolder\ofc.exe" "$ofcFolder\ofc.ini"
    if (!$keep){
        del "$xlsFolder\*.xls"
    }
}

The conversion through ofc.exe runs faster on my system than using the COM interop method, in addition, ofc also has the ability to convert office files within nested folder structures up to 10 level deep.

shareThoughts


Photo Credit: x1klima via Compfight cc

Convert between US and European date formats in Excel

tree7

The easiest way I know of (please let me know if you know a better way) to convert between US (“mm/dd/yy”) and European (“dd/mm/yy”) dates without using VBA in Excel is via “Text to Columns”. Let’s look at an example:
Convert date1
My system’s regional settings are setup for US dates, therefore I need to convert the dates to US format in order to make the Weekday function return a proper result. Here are the steps:

  1. Highlight the range of dates to convert (A2:A6)
  2. Click on “Text to Columns” in the Data ribbon
  3. Go with the defaults in the first two steps of the wizard
  4. Select “Date” as Column data format and pick the appropriate Format (DMY) from the dropdown
  5. Modify the Destination to paste the results somewhere else if necessary (needs to be on the same sheet)
  6. Click on “Finish”

ConvertDates2
ConvertDates3

shareThoughts


photo credit: Bouleau d’hiver, Megève, Haute-Savoie, Rhône-Alpes, France. via photopin (license)

“Please wait while windows configures Microsoft Visual Studio…” when starting Excel

tree6

I got this dialog (“Please wait while windows configures Microsoft Visual Studio Professional 2013”) on every Excel (2010) start up shortly after I had installed Visual Studio 2013 Community Edition. In my case it delayed the Excel start-up for several minutes. In order to get rid of the quite annoying dialog I just created a new directory under C:\WINDOWS\Microsoft.NET\Framework\URTInstallPath_GAC. To do so just run the command below from an elevated command- or PowerShell prompt:

mkdir C:\WINDOWS\Microsoft.NET\Framework\URTInstallPath_GAC

photo credit: Mañana… – Tomorrow… via photopin (license)