Import .csv file into oracle database.

my csv file contain a column which is having a date datatype and im getting an error which is row1 column 1 is invalid number.How to resolve this problem.The code is written in asp.net and c#

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlTypes;
using System.Globalization;
using System.IO;
//using System.Data.OracleClient;
using Oracle.DataAccess.Client;
public partial class Exp : System.Web.UI.Page
{
public OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["conguest"].ToString());
public OracleConnection conn1 = new OracleConnection(ConfigurationManager.ConnectionStrings["conguest1"].ToString());
private void InsertCSVRecords(DataTable dt)
{
conn1.Open();
OracleBulkCopy OracleBulkCopy = new OracleBulkCopy(conn1);
OracleBulkCopy.DestinationTableName = "guest.material_master";
//Response.Write("test2");
OracleBulkCopy.ColumnMappings.Add("REQ_NO", "REQ_NO");
OracleBulkCopy.ColumnMappings.Add("REQ_DT", "REQ_DT");
OracleBulkCopy.ColumnMappings.Add("REQ_BY", "REQ_BY");
OracleBulkCopy.ColumnMappings.Add("MAT_CD", "MAT_CD");
OracleBulkCopy.ColumnMappings.Add("MAT_DESC", "MAT_DESC");
OracleBulkCopy.ColumnMappings.Add("MAT_TYPE_CD", "MAT_TYPE_CD");
OracleBulkCopy.ColumnMappings.Add("BASE_UOM_CD", "BASE_UOM_CD");
OracleBulkCopy.ColumnMappings.Add("STOR_LOC_CD", "STOR_LOC_CD");
OracleBulkCopy.ColumnMappings.Add("PUR_GRP_CD", "PUR_GRP_CD");
OracleBulkCopy.ColumnMappings.Add("HSN_CD", "HSN_CD");
OracleBulkCopy.ColumnMappings.Add("MAT_LONG_DESC", "MAT_LONG_DESC");
OracleBulkCopy.ColumnMappings.Add("TAX_IND", "TAX_IND");
OracleBulkCopy.ColumnMappings.Add("ACTIVE_IND", "ACTIVE_IND");
OracleBulkCopy.ColumnMappings.Add("DEL_IND", "DEL_IND");
OracleBulkCopy.WriteToServer(dt);
conn1.Close();
}
protected void Button4_Click(object sender, EventArgs e)
{
//try
//{
string csvPath = Server.MapPath("~/Uploads/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(csvPath);
if ((FileUpload1.PostedFile != null) && (FileUpload1.PostedFile.ContentLength > 0))
{
string fn = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
string SaveLocation = Server.MapPath("~/Uploads") + "\\" + fn;
FileUpload1.PostedFile.SaveAs(SaveLocation);
Response.Write("The file has been uploaded.");
DataTable dt = new DataTable();
//DataSet ds = new DataSet();
dt.Columns.AddRange(new DataColumn[14] {
new DataColumn("REQ_NO", typeof(string)),
new DataColumn("REQ_DT", typeof(string)),
new DataColumn("REQ_BY",typeof(string)),
new DataColumn("MAT_CD", typeof(string)),
new DataColumn("MAT_DESC", typeof(string)),
new DataColumn("MAT_TYPE_CD", typeof(string)),
new DataColumn("BASE_UOM_CD", typeof(string)),
new DataColumn("STOR_LOC_CD", typeof(string)),
new DataColumn("PUR_GRP_CD", typeof(string)),
new DataColumn("HSN_CD", typeof(string)),
new DataColumn("MAT_LONG_DESC", typeof(string)),
new DataColumn("TAX_IND", typeof(string)),
new DataColumn("ACTIVE_IND", typeof(string)),
new DataColumn("DEL_IND", typeof(string))});
//DateTime dt1 = DateTime.Parse("dd-MMM-yyyy");
string csvData = File.ReadAllText(fn);
foreach (string row in csvData.Split('\r'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
//Response.Write("Test2");
InsertCSVRecords(dt);
}
else
{
Response.Write("Please select a file to upload.");
}
//}
//catch (Exception ex)
//{
// Response.Write(ex.Message);
//}
}
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
I am not an Oracle expert, however, would like to point out few things:

1/ Typically, under scenarios like this, it is a standard practise is to get data in to Oracle first, irrespective of the data type, that is "as is" and then migrate it from stagging table to destination table.

2/ With respect to dates, please ensure that dates are ALWAYS formatted as YYYY-MON-DD (YYYY-MM-DD) while communicating with computers. Computers hate all other formats because those are hopen to human interpretation. Always remember how date is stored in database is COMPLETELY different thing than how date is shown to end user.

Also, if time do exists, it should be 24Hrs format.