Import Excel Table from a FileUpload Control to SQL Database. : The Official Microsoft ASP.NET Forums

Reply

Import Excel Table from a FileUpload Control to SQL Database.

Feb 09, 2012 11:15 AM|LINK

Dear All,

Help needed for my below query:

I Have Fileuploader through which i need to import Excel table into Sql Datatable.

My Excel Fields are:

SLNo|CourseCode|Title|Type

Thanks,

Surendra

Reply

Re: Import Excel Table from a FileUpload Control to SQL Database.

Feb 09, 2012 11:22 AM|LINK

http://dotnetsquare.com/resources/44-import-data-from-excel-to-sql-server-using-Asp-Net

http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx

hope this helps...

Cheers!
KK

Please mark as Answer if post helps in resolving your issue
My Site

Reply

Re: Import Excel Table from a FileUpload Control to SQL Database.

Feb 11, 2012 12:15 AM|LINK

sksurendra
I Have Fileuploader through which i need to import Excel table into Sql Datatable.

Hello：）

1）Uploading your file into the Server side first by using：FileUpload.SaveAs(Server.MapPath(……))。

2）Then you should use OledbConnection+OleDbCommand to deal with the problem——

/ Connection String to Excel Workbook string excelConnectionString = @"Provider=Microsoft
 .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
 Properties=""Excel 8.0;HDR=YES;""";

// Create Connection to Excel Workbook using (OleDbConnection connection =
             new OleDbConnection(excelConnectionString))
{
    OleDbCommand command = new OleDbCommand
            ("Select ID,Data FROM [Data$]", connection);

    connection.Open();
    
    // Create DbDataReader to Data Worksheet     using (DbDataReader dr = command.ExecuteReader())
    {
        // SQL Server Connection String         string sqlConnectionString = "Data Source=.;
 Initial Catalog=Test;Integrated Security=True";

        // Bulk Copy to SQL Server         using (SqlBulkCopy bulkCopy =
                   new SqlBulkCopy(sqlConnectionString))
        {
            bulkCopy.DestinationTableName = "ExcelData";
            bulkCopy.WriteToServer(dr);
        }
    }
}

From：http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

Reply

Re: Import Excel Table from a FileUpload Control to SQL Database.

Feb 11, 2012 05:37 PM|LINK

Hi Thanks for ur code.

I'm getting this error

"The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data."

public partial class newpage : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        // Connection String to Excel Workbook
        string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls; Extended Properties=""Excel 8.0;HDR=YES;""";

        // Create Connection to Excel Workbook
        using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
        {
            OleDbCommand command = new OleDbCommand("Select CourseCode, FileName, Title, MediaType, Duration, Vendor, FROM [Sheet1$]", connection);

            connection.Open();

            // Create DbDataReader to Data Worksheet
            using (DbDataReader dr = command.ExecuteReader())
            {
                // SQL Server Connection String
                string constrng = WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
                SqlConnection con = new SqlConnection(constrng);

                // Bulk Copy to SQL Server
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
                {
                    bulkCopy.DestinationTableName = "Catalogue";
                    bulkCopy.WriteToServer(dr);
                }
            }
        }
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            // GetExcelSheets(FilePath, Extension, "Yes");
        }


    }
}

Reply

Re: Import Excel Table from a FileUpload Control to SQL Database.

Feb 11, 2012 05:39 PM|LINK

Hi Thanks for your response.

Reply

Re: Import Excel Table from a FileUpload Control to SQL Database.

Feb 12, 2012 12:34 AM|LINK

sksurendra
"The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data."

Please make sure that your AccessDB isn't used by any other threads or progresses……；and don't forget to call connection.Close() when you don't want to use that connection……Though using statement will automatically close the connection。