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?
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.