Solved

SQL - reading excel help

Posted on 2013-06-11
7
248 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now