A quick list-to-array conversion trick

Somebody sends you an Excel spreadsheet with a bunch of OBS terms on it. Could be patient PIds, could be anything – it’s a big list. You want to use that list in a query, and there could be dozens or hundreds of them. Options are to set up a data connection to the spreadsheet, or paste the list into your query and start adding single quotes and commas. Or, you can copy this little bit of code and put it in your tricks file:

Copy the list out of Excel and paste it into the @InputString variable:

Now execute the script, and copy/paste the results into your query. Here’s what you get:

Perfect for adding to your IN clause. It’s a lot easier than either of the other two methods. And a little bit of tweakage will generate a similar list for insertion into a temp table or table variable:

Resulting in…

You’re welcome!

Leave a Reply