Comments by Member 10868354 (Top 2 by date)

I want to insert .csv data into database using C# , include header and left side row number. Now I Do like this see below my coding . I'm expecting Your Reply


protected void btCSVUpload_Click(object sender, EventArgs e)
{



if (FileUploadControl.HasFile)
{
FileInfo fileInfo = new FileInfo(FileUploadControl.PostedFile.FileName);
if (fileInfo.Name.Contains(".csv"))
{

string fileName = fileInfo.Name.Replace(".csv", "").ToString();
string csvFilePath = Server.MapPath("UploadedCSVFiles") + "\\" + fileInfo.Name;

//Save the CSV file in the Server inside 'MyCSVFolder'
FileUploadControl.SaveAs(csvFilePath);

//Fetch the location of CSV file
string filePath = Server.MapPath("UploadedCSVFiles") + "\\";
string strSql = "SELECT * FROM [" + fileInfo.Name + "]";
string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='text;HDR=YES;'";

// load the data from CSV to DataTable

OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, strCSVConnString);
DataTable dtCSV = new DataTable();
DataTable dtSchema = new DataTable();
adapter.FillSchema(dtCSV, SchemaType.Mapped);
adapter.Fill(dtCSV);
if (dtCSV.Rows.Count > 0)
{

//CreateDatabaseTable(dtCSV, fileName);
//Label2.Text = string.Format("The table ({0}) has been successfully created to the database.", fileName);
string fileFullPath = filePath + fileInfo.Name;
LoadDataToDatabase(fileName, fileFullPath, ",");
if (DuplicateCnt == 0)
{
Label1.Text = string.Format("({0}) records has been loaded to the table {1}.", dtCSV.Rows.Count, fileName);
}
}
else
{
lbStatus.Text = "File is empty.";
}
}
else
{
lbStatus.Text = "Unable to recognize file.";
}
}
}




private void LoadDataToDatabase(string tableName, string fileFullPath, string delimeter)
{
string sqlQuery = string.Empty;
StringBuilder sb = new StringBuilder();
//sb.AppendFormat(string.Format("BULK INSERT {0} ", tableName));
sb.AppendFormat(string.Format("BULK INSERT HdbRpt"));
sb.AppendFormat(string.Format(" FROM '{0}'", fileFullPath));
sb.AppendFormat(string.Format(" WITH ( FIELDTERMINATOR = '{0}' , ROWTERMINATOR = '\n' )", delimeter));

sqlQuery = sb.ToString();

using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
{
try
{
DuplicateCnt = 0;
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn);
sqlCmd.ExecuteNonQuery();
sqlConn.Close();
}
catch { DuplicateCnt = 1; ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('This Data is are alreaddy inserted')", true); }
}
}