Solved

SQL - reading excel help

Posted on 2013-06-11
7
250 Views
Last Modified: 2013-06-17
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
Comment
Question by:holemania
  • 4
  • 3
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39238564
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
 

Author Comment

by:holemania
ID: 39238777
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39238899
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39238977
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
 

Author Comment

by:holemania
ID: 39239561
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
 

Author Comment

by:holemania
ID: 39253158
Thanks for the help.  Going to see if someone that knows VB.Net will can help with converting this VBA into VB.NEt.
0
 

Author Closing Comment

by:holemania
ID: 39253160
Thank you.  Provided what I need.  Just need to convert to VB.Net.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question