Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i am importing data into sql server from CSV file now i am getting this issue
String was not recognized as a valid DateTime.Couldn't store <20180526> in Date Column.  Expected type is DateTime.


What I have tried:

Code added from replies;

here is my complete code

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Configuration;



namespace BindGridviewFromCSVFile
{
    public partial class BindGridview : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
       
        }

        protected void btnFileUpload_Click(object sender, EventArgs e)
        {
            
            
            //Creating object of datatable  
            //Upload and save the file.
            //getting full file path of Uploaded file  
            string CSVFilePath = Server.MapPath(FileUpload1.PostedFile.FileName);

            //Reading All text  
            string ReadCSV = File.ReadAllText(CSVFilePath);

            DataTable dt = new DataTable("HR");
            //DataColumn dateColumn = new DataColumn();
            
            //dateColumn.ColumnName = "Date";
            //dt.Columns.AddRange(new DataColumn[5] { new DataColumn("Empcode", typeof(int)),
            //new DataColumn("Days", typeof(int)),
            // new DataColumn("Date", typeof(int)),
            //DateTime Date = Convert.ToDateTime("Date");
        //    table.Columns.Add("SaleDate", typeof(DateTime));


            //  new DataColumn("Time", typeof(int)),
            //   new DataColumn("IN/OUT", typeof(int)),});

            // Create Column 1: SaleDate
       

            DataColumn DaysColumn = new DataColumn();
            // dateColumn.DataType = Type.GetType("System.int");     
            DaysColumn.ColumnName = "Days";

      

            DataColumn dateColumn = new DataColumn();
            dateColumn.DataType = Type.GetType("System.DateTime");
         
            dateColumn.ColumnName = "Date";

            
            
       


            // Create Column 2: ProductName
            DataColumn timeNameColumn = new DataColumn();
            timeNameColumn.ColumnName = "Time";

            // Create Column 3: TotalSales
            DataColumn INOUTColumn = new DataColumn();
          //  totalSalesColumn.DataType = Type.GetType("System.int");
           INOUTColumn.ColumnName = "IN/OUT";

           //  EmpcodeColumn.DataType = Type.GetType("int");
           DataColumn EmpcodeColumn = new DataColumn();
           EmpcodeColumn.ColumnName = "Empcode";

            // Add the columns to the ProductSalesData DataTable
           dt.Columns.Add(DaysColumn);
           dt.Columns.Add(dateColumn);
           dt.Columns.Add(timeNameColumn);
           dt.Columns.Add(INOUTColumn);
           dt.Columns.Add(EmpcodeColumn);
           // Let's populate the datatable with our stats.
           // You can add as many rows as you want here!

           // Create a new row
           //DataRow dailyProductSalesRow = dt.NewRow();
           //dailyProductSalesRow["Empcode"] = "";
           //dailyProductSalesRow["Days"] = "";

           //dailyProductSalesRow["Date"] = DateTime.Today;
           //dailyProductSalesRow["Time"] = "";
           //dailyProductSalesRow["IN/OUT"] = "";



          string csvData = File.ReadAllText(CSVFilePath);
            foreach (string row in csvData.Split('\n'))
            {
                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++;
                    }
                }
            }

            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Institute;Integrated Security=True");
            //        con.Open();
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name.
                    sqlBulkCopy.DestinationTableName =
Posted
Updated 14-Sep-18 5:09am
v2
Comments
F-ES Sitecore 14-Sep-18 9:15am    
Looks like there is a problem with your code, but as you haven't shown us the code it's impossible to give explicit advice.
Member 12314309 14-Sep-18 10:12am    
here is my complete code

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Configuration;



namespace BindGridviewFromCSVFile
{
public partial class BindGridview : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void btnFileUpload_Click(object sender, EventArgs e)
{


//Creating object of datatable
//Upload and save the file.
//getting full file path of Uploaded file
string CSVFilePath = Server.MapPath(FileUpload1.PostedFile.FileName);

//Reading All text
string ReadCSV = File.ReadAllText(CSVFilePath);

DataTable dt = new DataTable("HR");
//DataColumn dateColumn = new DataColumn();

//dateColumn.ColumnName = "Date";
//dt.Columns.AddRange(new DataColumn[5] { new DataColumn("Empcode", typeof(int)),
//new DataColumn("Days", typeof(int)),
// new DataColumn("Date", typeof(int)),
//DateTime Date = Convert.ToDateTime("Date");
// table.Columns.Add("SaleDate", typeof(DateTime));


// new DataColumn("Time", typeof(int)),
// new DataColumn("IN/OUT", typeof(int)),});

// Create Column 1: SaleDate


DataColumn DaysColumn = new DataColumn();
// dateColumn.DataType = Type.GetType("System.int");
DaysColumn.ColumnName = "Days";



DataColumn dateColumn = new DataColumn();
dateColumn.DataType = Type.GetType("System.DateTime");

dateColumn.ColumnName = "Date";






// Create Column 2: ProductName
DataColumn timeNameColumn = new DataColumn();
timeNameColumn.ColumnName = "Time";

// Create Column 3: TotalSales
DataColumn INOUTColumn = new DataColumn();
// totalSalesColumn.DataType = Type.GetType("System.int");
INOUTColumn.ColumnName = "IN/OUT";

// EmpcodeColumn.DataType = Type.GetType("int");
DataColumn EmpcodeColumn = new DataColumn();
EmpcodeColumn.ColumnName = "Empcode";

// Add the columns to the ProductSalesData DataTable
dt.Columns.Add(DaysColumn);
dt.Columns.Add(dateColumn);
dt.Columns.Add(timeNameColumn);
dt.Columns.Add(INOUTColumn);
dt.Columns.Add(EmpcodeColumn);
// Let's populate the datatable with our stats.
// You can add as many rows as you want here!

// Create a new row
//DataRow dailyProductSalesRow = dt.NewRow();
//dailyProductSalesRow["Empcode"] = "";
//dailyProductSalesRow["Days"] = "";

//dailyProductSalesRow["Date"] = DateTime.Today;
//dailyProductSalesRow["Time"] = "";
//dailyProductSalesRow["IN/OUT"] = "";



string csvData = File.ReadAllText(CSVFilePath);
foreach (string row in csvData.Split('\n'))
{
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++;
}
}
}

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Institute;Integrated Security=True");
// con.Open();
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name.
sqlBulkCopy.DestinationTableName =
Vincent Maverick Durano 14-Sep-18 10:44am    
Please use the "Improve Question" action link and post your code there instead. Also tell us which line causes the error.

You probably need to convert the string value to a DateTime with DateTime.Parse().
See examples here: https://www.dotnetperls.com/datetime-parse[^]
 
Share this answer
 
Thank you for updating your question with the code.

Looking at your code, you have this line below:
C#
dt.Rows[dt.Rows.Count - 1][i] = cell;


It looks like you are treating all values as string type but your DataTable contains a System.DateTime column. You need to identify the Column that has non-string type and perform conversion before assigning the value.

A quick and dirty example basing on your code is to do something like this:

C#
foreach (string row in csvData.Split('\n'))
            {
                if (!string.IsNullOrEmpty(row))
                {
                    dt.Rows.Add();
                    int i = 0;
    
                    foreach (string cell in row.Split(','))
                    {
                        //means you are looking for the 2nd Column which of type DateTime
                        //note that index starts at 0, so we use 1 for locating 2nd column
                        if(i = 1){
                            //convert string to datetime
                            DateTime documentDate = DateTime.MinValue;
                            if(DateTime.TryParse(cell, out documentDate)){
                              dt.Rows[dt.Rows.Count - 1][i] = documentDate;
                            }
                         
                        }
                        else{
                            dt.Rows[dt.Rows.Count - 1][i] = cell;
                        }
                        i++;
                    }
                }
            }


The thing to keep a note there is we use DateTime.TryParse to ensure that we are passing a valid datetime string value to the column.

You may also want to look at this article that uses schema.ini to import CSV to SQL database: ASP.NET WebForms: Uploading and Importing CSV File to SQL Server[^]
 
Share this answer
 
v3
Comments
Member 12314309 14-Sep-18 11:31am    
now getting this error
The given value of type String from the data source cannot be converted to type datetime of the specified target column.
Vincent Maverick Durano 14-Sep-18 12:22pm    
You have to tell us which line causes the error. The main thing to keep in mind is that you need to ensure that the type you pass in your SQL database should match with the type of your table column.
Member 12314309 14-Sep-18 12:27pm    
Now data is importing blank in sql server table and no error is giving.
Vincent Maverick Durano 14-Sep-18 12:40pm    
Then that's the time that you debug your code. Set a break point on the line where you associate the data and step into it to figure out what went wrong.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900