Whenever I upload the file from excel and export into SQL, I get multiple empty lines( )
Below is my codeprotected void btnUpload_Click(object sender, EventArgs e)
{
//Coneection String by default empty
string ConStr = "";
//Extantion of the file upload control saving into ext because
//there are two types of extation .xls and .xlsx of excel
string ext = Path.GetExtension(FileUpload1.FileName).ToLower();
//getting the path of the file
string path = Server.MapPath("~/MyFolder/" + FileUpload1.FileName);
//saving the file inside the MyFolder of the server
FileUpload1.SaveAs(path);
Label1.Text = FileUpload1.FileName + "\'s Please check the below detail again and Click Submit";
//checking that extantion is .xls or .xlsx
if (ext.Trim() == ".xls")
{
//connection string for that file which extantion is .xls
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (ext.Trim() == ".xlsx")
{
//connection string for that file which extantion is .xlsx
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//making query
string query = "SELECT * FROM [Sheet1$]";
//Providing connection
OleDbConnection conn = new OleDbConnection(ConStr);
//checking that connection state is closed or not if closed the
//open the connection
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//create command object
OleDbCommand cmd = new OleDbCommand(query, conn);
// create a data adapter and get the data into dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the excel data to data set
da.Fill(ds);
//set data source of the grid view
gvExcelFile.DataSource = ds.Tables[0];
//binding the gridview
gvExcelFile.DataBind();
//close the connection
conn.Close();
}
protected void btnDownload_Click(object sender, EventArgs e)
{
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", "attachment; filename=TemplateforUpload.xlsx");
Response.TransmitFile(Server.MapPath("~/MyFolder/TemplateforUpload.xlsx"));
Response.End();
}
protected void Submit_Click(object sender, EventArgs e)
{
ExportRowsToDB();
Label2.Text = "Successfully Updated";
Response.Redirect("Thankyou.aspx");
}
private void ExportRowsToDB()
{
//container for source data
DataTable dtProducts = new DataTable("ParcelInf");
//dtProducts.Columns.Add("RequestID");
dtProducts.Columns.Add("SubdivisionNo");
dtProducts.Columns.Add("ParcelNo");
dtProducts.Columns.Add("Region");
dtProducts.Columns.Add("City");
dtProducts.Columns.Add("Zone");
dtProducts.Columns.Add("CoordinateSystem");
//dtProducts.Columns.Add("CUID");
//dtProducts.Columns.Add("Status");
//dtProducts.Columns.Add("Remarks");
DataRow dr = null;
//iterate through each grid and populate source data
foreach (GridViewRow gridRow in gvExcelFile.Rows)
{
dr = dtProducts.NewRow();
//dr["RequestID"] = (gridRow.Cells[1].Text);
dr["SubdivisionNo"] = gridRow.Cells[1].Text;
dr["ParcelNo"] = gridRow.Cells[2].Text;
dr["Region"] = gridRow.Cells[3].Text;
dr["City"] = gridRow.Cells[4].Text;
dr["Zone"] = (gridRow.Cells[5].Text);
dr["CoordinateSystem"] = gridRow.Cells[6].Text;
//dr["CUID"] = gridRow.Cells[8].Text;
//dr["Status"] = gridRow.Cells[9].Text;
//dr["Remarks"] = gridRow.Cells[10].Text;
dtProducts.Rows.Add(dr);
}
//insert data into destination table
CopyData(dtProducts);
}
public void CopyData(DataTable sourceData)
{
string destConnString =
ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
// Set up the bulk copy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destConnString))
{
bulkCopy.DestinationTableName = "dbo.ParcelInf";
// Guarantee that columns are mapped correctly by
// defining the column mappings for the order.
//bulkCopy.ColumnMappings.Add("RequestID", "RequestID");
bulkCopy.ColumnMappings.Add("SubdivisionNo", "SubdivisionNo");
bulkCopy.ColumnMappings.Add("ParcelNo", "ParcelNo");
bulkCopy.ColumnMappings.Add("Region", "Region");
bulkCopy.ColumnMappings.Add("City", "City");
bulkCopy.ColumnMappings.Add("Zone", "Zone");
bulkCopy.ColumnMappings.Add("CoordinateSystem", "CoordinateSystem");
//bulkCopy.ColumnMappings.Add("CUID", "CUID");
//bulkCopy.ColumnMappings.Add("Status", "Status");
//bulkCopy.ColumnMappings.Add("Remarks", "Remarks");
// Write from the source to the destination.
bulkCopy.WriteToServer(sourceData);
}
}
Could you please tell me how can I avoid these empty lines.
Below is my Table detail
Table ParcelInf(
[RequestID] [int] IDENTITY(1,1) NOT NULL,
FormattedRequestID AS ('CUID' + RIGHT('00' + CAST(RequestID AS VARCHAR(10)),10)),
[SubdivisionNo] [nvarchar](20) NOT NULL,
[ParcelNo] [nvarchar](20) NOT NULL,
[Region] [nvarchar](20) NULL,
[City] [nvarchar](20) NULL,
[Zone] [nvarchar](20) NOT NULL,
[CoordinateSystem] [nvarchar](20) NOT NULL,
[CUID] [nvarchar](20) NULL,
[Status] [nvarchar](20) NULL,
[Remarks] [nvarchar](20) NULL,
[UpdateDate] [date] NULL,
FOREIGN KEY (RequestID) REFERENCES RequestInf (RequestID)
)