Click here to Skip to main content
15,881,812 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

I have to store some excel data to my database.
Excel file has been uploaded by user, then i want to convert that excel in C# datatable then using bulk insert query will update database.

I have code to read excel from one file path, But in my project, excel has been uploaded by user. I does't have any permission to store file in server.
http://www.c-sharpcorner.com/code/788/how-to-convert-excel-to-datatable-in-C-Sharp.aspx

so i want read the excel details without storing excel file any where?

help me to achieve this?
Posted
Comments
Maciej Los 7-Dec-15 2:38am    
You mean Excel file has been downloaded (not uploaded) by the user... So, this file is stored on local computer. You need to catch the path to the file and you'll be able to read from this file.
Richard MacCutchan 7-Dec-15 3:58am    
All you receive in the uploaded file is a stream of bytes, so you must store it somewhere in order to extract the data.
Baskar Gs 8-Dec-15 0:00am    
Yes, file is received stream of bytes. But i dont want save file in my server.

can i able extract the data from client uploaded path?

Richard MacCutchan 8-Dec-15 4:00am    
Only if you write a program that understands the structure of an Excel file and can extract the data from the stream. In reality, I would say this is a very difficult project.
Herman<T>.Instance 7-Dec-15 5:34am    
What type of database?

1 solution

using System.Data.OleDb;

C#
private void copy_excel(string FullPath)
        {

            //Excel command
            string cmdText = "SELECT [COL1], [COL2], [COL3]" +
               " FROM [TABNAME$]";

            //Excel connection string
            string excelConnString = "provider=microsoft.jet.oledb.4.0;" +
                " Data Source=C:\temp\20151207.XLS ;Extended Properties=" +
                "\"Excel 8.0;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";

            //Oracle Connection
            string oraConnString = Properties.Settings.Default.oraConn;
            OracleConnection oraConn = new OracleConnection(oraConnString);
            
            //OleDb Command using cmdText and Connection to read Excel
            OleDbConnection oleDbConn = new OleDbConnection(excelConnString);
            OleDbCommand oleDbCmd = new OleDbCommand(cmdText, oleDbConn);

            try
            {
                //open excel
                oleDbConn.Open();
                OleDbDataReader dr = oleDbCmd.ExecuteReader();
                DataTable dt = new DataTable();

                dt.Load(dr); //Load the excel we want to write to Oracle
                dr.Close();

                //Oracle Connection

                //instanciate cmd
                OracleCommand oraCmd = new OracleCommand();

                //Delete cmd
                oraCmd.CommandText = "DELETE FROM raw_data.excel_import";
                oraCmd.CommandType = CommandType.Text;
                oraCmd.Connection = oraConn;
                
	//Execute delete
                oraConn.Open();
                oraCmd.ExecuteNonQuery();      

                //BulkCopy data into Oracle
                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(oraConn))
                {
                    bulkCopy.DestinationTableName = "raw_data.excel_import";
                    bulkCopy.WriteToServer(dt);
                }
            }
            catch (Exception ex)
            {
                //Write output file of failure
                string[] errOut = { "Error durring import of file " + FullPath, "Exception occured in " + ex.Source, "Exception: " + ex.Message };
                System.IO.File.WriteAllLines(FullPath.Replace(".xls", "Error.txt"), errOut);
            }
            finally
            {
                if (oraConn.State == ConnectionState.Open)
                {
                    oraConn.Close();
                }
                if (oleDbConn.State == ConnectionState.Open)
                {
                    oleDbConn.Close(); //Close excel connection
                }
                oraConn.Dispose();
                oleDbCmd.Dispose();
                oleDbConn.Dispose();        
            }
        }
 
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