Click here to Skip to main content
14,691,836 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have excel sheet.in that i have records.

i want to export the excel sheet data into sql server.

How to do please help me.
Posted
Comments
Herman<T>.Instance 19-Dec-12 9:00am
   
Sql Server version? Excel version?

Hi.

Select your Database name in Sql Server-> right click > Import Data

1. in Data source : select the Microsoft Excel
2. Excel File path : Give your Excel path
3. version : select version of excel

Now give the Db Name and Table name where you want to import data.
and done.
   
   
I think below code can help you:

private void Form1_Load(object sender, System.EventArgs e)
{
    Workbook workbook = new Workbook();
    workbook.LoadFromFile(@"..\..\DataTableSample.xls");
    Worksheet sheet = workbook.Worksheets[0];
    DataTable data= sheet.ExportDataTable();
  //set the string connection to be the local databasestring
    string connectionStr="";
    SqlConnection conn= new SqlConnection(connectionStr);
    for (int i=0;i<data.rows.count;i++)>
    {
        DataRow row=data.Rows[i];
        int columnCount=data.Columns.Count;
        string[] columns=new string[columnCount];
        for(int j=0;j<columncount;j++)>
        {
            columns[j]=row[j].ToString();
        }
       // suppose there are only three columns in your datatable

        SqlCommand command=new SqlCommand ("insert into DataTableSample(column1,column2,column3) values('"+columns[0]+"','"+columns[1]+
       " ','"+columns[2]+"')");
        command.ExecuteNonQuery();
    }
    conn.Close();
}


This code is from the answer of this similar question:
How to Import excel file (.xls &.xlsx) into sql server 2000 dynamically without using sqlbulkcopy class or sqlserver import/export wizard[^]
   
Comments
Herman<T>.Instance 19-Dec-12 9:00am
   
He does not state which excel version......
[no name] 19-Dec-12 20:31pm
   
He indeed does not state the excel version, and the similar question of my version also does not state the excel version. it states SQL Server version, and the sql server version does not effect this solution,as it is database, it is suitable.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900