[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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.OleDB.4.0', 'Excel 8.0;Database=C:\Excel\UserSecurity.xls', 'SELECT * FROM [Sheet$]')

Here's how my file format is.

Excel Example
 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?

Output desire
0
holemania
Asked:
holemania
  • 4
  • 3
1 Solution
 
Patrick MatthewsCommented:
In my opinion, the best thing to do is to flatten the file in Excel, before it ever gets imported, unless the number of columns from the Excel is fixed and you know the column names ahead of time.

The Excel VBA code required to do the transformation is trivial to write.  Please indicate whether that is an acceptable approach.
0
 
holemaniaAuthor Commented:
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?
0
 
Patrick MatthewsCommented:
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.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Patrick MatthewsCommented:
This is VBA that can recast the file.  It can be adapted to VBScript and to VB.Net if needed.



Sub Flatten()

    Dim LastR As Long, LastC As Long
    Dim arr As Variant
    Dim RCounter As Long, CCounter As Long
    Dim DestArr() As Variant
    Dim DestRow As Long
    Dim wb As Workbook
    
    With ThisWorkbook.Worksheets("Sheet4") 'rename as needed
        LastR = .Cells(.Rows.Count, "b").End(xlUp).Row
        LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        arr = .Range(.Cells(1, "b"), .Cells(LastR, LastC)).Value
    End With

    ReDim DestArr(1 To (LastR - 1) * (LastC - 2), 1 To 3) As Variant
    
    For CCounter = 2 To (LastC - 1)
        For RCounter = 2 To LastR
            DestRow = DestRow + 1
            DestArr(DestRow, 1) = arr(RCounter, 1)
            DestArr(DestRow, 2) = arr(1, CCounter)
            DestArr(DestRow, 3) = arr(RCounter, CCounter)
        Next
    Next
    
    Set wb = Workbooks.Add
    
    [a1:c1] = Array("Module", "User", "Permission")
    [a2].Resize(UBound(DestArr, 1), UBound(DestArr, 2)).Value = DestArr
    
    wb.SaveAs "c:\Test\Foo.xls", xlExcel12
    wb.Close
    
End Sub

Open in new window

0
 
holemaniaAuthor Commented:
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.
0
 
holemaniaAuthor Commented:
Thanks for the help.  Going to see if someone that knows VB.Net will can help with converting this VBA into VB.NEt.
0
 
holemaniaAuthor Commented:
Thank you.  Provided what I need.  Just need to convert to VB.Net.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now