I am developing ETL Tool to import data from excel template and transform it before loading to sql database. 1) 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. 2) I have a combobox tied to a function known as (GetExcelSheetNames(SVTest:="")), ComboBox1.DataSource = GetExcelSheetNames(SVTest:="") that display all the SheetNames, when i choose any sheetname that represent a table, i want it to automatically pass all the columns//FieldNames to ListView1.Checkboxes, so that i can select the desired Checkboxes:columns, to be mapped to ListView2 before passing to SQL Database.Any idea(s) on the VB.Net Codes (Framework 4) will be very much appreciated.Thanx guys
Reading Data from Excel Workbook
- Thread starter mish
- Start date
How to get column/Field names from excel table displayed in ListView with Checkboxes
The following Code call and display all excel sheets in a combobox and its working perfectly: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:="") '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 Dim index As Integer index = ComboBox1.FindStringExact(0) Label5.Text = ("Number of Column(s) : " & dtCSV.Columns.Count) excelConnectionString.Close()However, when i select a table from the list in the combobox, i want it to display all the table columns in LstView1 with checkboxes........ How can i achieve that? pls guys, its urgent as am developing ETL Tool... My code below
rivate Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged ListView1.Items.Add(GetField(obj:="", FieldName:="")) ListView1.Items.Add(ComboBox1.DisplayMember()) Dim selectedIndex As Integer selectedIndex = ComboBox1.SelectedIndex Dim selectedItem As String selectedItem = ComboBox1.SelectedItem() Label5.Text = ("Number of Column(s) : " & ListView1.Items.Count) End SubIts only display 1 checkbox in the ListView1 without column name but i have 14 columns in the table selected. How can i do it? Can any1 correct the codings for me?Any immediate answer will serve my interest and more. Thanx guys
The following Code call and display all excel sheets in a combobox and its working perfectly: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:="") '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 Dim index As Integer index = ComboBox1.FindStringExact(0) Label5.Text = ("Number of Column(s) : " & dtCSV.Columns.Count) excelConnectionString.Close()However, when i select a table from the list in the combobox, i want it to display all the table columns in LstView1 with checkboxes........ How can i achieve that? pls guys, its urgent as am developing ETL Tool... My code below
rivate Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged ListView1.Items.Add(GetField(obj:="", FieldName:="")) ListView1.Items.Add(ComboBox1.DisplayMember()) Dim selectedIndex As Integer selectedIndex = ComboBox1.SelectedIndex Dim selectedItem As String selectedItem = ComboBox1.SelectedItem() Label5.Text = ("Number of Column(s) : " & ListView1.Items.Count) End SubIts only display 1 checkbox in the ListView1 without column name but i have 14 columns in the table selected. How can i do it? Can any1 correct the codings for me?Any immediate answer will serve my interest and more. Thanx guysSimilar threads
- Question
