Oracle UNION SELECT from Excel Worksheet: UnionSelectRange() Function

One thing that data wranglers will have to do from time to time is take a bunch of values from an Excel spreadsheet and plug them into an Oracle query. One approach is to use the concatenate() function with a dizzying number of single and double quotes. To make this process much simpler, here is a custom VBA function called UnionSelectRange that handles the work:


Usage:

Generated UNION SELECT sql statement:

select 'adf' as A1, 'dfa' as B1, 'ad' as C1 from dual
union select 'sdddd' as A1, 'af' as B1, 'd' as C1 from dual
union select 'adf' as A1, 'dfa' as B1, '' as C1 from dual
union select 'fa' as A1, '' as B1, 'af' as C1 from dual
union select 'd' as A1, 'adf' as B1, '' as C1 from dual
union select '' as A1, '' as B1, 'adf' as C1 from dual
union select 'df' as A1, 'a' as B1, 'f' as C1 from dual

Limitations:
* Headers are assumed to exist - row 1 is used for column aliasing
* The function expects a single contiguous range of data. Example: you can't pass in something like =UnionSelectRange(A2:D2, F2)
* If the selected range is one row, each row gets a 'union'. You'll have to manually remove the union from the first row
* If the selected range is multiple rows, Excel will put double quotes around the copied union select. Simply remove them to use the SQL.
* Oracle limitations - maximum number of selected columns is 1000, maximum column alias length is 30 characters

Source:

Public Function UnionSelectRange(dataRange As Range) As String
    Dim st As String
    
    Dim x As Long, y As Long
    
    For x = 1 To dataRange.Rows.Count
        If x > 1 Then
            st = st & Chr(13) & Chr(10) & "union "
        End If
        
        'Add a union if doing a row at a time
        If dataRange.Rows.Count = 1 Then
            st = st & "union "
        End If
        
        st = st & "select "
        
        For y = 1 To dataRange.Columns.Count
            If y > 1 Then
                st = st & ", "
            End If
            st = st & "'" & dataRange.Cells(x, y).Value & "' as " & Sheets(dataRange.Parent.Name).Cells(1, dataRange.Cells(x, y).Column).Value
        Next y
        
        st = st & " from dual"
    Next x
    
    UnionSelectRange = st
End Function

Suunto GPS Backup Utility - v0.1

* Note: The executable has only been tested using an Ambit2 and Windows 7. This will probably work on Windows 8 and/or additional Suunto GPS models. Advanced Mac/Linux users may still be able to use the backup utility by following method 2 below.

Prerequisites: Must have Moveslink installed and a Suunto device connected.

Method 1
1) Download ambit_backup.zip

2) Extract the files, run ambit_backup.exe

a) Running the ambit_backup.exe utility.

b) GPX (Strava compatible) files will show up in the Documents / ambit_backup folder. Please note the file names contain the date of the recorded activity:

Method 2 (Advanced)
1) Download and install Python 2.7
2) Download and extract ambit_backup_src.zip
3) Run the ambit_backup Python script:
python ambit_backup.py

Upload Suunto data directly to Strava (without using Movescount)

Update: Please see the Suunto GPS Backup Utility for a slightly easier method.

While I love the Ambit2, the companion analysis and tracking website -- Movescount -- leaves much to be desired. Here is a list of outages for this year alone:

6/17/2014
6/18/2014
8/8/2014
8/28/2014
12/22/2014
12/23/2014
12/24/2014

Given the track record of website instability, could there really be -no- way to get data from the device without Movescount? Fortunately I stumbled across ambit2gpx, an open source utility that converts Suunto SML format to a common GPX format. What this means is that the data can be imported into Strava or other analysis software without first going through Movescount.

You'll need:
* Some level of computer literacy
* Python 2.7
* ambit2gpx

Note: These instructions apply to Windows 7, however the concept is applicable to Mac as well

1) Download and install Python 2.7:
https://www.python.org/download/releases/2.7.7/

2) Download ambit2gpx - move to folder identified on step 4 for ease of use.

3) Allow Moveslink to download data from the Suunto device (and fail to sync)

4) Browse to the application data folder:
On windows 7:
C:\Users\<username>\AppData\Roaming\Suunto\Moveslink2

5) Convert SML to GPX (prior nerd experience required):
a) Open command prompt, browse to the folder above
CD c:\users\killian.jornet\AppData\Roaming\Suunto\MovesLink2
b) Run ambit2gps on sml file to be converted
c:\python27\python ambit2gpx-1.7.py 25AD0A510C000E00-2014-12-21T14_03_37-0.sml

Parsing file 25AD0A510C000E00-2014-12-21T14_03_37-0.sml
Done.
Creating file 25AD0A510C000E00-2014-12-21T14_03_37-0.gpx
................................................................................
.........................................................
Done.

5) The GPX file will be created in the Suunto Moveslink2 data folder above. This can then be uploaded to Strava or elsewhere.

Update: Here is the data as it appears on Strava. In this case I was using multi-sport mode, but all of the GPS waypoints came though:
http://www.strava.com/activities/231993015