Home » Asp.netRSS

Insert Excel Data via File Upload into sql server database???

Hi all,

I have requirement that User can Upload the Excel Sheet Data to sql server Database at once.

How i do that Any Article or hint is apperciated.

 

Regards 

 

16 Answers Found

 

Answer 1

sample code will be like this

 string strQuery = string.Empty;
            string strExcelCon = ConfigurationSettings.AppSettings["ExcelSource"] + FileName;
            SqlConnection sqlcon = new SqlConnection(ConfigurationSettings.AppSettings["sqlCon"]);
            OleDbConnection excelCon = new OleDbConnection(strExcelCon);
            DataTable dtExcelData = new DataTable();
            bool bColumnExists = true;
            try
            {
                ExcelSheets objExcel = new ExcelSheets();
                string[] SheetNames = objExcel.GetExcelSheetNames(FileName);                
                excelCon.Open();
                strQuery = "SELECT * FROM [" + SheetNames[0].Replace("'", "") + "]";

                OleDbDataAdapter oleDA = new OleDbDataAdapter(strQuery, excelCon);
                oleDA.Fill(dtExcelData);

                                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
                {
                    sqlcon.Open();
                    bulkCopy.DestinationTableName = TableName;

                    for (int iIndex = 0; iIndex < arrExcelSheetColumns.Length; iIndex++)
                    {
                        bulkCopy.ColumnMappings.Add(arrExcelSheetColumns[iIndex].ToString(), arrDatabaseColumns[iIndex].ToString());
                    }

                    bulkCopy.WriteToServer(dtExcelData);
                }

                if (excelCon.State == ConnectionState.Open)
                {
                    excelCon.Close();
                }
              
            }
            catch (Exception ex)
            {
                if (excelCon.State == ConnectionState.Open)
                {
                    excelCon.Close();
                }
                throw ex;
            }
            finally
            {
                if (excelCon.State == ConnectionState.Open)
                {
                    excelCon.Close();
                }
            }


 

public String[] GetExcelSheetNames(string excelFile)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;

            try
            {
                // Connection String. Change the excel  file to the file  you
                // will search.
                String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
                // Create connection object by using the preceding connection string.
                objConn = new OleDbConnection(connString);
                // Open connection with the database.
                objConn.Open();
                // Get the data  table containg the schema guid.
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return null;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet  name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                    
                }

                // Loop through all of the sheets if you want too...
                for (int j = 0; j < excelSheets.Length; j++)
                {
                    // Query each excel sheet.
                }

                return excelSheets;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Clean up.
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }


 

 

Answer 2

Thank you very much for Reply, let me study your code and Back to you it would be better to give some Theory Explanation that how to read data  from Excel file and store it into Database Table .

 

Any way Thanks for your support.

 

Regards 

 

Answer 3

Approach is

1. Load it into a dataset.

2. insert that dataset into database  through sqlbulkcopy.

Now coming to loading into dataset from excel, we use oledbadapter(for excel). Oledbadapter has a fill() method which returns the dataset with the contents of the excel  file. Before that you need to configure the adapter's connection (oledbconnection) details(where is the file  and what is the type). You can see the setting of connection string. (Connection string you can see it in my second function).

Then In next step, we are using sqlbulkcopy class to import to database. first we will specify the columns mapping(which columns in excel needs to insert  into which columns in table) .

 

Answer 4

 Thank You vary much for your reply, atleast i got the idea ...

 

But in my mind there is an other approach suggest me which one would be the best

First Create the Excel File with appropriate Columns Names and Data into them.

Second Upload the Excel File via FileUpload Control to one Temp Folder in root Directory.

Third Make  the Connection string and connect with Excel Doc and using BulkCopy() Load the Data into Sql Server table.

In the last after inserting the Data Delete the Excel File from Temp Folder.

 

Now suggest me the which one approach would be the best yours, or above????

again thanks for your support .

 

Regards

 

 

 

 

Answer 5

SQL server  – Import CSV file  Into sql  Server Using Bulk Insert

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

 

Answer 6

The difference between yoru approach and my approach is first 2 steps.

my assumption is you have an excel  file. just u wants to upload  it..

if u dont have excel file,if users will provide  it, yours is the approach... it depends on your requirements.

 

 

Answer 7

Thank you very much for continueous help

 

Surely will mark as answer

 

Regards. 

 

Answer 8

Dear, ramireddyindia 

There is last issue iam facing issue is that

the first row of the excel  2007 file  is being skipped when using sql  bulk copy method. any suggestions?

i mean 1 row of the excel sheet  is the Name of column and second row contains the Data but when iam going to insert  the Data 2nd row is being skipped why???

 

Regards

 

Answer 9

you might need to set the Header as NO in the connection string.  can you try that...

Extended Properties="Excel 12.0;HDR=NO";

 

Answer 10

ramireddyindia:

you might need to set the Header as NO in the connection string.  can you try that...

Extended Properties="Excel 12.0;HDR=NO";

 

Yes i tried the HDR = NO but still the second Row is Skipped.

Look here is the my Excel Sheet Strucure

1. CoulmnName , SecondColumnName

2. XYZ                          12

3. Abc                           04

4. ZES                           10

 

Now only Two Record  3 and 4 are inserting into Table but 2 Record is skipping i dont know why

Is there any thing iam doing wrong???

Regards.    

 

Answer 11

Not sure, how it is missing second row? Is it able to detect the Header column names??

try the connection string with like below pattern

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

 

Answer 12

ramireddyindia:

Not sure, how it is missing second row? Is it able to detect the Header column names??

try the connection string with like below pattern

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

 

Below is my ConnectionString

		<add key="Excel2007OleDBConnection" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1""></add>


 

and about your second question Is it able to detect the Header column names??

How i detect the Header Column Names ???

Thanks

 

 

Answer 13

hi i am using the Bulkinsert method to save Excelfile is Sqlserverdatabas ,

i am getting strange problem , code is working well on localhost and inserting data  in Sqlserver database  , returning rows "2 rows in gooddatatTable and 2 in BaddataTable" 


but,when it deploy on dedicated server  then ,its displaying errorMsg "Select Excel file"(i allow .xls and xlsx file) and ,allow to upload  file, and insert  no record in database returning message "0 rows in gooddatatTable and 0 in BaddataTable"


help,

 

Answer 14

Hi

There might be issue with setting the Path check the File Path where you are uploading the Excell Sheet and proper folder permissions.

 

Let me know if you need any help i have done this before.

 

Best Regards

 

Answer 15

Hi,

    When I use your code to integrate with my application. It shows error for object ExcelSheets. Do I need to install third party component before I use

this excel  object? cos I need this to detect the excel sheet  name for query purpose.

Please reply. Thanks.

 

Answer 16

 Hi,

Refer the below links

http://csharpdotnetfreak.blogspot.com/2009/04/export-excel-data-sqlbulkcopy-aspnet.html

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

 

OR

Post your code here as we can analyze where is the bug which version of Excell you are using????

 

Best Regards

Rameez

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter