?
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
Medium Priority
?
476 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 2000 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

741 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