Uploading an Excel sheet and importing the data into SQL Server database

Question

I am developing this simple application to upload an Excel file (.xlsx) and import the data present in that Excel worksheet into a SQL Server Express database in .NET

I'm using the following code on click of the import button after browsing and selecting the file to do it.

protected void Button1_Click(object sender, EventArgs e)
{
        String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Users\\Hemant\\documents\\visual studio 2010\\Projects\\CRMdata\\CRMdata\\App_Data\\Database1.mdf';Integrated Security=True;User Instance=True";
        //file upload path
        string path = FileUpload1.PostedFile.FileName;
        //string path="C:\\ Users\\ Hemant\\Documents\\example.xlsx";
        //Create connection string to Excel work book
        string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        //Create OleDbCommand to fetch data from Excel
        OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection);
        excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
        //Give your Destination table name
        sqlBulk.DestinationTableName = "Excel_table";
        sqlBulk.WriteToServer(dReader);
        excelConnection.Close();
    }

But the code doesn't run when I use

string path = FileUpload1.PostedFile.FileName;`

and even

string path="C:\ Users\ Hemant\Documents\example.xlsx";` 

The dReader is unable to take the path in this format.

It is only able to take path in the following format

string path="C:\\ Users\\ Hemant\\Documents\\example.xlsx";

i.e. with the the \\ in the path.For which I have to hard code the path but we have to browse the file.

So,can any one please suggest a solution to use the path taken by the FileUpload1 to import the data?

Answer 1

You are dealing with a HttpPostedFile; this is the file that is "uploaded" to the web server. You really need to save that file somewhere and then use it, because...

...in your instance, it just so happens to be that you are hosting your website on the same machine the file resides, so the path is accessible. As soon as you deploy your site to a different machine, your code isn't going to work.

Break this down into two steps:

1) Save the file somewhere - it's very common to see this:

string saveFolder = @"C:\temp\uploads"; //Pick a folder on your machine to store the uploaded files

string filePath = Path.Combine(saveFolder, FileUpload1.FileName); 

FileUpload1.SaveAs(filePath);

Now you have your file locally and the real work can be done.

2) Get the data from the file. Your code should work as is but you can simply write your connection string this way:

string excelConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 12.0";", filePath);

You can then think about deleting the file you've just uploaded and imported.

To provide a more concrete example, we can refactor your code into two methods:

    private void SaveFileToDatabase(string filePath)
    {
        String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Users\\Hemant\\documents\\visual studio 2010\\Projects\\CRMdata\\CRMdata\\App_Data\\Database1.mdf';Integrated Security=True;User Instance=True";

        String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
        //Create Connection to Excel work book 
        using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
        {
            //Create OleDbCommand to fetch data from Excel 
            using (OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection))
            {
                excelConnection.Open();
                using (OleDbDataReader dReader = cmd.ExecuteReader())
                {
                    using(SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                    {
                        //Give your Destination table name 
                        sqlBulk.DestinationTableName = "Excel_table";
                        sqlBulk.WriteToServer(dReader);
                    }
                }
            }
        } 
    }


    private string GetLocalFilePath(string saveDirectory, FileUpload fileUploadControl)
    {


        string filePath = Path.Combine(saveDirectory, fileUploadControl.FileName);

        fileUploadControl.SaveAs(filePath);

        return filePath;

    }

You could simply then call SaveFileToDatabase(GetLocalFilePath(@"C:\temp\uploads", FileUpload1));

Consider reviewing the other Extended Properties for your Excel connection string. They come in useful!

Other improvements you might want to make include putting your Sql Database connection string into config, and adding proper exception handling. Please consider this example for demonstration only!