MS Office: Excel for SQL Developers I (Range to Delimited List of Values)

    For many SQL developers Excel could prove to be a useful tool in what concerns not only data analysis and manipulation but also in using its functionality for handling repetitive tasks. One of the simplest such examples I may thing of is the formatting of a list of values for use within a query.

    Typically users are requesting to provide a report based on a list of values provided in Excel, for example a list of Products. If the list is quite small, let's say 5-10 products, list's transformation to be used in the query is not quite a big deal. On the other side when dealing with hundreds or thousand of records the story totally changes. In all cases what I do is to select first the distinct list of values and copy them eventually in a new sheet where I use a formula like the one in the screenshot and apply it to the whole data set. 

 
 

 
Notes:
1.    Within the = ", '" & TRIM(A2) & "'" formula the Trim function helps to remove the additional spaces.
2.    Please note that when the values from Excel were copied beforehand from other applications (e.g. Web pages) it could happen that additional formatting is stored resulting in unexpected behavior. What it helps to solve this issue is to copy paste the values in another sheet using the Paste Special feature, use Data/Text To Columns functionality for each column involved or export the data to a comma delimited file and re-import it in another Excel sheet.

     Now all I have to do is to copy the transformed output into my SQL Editor of choice and use it in a query.

SELECT * -- specify explicitly the attributes! 
FROM Production.Product 
WHERE ProductNumber IN ( 
'BK-R93R-62' , 'BK-R93R-44' 
, 'BK-R93R-48' 
, 'BK-R93R-52' 
, 'BK-R93R-56' 
, 'BK-R68R-58' 
, 'BK-R68R-60' 
, 'BK-R68R-44' 
, 'BK-R68R-48' 
, 'BK-R68R-52' 
, 'BK-R50R-58' 
, 'BK-R50R-60' 
, 'BK-R50R-62' 
, 'BK-R50R-44' 
, 'BK-R50R-48' 
, 'BK-R50R-52' 
, 'BK-R50B-58' 
, 'BK-R50B-60' 
, 'BK-R50B-62' 
, 'BK-R50B-44') 

    An even much easier approach is to create a simple macro that takes as input the range I would like to extract the information from and transform the input directly into a comma delimited list of values. Here’s the function used for this purpose: 

 

Function GetRangeAsList(ByVal rng As Range) As String 
Dim result As String 
Dim cell As Range 
For Each cell In rng 
    If Len(Trim(cell.Value)) > 0 Then 
       result = result & ", '" & Trim(cell.Value) & "'" 
    End If 
Next 
GetRangeAsList = IIf(Len(result) > 0, Right(result, Len(result) - 1), result) 
End Function 

   Now all I have to do in the Excel sheet with my data is to type the following formula =GetRangeAsList(A2:A21) in an empty cell out of existing dataset, function’s output being similar to the previous used formula, however the list of values is more compact:
'BK-R93R-62', 'BK-R93R-44', 'BK-R93R-48', 'BK-R93R-52', 'BK-R93R-56', 'BK-R68R-58', 'BK-R68R-60', 'BK-R68R-44', 'BK-R68R-48', 'BK-R68R-52', 'BK-R50R-58', 'BK-R50R-60', 'BK-R50R-62', 'BK-R50R-44', 'BK-R50R-48', 'BK-R50R-52', 'BK-R50B-58', 'BK-R50B-60', 'BK-R50B-62', 'BK-R50B-44'

Note:
  The function could be easily changed to provide the delimiter as parameter too, though I haven’t met many occasions when other delimiter was required.