Solved

How to pull values from Excel into a comma separated list to use in a SQL update statement

Posted on 2011-09-07
7
468 Views
Last Modified: 2012-05-12
I have a spreadsheet that basically has a bunch of primary key ids that I asked a co-worker to put together.  I can run a simple update statement in SQL Server to update the status of all of these records but I need to get the values into a comma separated list to use for the "IN ()" clause.

Is there a way to auto generate a list (1,2,3,4) from an Excel spreadsheet.  The Spreadsheet has one column of numbers and a 2nd column of different primary keys but that column also has some blank columns.

I have tried saving as a CSV but it still opens in Excel and I'm not sure how to grab a simple value list.

Thanks in advance for any tips!
0
Comment
Question by:Ike23
  • 4
  • 3
7 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 36499552
You can put all your values in a row, save as csv and open the csv in notepad.

You can also use a custom function to concatenate your values with a comma separator. I'm attaching the code for one such function.

Thomas
Public Function concatPlus(rng As Range, sep As String, Optional noDup As Boolean = False, Optional skipEmpty As Boolean = False) As String
'concatenates a range with the specified separator
Dim cl As Range, strTemp As String

If noDup Then

    Dim newCol As New Collection

    On Error Resume Next

    For Each cl In rng.Cells
        If skipEmpty = False Or Len(Trim(cl.Text)) > 0 Then _
            newCol.Add cl.Text, cl.Text
    Next
    
    For i = 0 To newCol.Count
        strTemp = strTemp & newCol(i) & sep
    Next

Else
    
    For Each cl In rng.Cells
        If skipEmpty = False Or Len(Trim(cl.Text)) > 0 Then _
            strTemp = strTemp & cl.Text & sep
    Next

End If

concatPlus = Left(strTemp, Len(strTemp) - Len(sep))

End Function

Open in new window

0
 
LVL 4

Author Comment

by:Ike23
ID: 36499563
I was going to do the CSV idea but all the values are not in a row but rather in a single column so they are all on their own row.  I'm trying to avoid having to copy and paste them again.

I could probably whip up a query in a web app and display the values in a list but thought there is probably a way to do it in Excel or SQL.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 36499576
The function I've attached will do it. Put it in a module in your file and call it from the workbook like
=concatplus(A1:A15,",")
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 4

Author Comment

by:Ike23
ID: 36499605
Sorry for my lack of Excel skills but where do I add the "module"?  
0
 
LVL 39

Expert Comment

by:nutsch
ID: 36499619
Go to Visual basic editor (Alt+F11), Insert \ Module, copy the code.

T
0
 
LVL 4

Author Comment

by:Ike23
ID: 36499679
Just for my own knowledgebase I am writing down the steps.  Thanks T!

In Excel 2010:

The VBA is not shown by default.  To add go to Options: File|Options|Customize the ribbon|on the right select 'Main tabs'|Check 'Developer'

In VBA click Insert>>Module

In Excel call the function on the page you want to use it and fill in the ranges.

--------------------------------------------------------------------------------
0
 
LVL 39

Expert Comment

by:nutsch
ID: 36499705
Glad to help. Thanks for the grade.

Thomas
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now