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
470 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 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