Penguen Penguen - 3 months ago 15
C# Question

How can I create a SQL table using excel columns?

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

Answer

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