How can I create a SQL table using excel columns?
- by Phsika
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();
}
