Transfer excel data to oracle
-
Hi, There is one utility called SQLBULKCOPY but that is for sqlserver. In asp.net 2.0, there is no as such utility for oracle. My need is to transfer excel data to oracle? Provide any solution.
OLEDB
-
OLEDB
I have opned an oledb connection to connect to oracle. I am working in offline mode. I fill up the dataset with excel data. Works fine. Now I open oracle connection with oledb only. Created oracle adpater also using oledbadapter. Created the dataset. it contains no rows as table in oracle is empty. Check the code below: string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\ASP1\\Book1.xlsx;Extended Properties=Excel 12.0"; OleDbConnection oledbConn = new OleDbConnection(connString); OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn); OleDbDataAdapter oleda = new OleDbDataAdapter(); oleda.SelectCommand = cmd; DataSet ds = new DataSet(); oleda.Fill(ds, "Sheet1"); OleDbConnection oledbConn1 = new OleDbConnection("Provider=MSDAORA;Data Source=mywork; User Id = system; Password =Infosys123"); OleDbCommand cmd1 = new OleDbCommand("SELECT * FROM excel_ora", oledbConn1); OleDbDataAdapter oledb = new OleDbDataAdapter(cmd1); DataSet ds1 = new DataSet(); oledb.Fill(ds1, "excel_ora"); ds1 = ds.Copy(); OleDbCommandBuilder oo = new OleDbCommandBuilder(oledb); oledb.Update(ds1,"excel_ora"); //the above line thorws error as it does not find table "excel_ora" Response.Write("hi");
-
I have opned an oledb connection to connect to oracle. I am working in offline mode. I fill up the dataset with excel data. Works fine. Now I open oracle connection with oledb only. Created oracle adpater also using oledbadapter. Created the dataset. it contains no rows as table in oracle is empty. Check the code below: string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\ASP1\\Book1.xlsx;Extended Properties=Excel 12.0"; OleDbConnection oledbConn = new OleDbConnection(connString); OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn); OleDbDataAdapter oleda = new OleDbDataAdapter(); oleda.SelectCommand = cmd; DataSet ds = new DataSet(); oleda.Fill(ds, "Sheet1"); OleDbConnection oledbConn1 = new OleDbConnection("Provider=MSDAORA;Data Source=mywork; User Id = system; Password =Infosys123"); OleDbCommand cmd1 = new OleDbCommand("SELECT * FROM excel_ora", oledbConn1); OleDbDataAdapter oledb = new OleDbDataAdapter(cmd1); DataSet ds1 = new DataSet(); oledb.Fill(ds1, "excel_ora"); ds1 = ds.Copy(); OleDbCommandBuilder oo = new OleDbCommandBuilder(oledb); oledb.Update(ds1,"excel_ora"); //the above line thorws error as it does not find table "excel_ora" Response.Write("hi");
//Code related to excel string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\ASP1\\Book1.xlsx;Extended Properties=Excel 12.0"; OleDbConnection oledbConn = new OleDbConnection(connString); OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn); OleDbDataAdapter oleda = new OleDbDataAdapter(); oleda.SelectCommand = cmd; DataSet ds = new DataSet(); oleda.Fill(ds, "excel_ora"); //Code related to Oracle OleDbConnection oledbConn1 = new OleDbConnection("Provider=MSDAORA;Data Source=mywork; User Id = system; Password =Infosys123"); OleDbCommand cmd1 = new OleDbCommand("SELECT * FROM excel_ora", oledbConn1); OleDbDataAdapter oledb = new OleDbDataAdapter(cmd1); DataSet ds1 = new DataSet(); oledb.Fill(ds1, "excel_ora"); //Transfer excel dataset to oracle ds1 = ds.Copy(); OleDbCommandBuilder oo = new OleDbCommandBuilder(oledb); oledb.Update(ds1,"excel_ora"); Response.Write("hi"); ----------------------------------------------- Written aboce code. everything fine but data does not transfer to oracle. WHy?
-
Hi, There is one utility called SQLBULKCOPY but that is for sqlserver. In asp.net 2.0, there is no as such utility for oracle. My need is to transfer excel data to oracle? Provide any solution.
Use Oracle Data Provider for .NET[^] to connect with Oracle from .NET. Also refer this link: http://download.oracle.com/docs/html/E10927_01/OracleBulkCopyClass.htm[^]
-
Use Oracle Data Provider for .NET[^] to connect with Oracle from .NET. Also refer this link: http://download.oracle.com/docs/html/E10927_01/OracleBulkCopyClass.htm[^]