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
464 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

760 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

21 Experts available now in Live!

Get 1:1 Help Now