holemania
asked on
SQL - reading excel help
I have a excel file that I need to read into SQL into a temp table to be use with other SQL tables.
I can read each column as a field, but the way how I have my data layout, it's making it somewhat complicated.
Currently this is what I have:
SELECT * FROM OPENROWSET('Microsoft.Jet.
Here's how my file format is.
I need to be able to read as followed. Is there a way to structure my query so I can read anything passed "Module" as username and everything under "Module" as the Module name and then the permission like the below example?
I can read each column as a field, but the way how I have my data layout, it's making it somewhat complicated.
Currently this is what I have:
SELECT * FROM OPENROWSET('Microsoft.Jet.
Here's how my file format is.
I need to be able to read as followed. Is there a way to structure my query so I can read anything passed "Module" as username and everything under "Module" as the Module name and then the permission like the below example?
ASKER
The number of columns is not fixed and will pull base on number of users. Can this be done via windows application within vb.net? I guess the VBA code for excel would be similar, but this report is generated from SSRS. So maybe if it can be fed into a windows application and do the data manipulation?
Unless you have Excel installed on your server, I don't think you can do an Excel interop via .Net. Thus, I think your best bet is going to be transforming the file before it ever gets into SQL Server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It's working, but giving me a 400 error pop up. Even though there's the 400 error, it seems to be sorting it as the output. Could it be due to Excel 2010?
How can I incorporate this into VB.NET? I was hoping to create a 1 button to open the source file, use what you did above and spit out the transform file. Then do a openquery to read the file and dump it into a sql temp table.
How can I incorporate this into VB.NET? I was hoping to create a 1 button to open the source file, use what you did above and spit out the transform file. Then do a openquery to read the file and dump it into a sql temp table.
ASKER
Thanks for the help. Going to see if someone that knows VB.Net will can help with converting this VBA into VB.NEt.
ASKER
Thank you. Provided what I need. Just need to convert to VB.Net.
The Excel VBA code required to do the transformation is trivial to write. Please indicate whether that is an acceptable approach.