i have an excel sheet which have 1 lakh of record in it now i want to load this whole sheeet into postgres database it take lotup time approx 20 to 30 min
i want to reduce time to load into database how can i do this pls suggest me
code is this
public static DataTable GetDataTableExcel(string strFileName, string Table, string sheet, string expProp)
{
string date = DateTime.Now.ToString();
date = date.Replace("/", "").Replace(":", "").Replace(" ", "").ToString();
string values = string.Empty;
string Column = string.Empty;
string strFilename = System.IO.Path.GetFileNameWithoutExtension(strFileName);
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"" + expProp + ";HDR=Yes;IMEX=1\";");
conn.Open();
string strQuery = "SELECT * FROM [" + sheet + "]";
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
System.Data.DataTable ds = new System.Data.DataTable();
adapter.Fill(ds);
conn.Close();
string Columnname = string.Empty;
for (var i = 0; i < ds.Columns.Count; i++)
{
Columnname += ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty).Replace("?", string.Empty).Replace("@", string.Empty).Replace("&", string.Empty).Replace("$", string.Empty).Replace("%", string.Empty).Replace("!", string.Empty).Replace("/", string.Empty).Replace("-", string.Empty).Replace("~", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty) + " " + "character varying(255)" + ",";
}
Columnname = Columnname.TrimEnd(',');
Columnname = Columnname + "," + "pid character varying(255)";
string sql_Create = "CREATE TABLE " + Table + "(" + Columnname + ")";
obj.ExecuteNonQuery(sql_Create);
for (var j = 0; j < ds.Columns.Count; j++)
{
Column += ds.Columns[j].ToString().Replace(" ", string.Empty).Replace("@", string.Empty).Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("$", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim().Replace("/", string.Empty).Replace("-", string.Empty) + ",";
}
Column = Column.TrimEnd(',');
Column = Column + "," + "pid";
int counter = 0;
for (int k = 0; k < ds.Rows.Count; k++)
{
counter = counter + 1;
for (int i = 0; i < ds.Columns.Count; i++)
{
string val = ds.Rows[k][i].ToString();
if (val.Contains("'") || val.Contains(@"\"))
{
val = val.Replace("\"", "").Replace("'", "").Replace(@"\", "-").Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("$", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim();
}
values += "'" + val.Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("$", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim() +"'" + ",";
}
Column = Column.TrimEnd(',');
values = values.TrimEnd(',');
values = values + "," + counter;
obj.ExecuteNonQuery("insert into " + Table + "(" + Column + ") values(" + values + ")");
values = string.Empty;
}
return ds;
}