Link to home
Create AccountLog in
Avatar of Butler Bros
Butler BrosFlag for United States of America

asked on

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:
User generated image
Thoughts?
Rich
Capture.PNG
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

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?
Avatar of Butler Bros

ASKER

see corrections in my OP.
Sorry for the confusion
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?
ASKER CERTIFIED SOLUTION
Avatar of Butler Bros
Butler Bros
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
(see attached)
data.xlsx
I had just figured it out  about 5 or so minutes ago, using code very much like yours.

thanks,
Rich