Click here to Skip to main content
15,887,952 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hello,
I want to get data from Excel sheet and insert into my database table.
Is there any best option to do this.
I am use ajax call to upload the file. and save data using web service.

What I have tried:

I want to export excel sheet data to ms sql database..
Posted
Updated 24-Jan-17 1:45am
Comments
Member 12882545 16-Jan-17 19:58pm    
why you don't try use Extract,Load,Transform (ETL) process?

you can do this by Import and Export Wizard: Know How to Import Data From Excel Spreadsheet to SQL Server[^]
 
Share this answer
 
Make a linked server from your sql server to excel and data will come in sql server using open row set command.
 
Share this answer
 
hi, I am convert my datatable in CSV format but I cant able to download the csv file,
please see below my code.


DataTable dt=new DataTable();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
con.Open();
SqlDataAdapter da = new SqlDataAdapter("getContacts", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@userAutoId", userAutoId);
//cmd.Parameters.Add("@retValue", System.Data.SqlDbType.VarChar).Direction = System.Data.ParameterDirection.ReturnValue;
da.Fill(dt);

//DataTable dt = GetData(cmd);

Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition","attachment;filename=DataTable.csv");
Response.Charset = "";
Response.ContentType = "csv/text";


StringBuilder sb = new StringBuilder();
for (int k = 0; k < dt.Columns.Count; k++)
{
    //add separator
    sb.Append(dt.Columns[k].ColumnName + ',');
}
//append new line
sb.Append("\r\n");
for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int k = 0; k < dt.Columns.Count; k++)
    {
        //add separator
        sb.Append(dt.Rows[i][k].ToString().Replace(",", ";") + ',');
    }
    //append new line
    sb.Append("\r\n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
 
Share this answer
 

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