1

My job has tools that require us to mass upload data through Excel workbooks, so I'm trying to embed my SQL results into my files to save me a step. I've created the data connection, but haven't quite figured out the code format in the 'Definition' box yet.

Right now I have:

select custcontnum from "CONTACT"."dbo"."Incont" where custcode='SNH' and username = '[Custcontnums$G2]'

But it's pulling back blank. (If I define the username criteria itself between single quotes, it does work) Eventually I would like to create an "in" statement for the username criteria, so something like:

select custcontnum from "CONTACT"."dbo"."Incont" where custcode='SNH' and username in ('[Custcontnums$G2]','[Custcontnums$G3]','[Custcontnums$G4]')

What is the correct format for an excel reference in the data connection feature for Excel?

Improve this question
6
  • Because it is a string you must exclude the code parts of the string using the & concatenation. For example "select custcontnum from CONTACT.dbo.Incont where custcode='SNH' and username = '"&[Custcontnums$G2]&"'" Commented Jan 19, 2016 at 15:55
  • Hm, if I try that, I get an "Incorrect syntax near '&' " error. I verified that there is data in cell G2. Commented Jan 19, 2016 at 16:18
  • Can you show more of the code you are using? Commented Jan 19, 2016 at 16:20
  • This might be a duplicate to the following: stackoverflow.com/questions/1285686/… or the following: superuser.com/questions/197453/… Commented Jan 19, 2016 at 16:24
  • Unfortunately that's it. I'm looking to reference a specific username and pull back that user's unique ID in the database. The incorrect syntax error appears when I plug in this code: "select custcontnum from CONTACT.dbo.Incont where custcode='SNH' and username = '"&[Custcontnums$G2]&"'" It's hung up on the first ampersand, but the formatting appears to be correct. Commented Jan 19, 2016 at 16:28
Related questions
766
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
511
Exporting data In SQL Server as INSERT INTO
441
Selecting data from two different servers in SQL Server
Load 3 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.