I recently worked with a customer who was exporting data from Toad Data Point to Excel templates (through automation) and she came up with this cool Excel macro to produce a single cell which has a delimited list of column contents. Here is what she sent me:
**
I created a macro to use when I have a list of account number like data in an Excel file and I want to use them as a parameter in SQL. I thought you may find it useful or want to share with others.
So, say you have a list like column A and you want to add it to an IN statement, run the macro, follow prompts, and copy and paste B1 into Toad.
**
**
Sub ChangeRange()
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "Enter Array to Convert to Range(Ex. $A$1:$A$9)"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Enter Destination (single cell Ex.B1):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
If outStr = "" Then
outStr = "('" & rng.Value & "'"
Else
outStr = outStr & ",'" & rng.Value & "'"
End If
Next
OutRng.Value = outStr & ")"
End Sub
A non macro alternative is to use a formula in cell B1 that is: ="IN ('" & A1 & "'"
Then in cell B2 a similiar formula: =B1 & ", '" & A2 & "'"
Copy the formula in B2 down to the last row and add a close paranthesis to the end of the formula on the last row as such: =B8 & ", '" & A9 & "')"
Then just copy and past the last row in column B to your sql. It may be a little hard to see but there are single quotes inside of the double quotes.
I use the formula version for years, very simple and no advance knowledge needed
Additionally, sometimes the values are trimmed of Zeros at the start because it was pasted as numeric value while column filtered is varchar with zeros (fixed digit) expected. This was often root cause for many data issues because if there is a value 01234 in DB and you paste it to excel the zero is trimmed and instead you compose the ‘IN (…)’ part with values without zero characters and such value will not be then filtered in the result set.
So to fill zeros up to N characters (let’s say 9) the formula would be:
A1 = 1234
B1 =RIGHT(REPT(“0”,9) & A1,9)
So it repeats the Zero character 9 times and concatenates it with the value in A1, so the result would be 0000000001234 and then we take first 9 chars from the right so we get 000001234. This is also safe when used for values that exceed 9 digits where they wont be affected.
So to incorporate this change into Greg’s formula it would be
=B1 & “, '” & RIGHT(REPT(“0”,9) & A2,9) & “’”
Hope this helps someone
