We help IT Professionals succeed at work.

How can i get column name from excel to create sql column?

i need to help to generate column name from excell automatically. i think that: we can do below codes:
CREATE TABLE [dbo].[Addresses_Temp] ( 
    [FirstName]   VARCHAR(20), 
    [LastName]    VARCHAR(20), 
    [Address]     VARCHAR(50), 
    [City]        VARCHAR(30), 
    [State]       VARCHAR(2), 
    [ZIP]         VARCHAR(10) 
) 

Open in new window


via C#. How can i learn column name from excel?
  private void Form1_Load(object sender, EventArgs e)
        {
            ExcelToSql();
        }


        void ExcelToSql()
        {

            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Source\MPD.xlsm;Extended Properties=""Excel 12.0;HDR=YES;""";
            // if you don't want to show the header row (first row)            
            // use 'HDR=NO' in the string             
            string strSQL = "SELECT * FROM [Sheet1$]";            
            OleDbConnection excelConnection = new OleDbConnection(connectionString);           
            excelConnection.Open(); // This code will open excel file.            
            OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);          
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);            
            // create data table            
            DataTable dTable = new DataTable();            
            dataAdapter.Fill(dTable);            
            // bind the datasource          
          //  dataBingingSrc.DataSource = dTable;         
            // assign the dataBindingSrc to the DataGridView         
           // dgvExcelList.DataSource = dataBingingSrc;             // dispose used objects          
            if (dTable.Rows.Count > 0)
                MessageBox.Show("Count:"+dTable.Rows.Count.ToString());
            dTable.Dispose();           
            dataAdapter.Dispose();           
            dbCommand.Dispose();            
            excelConnection.Close();           
            excelConnection.Dispose();
        }

Open in new window

Comment
Watch Question

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Just an example.

            if (dTable.Rows.Count > 0)
                MessageBox.Show("Count:"+dTable.Rows.Count.ToString());
foreach (DataColumn col in dTable.Columns)
     MessageBox.Show(col.ColumnName); // gets the column names from the dTable.Columns collection

Author

Commented:
Also i need to get sheetName Programmatically? Can i write "SELECT * FROM [Sheet1$]" programatically?
Top Expert 2010
Commented:

Yes. you can write:   SELECT * FROM [Sheet1$]

To get list of sheets, try GetSchema method of connection object. Below code works for MS Access/OleDB...you can try it for excel/ACE, it might work...

...
OleDbConnection excelConnection = new OleDbConnection(connectionString);           
            excelConnection.Open(); // This code will open excel file.

List<string> tableNames = GetTableList(excelConnection);
....



public List<string> GetTableList(OleDbConnection connection)
        {
            List<string> tableNames = new List<string>();

            DataTable userTables = connection.GetSchema("Tables");

            foreach (DataRow row in userTables.Rows)
            {
                string tableType = string.Empty;

                if (row["TABLE_TYPE"] != DBNull.Value)
                    tableType = Convert.ToString(row["TABLE_TYPE"]);

                if (tableType == "TABLE")
                {
                    if (row["TABLE_NAME"] != DBNull.Value)
                        tableNames.Add(Convert.ToString(row["TABLE_NAME"]));
                }
            }

            return tableNames;
        }

Open in new window