Solved

SQL - reading excel help

Posted on 2013-06-11
7
252 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

730 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