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

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
Richard GouetteIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
I'm still not exactly clear on your objective.  Can you give me an example of how a user would interact with your process and what results would be expected?

Are you trying to query Excel data in .NET and use that in a form?  Is there a reason why you have the Excel data in an .xlsx and not in a database itself?
0
Richard GouetteIT ManagerAuthor Commented:
see corrections in my OP.
Sorry for the confusion
0
Dustin SaundersDirector of OperationsCommented:
What I mean is, what's the purpose of the case statements?  Do you want to generate 900 different case statements for your code in SSMS?  Are you putting these case statements in a .NET app or in Excel vb code..?

You have a table structure...  why not use that for your select results rather than rows and rows of case statements?  What happens when a row is added?  You have to re-run SSMS and then paste in the new case statements?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Richard GouetteIT ManagerAuthor Commented:
I know it's not ideal, but there is a business case, and a need to do it that way for the time being.
These statements reside within .NET code

To your last question: Yes

:)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dustin SaundersDirector of OperationsCommented:
I would recommending doing this a different way.  But to answer your question see this example.
SELECT 'Case ' + CAST([Company] AS VARCHAR(50)) + ' MsgBox("company is:" ' + [Name] + ' + Environment.NewLine + ' + [First] + ' ' +  [Second] + ' ' + [Third] AS "code"
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test\data.xlsx', [Sheet1$])

Open in new window


(In my xlsx i named the columns for the Col: items 'First','Second','Third'
0
Dustin SaundersDirector of OperationsCommented:
(see attached)
data.xlsx
0
Richard GouetteIT ManagerAuthor Commented:
I had just figured it out  about 5 or so minutes ago, using code very much like yours.

thanks,
Rich
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.