Click here to Skip to main content
15,882,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am developing ETL Tool to import data from excel template and transform it before loading to sql database.

All table names ( in the excel sheets) are to be read dynamically. How do i read unknown table/Sheet name from the excel template, i have upto 20 Sheets? I have tried, [ " & SheetName & "$] or [Table_Name] ||[TableName] is not valid.



Any idea(s) on the VB.Net Codes (Framework 4) will be very much appreciated.Thanx guys
Posted
Updated 17-Aug-11 19:19pm
v3
Comments
Mcmish 7-Jul-11 12:44pm    
Hi Dave, any solution for me as am in dire need of solution to the seeming puzzle. Thanx

This is an example code block but you must use this reference

Imports Excel = Microsoft.Office.Interop.Excel

Code Block :
VB
Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim range As Excel.Range
        Dim rCnt As Integer
        Dim cCnt As Integer
        Dim Obj As Object

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("c:\vbexcel.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets(1)

        range = xlWorkSheet.UsedRange

        For rCnt = 1 To range.Rows.Count
            For cCnt = 1 To range.Columns.Count
                Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
                MsgBox(Obj.value)
            Next
        Next

        xlWorkBook.Close()
        xlApp.Quit()
 
Share this answer
 
v2
Comments
Maciej Los 6-Jul-11 16:25pm    
In this line: <pre lang="vb">xlWorkSheet = xlWorkBook.Worksheets(0)</pre> probably is error. Count of worksheets start from 1 to n.
Emrah Taylan 6-Jul-11 16:40pm    
Yes :) I forgat when I send answer, I am using c# :)
Mcmish 6-Jul-11 23:26pm    
Emrah, I quite appreciate, thanx alot but i must clarify a point that the Excel template is define differently by names WebTMA upto 4 different templates, so i dynamically chose the particular template and am able to display all the sheets in a combobox but i must specify one of the sheets's name before it works, so am required not to specify any specific sheet name so that any1 i chose from d combobox will be use. see my code below:



Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
OpenFileDialog1.ShowDialog()
XlsPath.Text = OpenFileDialog1.FileName


Dim excelConnectionString As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & OpenFileDialog1.FileName & ";" + " Extended Properties=Excel 8.0")

'Connection to Excel Datasheet
excelConnectionString.Open()

'Fetching all sheets from the Excel Template (Function Call)
ComboBox1.DataSource = GetExcelSheetNames(SVTest:="")
'ComboBox1.DataSource = GetDataFromExcel(SVTest:="")

'OleDbCommand to Fetch Data from Columns and Rows of selected sheet
Dim cmd As New System.Data.OleDb.OleDbCommand("SELECT * FROM [Details$]", excelConnectionString)
Dim daCSV As New OleDbDataAdapter()
daCSV.SelectCommand = cmd
Dim dtCSV = New DataTable()
daCSV.Fill(dtCSV)
ListView1.Visible = True
ListBox2.Visible = True
'ListView1.DataBindings.Add()

Dim index As Integer
index = ComboBox1.FindStringExact(0)
Label5.Text = ("Number of Column(s) : " & dtCSV.Columns.Count)
excelConnectionString.Close()


End Sub

Here is where i have problem, the SELECT * FROM [ ], i don't wanna specify the sheet name "Details" so that i can also work on other sheet as well:

Dim cmd As New System.Data.OleDb.OleDbCommand("SELECT * FROM [Details$]", excelConnectionString)

Any solution will be appreciated.
Thanx once again
Hi All, thanx for your supports and contributions, i was able to use the
GetOleDbSchemaTable
concept to solve the problem. Found below is the solution:

VB
Dim f As New OpenFileDialog()
      f.Filter = "Excel files | *.xls"
      f.InitialDirectory = My.Application.Info.DirectoryPath

      If f.ShowDialog() = DialogResult.OK Then
          If f.FileName IsNot Nothing AndAlso f.CheckFileExists = True Then
              Me.SVTest = f.FileName
              Me.XlsPath.Text = f.FileName
              Label6.Text = ("Driver: ") & f.FileName
              Open()


              dt = ODBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
              For Each row As DataRow In dt.Rows
                  Dim sq As New OleDbDataAdapter("select * from [" & row.Item("TABLE_NAME") & "]", ODBConn)

                  Dim XlsDT As New DataTable
                  sq.Fill(XlsDT)
                  XlsDT.TableName = row.Item("TABLE_NAME")
                  'ds.Add(XlsDT)
                  ComboBox1.Items.Add(XlsDT.TableName)

              Next
              Close()

              End If
              End If
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900