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
.NET Programming* SSMSMicrosoft SQL Server
7 Comments2 Solutions81 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$])

Open in new window


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)

Open in new window


Example of my desired output:
Capture.PNG
Thoughts?
Rich
Capture.PNG
ASKER CERTIFIED SOLUTION
Butler Bros
IT Dept

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
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 2 Answers 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