How do I create a SQL table using Excel columns?

advertisements

I need to help to generate column name from excel 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)
)

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();
}


You should be able to iterate over the DataTable's columns collection to get the column names.

System.Data.DataTable dt;
dt = new System.Data.DataTable();
foreach(System.Data.DataColumn col in dt.Columns)
{
     System.Diagnostics.Debug.WriteLine(col.ColumnName);
}