using System.Data.OleDb;
private void copy_excel(string FullPath)
{
string cmdText = "SELECT [COL1], [COL2], [COL3]" +
" FROM [TABNAME$]";
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\"";
string oraConnString = Properties.Settings.Default.oraConn;
OracleConnection oraConn = new OracleConnection(oraConnString);
OleDbConnection oleDbConn = new OleDbConnection(excelConnString);
OleDbCommand oleDbCmd = new OleDbCommand(cmdText, oleDbConn);
try
{
oleDbConn.Open();
OleDbDataReader dr = oleDbCmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dr.Close();
OracleCommand oraCmd = new OracleCommand();
oraCmd.CommandText = "DELETE FROM raw_data.excel_import";
oraCmd.CommandType = CommandType.Text;
oraCmd.Connection = oraConn;
oraConn.Open();
oraCmd.ExecuteNonQuery();
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(oraConn))
{
bulkCopy.DestinationTableName = "raw_data.excel_import";
bulkCopy.WriteToServer(dt);
}
}
catch (Exception ex)
{
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();
}
oraConn.Dispose();
oleDbCmd.Dispose();
oleDbConn.Dispose();
}
}