Below is the code to upload csv files to oracle DB in c#
protected void Import_Click(object sender, EventArgs e)
{
try
{
int cnt = 0;
DataTable dt = new DataTable();
string fileName = fu.FileName;
string FileExtension = fileName.Substring(fileName.LastIndexOf('.') + 1).ToLower();
if (FileExtension == "csv" || FileExtension == "CSV")
{
// Load CSV File to Datatable
dt = ReadCsvFile();
if (dt.Rows.Count > 0)
{
// Remove all the rows from the tables
DSPInventory.Core.DSP_PAGER_Week_Methods.DeleteAllRows();
for (int i = 0; dt.Rows.Count > i; i++)
{
string Week_from = dt.Rows[i].ItemArray.GetValue(0).ToString();
string Week_to = dt.Rows[i].ItemArray.GetValue(1).ToString();
string US_Day_primary = dt.Rows[i].ItemArray.GetValue(2).ToString();
string US_Day_Pri_ADID = dt.Rows[i].ItemArray.GetValue(3).ToString();
string US_Day_pri_Email = dt.Rows[i].ItemArray.GetValue(4).ToString();
string US_Day_pri_Phone = dt.Rows[i].ItemArray.GetValue(5).ToString();
string US_Day_Pager_pri_id = dt.Rows[i].ItemArray.GetValue(6).ToString();
string US_Day_secondary = dt.Rows[i].ItemArray.GetValue(7).ToString();
string US_Day_sec_ADID = dt.Rows[i].ItemArray.GetValue(8).ToString();
string US_Day_sec_Email = dt.Rows[i].ItemArray.GetValue(9).ToString();
string US_Day_sec_Phone = dt.Rows[i].ItemArray.GetValue(10).ToString();
string US_Day_sec_pri_id = dt.Rows[i].ItemArray.GetValue(11).ToString();
string INDIA_Day_primary = dt.Rows[i].ItemArray.GetValue(12).ToString();
string INDIA_Day_Pri_ADID = dt.Rows[i].ItemArray.GetValue(13).ToString();
string INDIA_Day_pri_Email = dt.Rows[i].ItemArray.GetValue(14).ToString();
string INDIA_Day_pri_Phone = dt.Rows[i].ItemArray.GetValue(15).ToString();
string INDIA_Day_Pager_pri_id = dt.Rows[i].ItemArray.GetValue(16).ToString();
string INDIA_Day_secondary = dt.Rows[i].ItemArray.GetValue(17).ToString();
string INDIA_Day_sec_ADID = dt.Rows[i].ItemArray.GetValue(18).ToString();
string INDIA_Day_sec_Email = dt.Rows[i].ItemArray.GetValue(19).ToString();
string INDIA_Day_sec_Phone = dt.Rows[i].ItemArray.GetValue(20).ToString();
string INDIA_Day_sec_pri_id = dt.Rows[i].ItemArray.GetValue(21).ToString();
// Insert into table
}
}
if (cnt > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
Label1.Text = "Below Grid Data Successfully Uploaded to Weekly Pager Table!!";
}
}
else
{
Label1.Text = "Only CSV Files Allowed!!";
}
}
catch (Exception ex)
{
Label1.Text = ex.Message.ToString();
}
}
/// <summary>
/// Load csv file to DataTable
/// </summary>
/// <returns></returns>
public DataTable ReadCsvFile()
{
DataTable dtCsv = new DataTable();
string Fulltext;
if (fu.HasFile)
{
using (StreamReader sr = new StreamReader(fu.PostedFile.InputStream))
{
while (!sr.EndOfStream)
{
Fulltext = sr.ReadToEnd().ToString();//read full file text
string[] rows = Fulltext.Split('\n');//split full file text into rows
for (int i = 0; i < rows.Count() - 1; i++)
{
string[] rowValues = rows[i].Split(',');//split each row with comma to get individual values
{
if (i == 0)
{
for (int j = 0; j < rowValues.Count(); j++)
{
dtCsv.Columns.Add(rowValues[j]);//add headers
}
}
else
{
DataRow dr = dtCsv.NewRow();
for (int k = 0; k < rowValues.Count(); k++)
{
dr[k] = rowValues[k].ToString();
}
dtCsv.Rows.Add(dr);//add other rows
}
}
}
}
}
}
return dtCsv;
}
protected void Import_Click(object sender, EventArgs e)
{
try
{
int cnt = 0;
DataTable dt = new DataTable();
string fileName = fu.FileName;
string FileExtension = fileName.Substring(fileName.LastIndexOf('.') + 1).ToLower();
if (FileExtension == "csv" || FileExtension == "CSV")
{
// Load CSV File to Datatable
dt = ReadCsvFile();
if (dt.Rows.Count > 0)
{
// Remove all the rows from the tables
DSPInventory.Core.DSP_PAGER_Week_Methods.DeleteAllRows();
for (int i = 0; dt.Rows.Count > i; i++)
{
string Week_from = dt.Rows[i].ItemArray.GetValue(0).ToString();
string Week_to = dt.Rows[i].ItemArray.GetValue(1).ToString();
string US_Day_primary = dt.Rows[i].ItemArray.GetValue(2).ToString();
string US_Day_Pri_ADID = dt.Rows[i].ItemArray.GetValue(3).ToString();
string US_Day_pri_Email = dt.Rows[i].ItemArray.GetValue(4).ToString();
string US_Day_pri_Phone = dt.Rows[i].ItemArray.GetValue(5).ToString();
string US_Day_Pager_pri_id = dt.Rows[i].ItemArray.GetValue(6).ToString();
string US_Day_secondary = dt.Rows[i].ItemArray.GetValue(7).ToString();
string US_Day_sec_ADID = dt.Rows[i].ItemArray.GetValue(8).ToString();
string US_Day_sec_Email = dt.Rows[i].ItemArray.GetValue(9).ToString();
string US_Day_sec_Phone = dt.Rows[i].ItemArray.GetValue(10).ToString();
string US_Day_sec_pri_id = dt.Rows[i].ItemArray.GetValue(11).ToString();
string INDIA_Day_primary = dt.Rows[i].ItemArray.GetValue(12).ToString();
string INDIA_Day_Pri_ADID = dt.Rows[i].ItemArray.GetValue(13).ToString();
string INDIA_Day_pri_Email = dt.Rows[i].ItemArray.GetValue(14).ToString();
string INDIA_Day_pri_Phone = dt.Rows[i].ItemArray.GetValue(15).ToString();
string INDIA_Day_Pager_pri_id = dt.Rows[i].ItemArray.GetValue(16).ToString();
string INDIA_Day_secondary = dt.Rows[i].ItemArray.GetValue(17).ToString();
string INDIA_Day_sec_ADID = dt.Rows[i].ItemArray.GetValue(18).ToString();
string INDIA_Day_sec_Email = dt.Rows[i].ItemArray.GetValue(19).ToString();
string INDIA_Day_sec_Phone = dt.Rows[i].ItemArray.GetValue(20).ToString();
string INDIA_Day_sec_pri_id = dt.Rows[i].ItemArray.GetValue(21).ToString();
// Insert into table
}
}
if (cnt > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
Label1.Text = "Below Grid Data Successfully Uploaded to Weekly Pager Table!!";
}
}
else
{
Label1.Text = "Only CSV Files Allowed!!";
}
}
catch (Exception ex)
{
Label1.Text = ex.Message.ToString();
}
}
/// <summary>
/// Load csv file to DataTable
/// </summary>
/// <returns></returns>
public DataTable ReadCsvFile()
{
DataTable dtCsv = new DataTable();
string Fulltext;
if (fu.HasFile)
{
using (StreamReader sr = new StreamReader(fu.PostedFile.InputStream))
{
while (!sr.EndOfStream)
{
Fulltext = sr.ReadToEnd().ToString();//read full file text
string[] rows = Fulltext.Split('\n');//split full file text into rows
for (int i = 0; i < rows.Count() - 1; i++)
{
string[] rowValues = rows[i].Split(',');//split each row with comma to get individual values
{
if (i == 0)
{
for (int j = 0; j < rowValues.Count(); j++)
{
dtCsv.Columns.Add(rowValues[j]);//add headers
}
}
else
{
DataRow dr = dtCsv.NewRow();
for (int k = 0; k < rowValues.Count(); k++)
{
dr[k] = rowValues[k].ToString();
}
dtCsv.Rows.Add(dr);//add other rows
}
}
}
}
}
}
return dtCsv;
}