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
474 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

691 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