Solved

SQL - reading excel help

Posted on 2013-06-11
7
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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