troubleshooting Question

how to query excel columns via SSMS, and manipulate the results ?

Avatar of Butler Bros
Butler BrosFlag for United States of America asked on
Microsoft SQL Server.NET Programming* SSMS
7 Comments1 Solution81 ViewsLast Modified:
Goal: to create a series of .NET code (CASE)statements from data derived from an Excel spreadsheet, queried via SSMS.

I have a spreadsheet(mysheet.xlsx) which contains 5 columns of data(990 rows)
e.g.
'111222'        'A COMPANY      NAME'   'Col 1:  20'      'Col 2:  15'      'Col 3:  10'


I am able to query the data successfully using:
SELECT TOP 5 * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=Z:\mysheet.xlsx', [Sheet1$])

What I wish to do is, take that queried data, and perform manipulation on it, such that I end up with multiple .NET CASE statements: possibly stuffing the results in a table, in a single row per result.
e.g. using the above row data from Excel, I would like to end up with:

Case 111222 MsgBox("company is:"  A COMPANY	NAME + Environment.NewLine + Col 1:  20 Col 2:  15 Col 3:  10)

Example of my desired output:
Capture.PNG
Thoughts?
Rich
Capture.PNG
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros