Share via

Format Date after import from CSV

Marius Dan 136 Reputation points
2021-08-23T18:09:22.45+00:00

Im using csvhelper to import to DataGrid View and I have a problem with formatting the date. I can't format it to yyyy-mm-dd to add it to the database. If I change in Windouws the date format to yyyy-mm-dd the import works but I can't change it in Windows must be dd / mm / yyyy

The error i get : Conversion failed when converting date/time from character string 

private void button60_Click(object sender, EventArgs e)  
    {  
        OpenFileDialog fdlg = new OpenFileDialog();  
        fdlg.Title = "Select file";  
        fdlg.InitialDirectory = @"c:\";  
        fdlg.FileName = textBox4.Text;  
        fdlg.Filter = "Text and CSV Files(*.txt, *.csv)|*.txt;*.csv|Text Files(*.txt)|*.txt|CSV Files(*.csv)|*.csv|All Files(*.*)|*.*";  
        fdlg.FilterIndex = 1;  
        fdlg.RestoreDirectory = true;  
        if (fdlg.ShowDialog() == DialogResult.OK)  
        {  
            using (var reader = new StreamReader(fdlg.FileName))  
            using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))  
  
            {  
                using (var dr = new CsvDataReader(csv))  
                {  
                    var dt = new DataTable();  
                    dt.Load(dr);  
                    dataGridView2.DataSource = dt;  
                    dataGridView2.Columns[0].HeaderCell.Value = "ID";  
                    dataGridView2.Columns[1].HeaderCell.Value = "Nr.";  
                    dataGridView2.Columns[2].HeaderCell.Value = "Serie";  
                    dataGridView2.Columns[3].HeaderCell.Value = "CUI";  
                    dataGridView2.Columns[4].HeaderCell.Value = "Client";  
                    dataGridView2.Columns[5].HeaderCell.Value = "Serie SIM";  
                    dataGridView2.Columns[6].DefaultCellStyle.Format = "yyyy-mm-dd";  
                    dataGridView2.Columns[6].HeaderCell.Value = "Data activarii";  
                    dataGridView2.Columns[7].HeaderCell.Value = "Status";  
                    dataGridView2.Columns[8].HeaderCell.Value = "Data expirarii";  
                    dataGridView2.Columns[8].DefaultCellStyle.Format = "yyyy-mm-dd";  
                    dataGridView2.Columns[9].HeaderCell.Value = "Telefon";  
                    dataGridView2.Columns[10].HeaderCell.Value = "Nr. activat";  
                    dataGridView2.Columns[11].HeaderCell.Value = "Nr_zile";  
                    dataGridView2.Columns[12].HeaderCell.Value = "Ob.";  
                    dataGridView2.Columns[13].HeaderCell.Value = "Tip client";  
                    dataGridView2.Columns[14].HeaderCell.Value = "Email";  
                    dataGridView2.Columns[15].HeaderCell.Value = "Data inregistrarii";  
                    dataGridView2.Columns[15].DefaultCellStyle.Format = "yyyy-mm-dd";  
                    dataGridView2.Columns[16].HeaderCell.Value = "Semnatura";  
  
                    dataGridView2.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(238, 239, 249);  
                    dataGridView2.CellBorderStyle = DataGridViewCellBorderStyle.SingleHorizontal;  
                    dataGridView2.DefaultCellStyle.SelectionBackColor = Color.DarkTurquoise;  
                    dataGridView2.DefaultCellStyle.SelectionForeColor = Color.WhiteSmoke;  
                    dataGridView2.BackgroundColor = Color.White;  
                    dataGridView2.EnableHeadersVisualStyles = false;  
                    dataGridView2.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.None;  
                    dataGridView2.ColumnHeadersDefaultCellStyle.BackColor = Color.FromArgb(20, 25, 72);  
                    dataGridView2.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;  
                }  
            }  
        }  
    }  
     private void button58_Click(object sender, EventArgs e)  
    {  
        string constring = ConfigurationManager.ConnectionStrings["db"].ConnectionString;  
        SqlConnection con = new SqlConnection(constring);  
        if (con.State == ConnectionState.Closed)  
            con.Open();  
        for (int i = 0; i < dataGridView2.RowCount; i++)  
        {  
            try  
            {  
                SqlCommand sqlCmd = new SqlCommand("insert into abonament (nr, serie, cui, client, sim, data_inst, activare, data_exp, telefon, nr_activat, nr_zile, ob,   
                tip_client, email, datacurenta1, semnatura) values ( @nr, @serie, @cui, @client, @sim, @data_inst, @activare, @data_exp, @telefon, @nr_activat, @nr_zile,   
                @ob, @tip_client, @email, @datacurenta1, @semnatura)", con);  
  
                sqlCmd.Parameters.AddWithValue("@mode", "Add");  
                sqlCmd.Parameters.AddWithValue("@id", 0);  
                sqlCmd.Parameters.AddWithValue("@nr   ", dataGridView2.Rows[i].Cells[1].Value);  
                sqlCmd.Parameters.AddWithValue("@serie   ", dataGridView2.Rows[i].Cells[2].Value);  
                sqlCmd.Parameters.AddWithValue("@cui   ", dataGridView2.Rows[i].Cells[3].Value);  
                sqlCmd.Parameters.AddWithValue("@client   ", dataGridView2.Rows[i].Cells[4].Value);  
                sqlCmd.Parameters.AddWithValue("@sim   ", dataGridView2.Rows[i].Cells[5].Value);  
                sqlCmd.Parameters.AddWithValue("@data_inst   ", dataGridView2.Rows[i].Cells[6].Value);  
                sqlCmd.Parameters.AddWithValue("@activare   ", dataGridView2.Rows[i].Cells[7].Value);  
                sqlCmd.Parameters.AddWithValue("@data_exp   ", dataGridView2.Rows[i].Cells[8].Value);  
                sqlCmd.Parameters.AddWithValue("@telefon   ", dataGridView2.Rows[i].Cells[9].Value);  
                sqlCmd.Parameters.AddWithValue("@nr_activat   ", dataGridView2.Rows[i].Cells[10].Value);  
                sqlCmd.Parameters.AddWithValue("@nr_zile   ", dataGridView2.Rows[i].Cells[11].Value);  
                sqlCmd.Parameters.AddWithValue("@ob   ", dataGridView2.Rows[i].Cells[12].Value);  
                sqlCmd.Parameters.AddWithValue("@tip_client   ", dataGridView2.Rows[i].Cells[13].Value);  
                sqlCmd.Parameters.AddWithValue("@email   ", dataGridView2.Rows[i].Cells[14].Value);  
                sqlCmd.Parameters.AddWithValue("@datacurenta1   ", dataGridView2.Rows[i].Cells[15].Value);  
                sqlCmd.Parameters.AddWithValue("@semnatura   ", dataGridView2.Rows[i].Cells[16].Value);  
  
                sqlCmd.ExecuteNonQuery();  
  
            }  
  
            catch (Exception ex)  
            {  
                MessageBox.Show(ex.Message, "Datele nu au fost salvate");  
            }  
  
             
  
        }  
        MessageBox.Show("Datele au fost salvate");  
    }

Ex. csv file it is am export from datagridview : 

private void ExportGridToCSV1()  
            {  
                SaveFileDialog sfd = new SaveFileDialog();  
                sfd.Filter = "CSV (*.csv)|*.csv";  
                sfd.FileName = "Output.csv";  
                if (sfd.ShowDialog() == DialogResult.OK)  
                {  
                    TextWriter writer = new StreamWriter(sfd.FileName);  
                    for (int i = 0; i < rap_cuiclient.Rows.Count - 1; i++)  
                    {  
                        writer.Write(rap_cuiclient.Rows[i].Cells[0].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[1].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[2].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[3].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[4].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[5].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[6].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[7].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[8].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[9].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[10].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[11].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[12].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[13].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[14].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[15].Value.ToString() + ",");  
                        writer.Write(rap_cuiclient.Rows[i].Cells[16].Value.ToString() + ",");  
      
                        writer.WriteLine("");  
                    }  
                    writer.Close();  
                    MessageBox.Show("Date exportate");  
                }  
            }

1,24,custodie,8888888,Marius,8940012004412026020,01/01/1900 00:00:00,IN CUSTODIE,01/01/1900 00:00:00,,,,,DISTRIBUITOR,,04/06/2021 00:00:00,,
2,25,custodie,8888888,Marius,8940012004412026079,01/01/1900 00:00:00,IN CUSTODIE,01/01/1900 00:00:00,,,,,DISTRIBUITOR,,04/06/2021 00:00:00,,

this is the only solution that i found to export correct 8940012004412026020

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,648 questions
Accepted answer
  1. Marius Dan 136 Reputation points
    2021-08-24T11:32:18.88+00:00

    The solution was to format before inserting in database 

     for (int i = 0; i < dataGridView2.RowCount; i++)
            {
                DateTime d1 = Convert.ToDateTime(dataGridView2.Rows[i].Cells[6].Value.ToString());
                string format = "s";
                DateTime d2 = Convert.ToDateTime(dataGridView2.Rows[i].Cells[8].Value.ToString());
                DateTime d3 = Convert.ToDateTime(dataGridView2.Rows[i].Cells[15].Value.ToString());

                try
                {

                    SqlCommand sqlCmd = new SqlCommand("insert into abonament (nr, serie, cui, client, sim, data_inst, activare, data_exp, telefon, nr_activat, nr_zile, ob, tip_client, email, datacurenta1, semnatura) values ( @nr, @serie, @cui, @client, @sim, @data_inst, @activare, @data_exp, @telefon, @nr_activat, @nr_zile, @ob, @tip_client, @email, @datacurenta1, @semnatura)", con);

                    sqlCmd.Parameters.AddWithValue("@mode", "Add");
                    sqlCmd.Parameters.AddWithValue("@id", 0);
                    sqlCmd.Parameters.AddWithValue("@nr   ", dataGridView2.Rows[i].Cells[1].Value);
                    sqlCmd.Parameters.AddWithValue("@serie   ", dataGridView2.Rows[i].Cells[2].Value);
                    sqlCmd.Parameters.AddWithValue("@cui   ", dataGridView2.Rows[i].Cells[3].Value);
                    sqlCmd.Parameters.AddWithValue("@client   ", dataGridView2.Rows[i].Cells[4].Value);
                    sqlCmd.Parameters.AddWithValue("@sim   ", dataGridView2.Rows[i].Cells[5].Value);
                    sqlCmd.Parameters.AddWithValue("@data_inst   ", d1.Date.ToString(format));
                    sqlCmd.Parameters.AddWithValue("@activare   ", dataGridView2.Rows[i].Cells[7].Value);
                    sqlCmd.Parameters.AddWithValue("@data_exp   ", d2);
                    sqlCmd.Parameters.AddWithValue("@telefon   ", dataGridView2.Rows[i].Cells[9].Value);
                    sqlCmd.Parameters.AddWithValue("@nr_activat   ", dataGridView2.Rows[i].Cells[10].Value);
                    sqlCmd.Parameters.AddWithValue("@nr_zile   ", dataGridView2.Rows[i].Cells[11].Value);
                    sqlCmd.Parameters.AddWithValue("@ob   ", dataGridView2.Rows[i].Cells[12].Value);
                    sqlCmd.Parameters.AddWithValue("@tip_client   ", dataGridView2.Rows[i].Cells[13].Value);
                    sqlCmd.Parameters.AddWithValue("@email   ", dataGridView2.Rows[i].Cells[14].Value);
                    sqlCmd.Parameters.AddWithValue("@datacurenta1   ", d3);
                    sqlCmd.Parameters.AddWithValue("@semnatura   ", dataGridView2.Rows[i].Cells[16].Value);

                    sqlCmd.ExecuteNonQuery();

                }
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. WayneAKing 4,921 Reputation points
    2021-08-23T21:11:06.777+00:00

    System.Globalization.CultureInfo ci =
    new System.Globalization.CultureInfo("DE-de");
    string format = "dd.MM.yyyy";
    dataGridView2.Columns[6].HeaderCell.Value = "Data activarii";
    DateTime.ParseExact(dataGridView2.Columns[6].ValueType.ToString(), format, ci);

    Some suggestions when exploring the intricacies of such
    translations:

    (1) Stop grouping or joining operations/methods together
    and then trying to intuit which part of the compound
    operations is failing. Break the compound statements
    into their individual steps so you can examine the results
    of each operation. This will help narrow your focus to the
    specific step that needs correcting.

    (2) Your last (4th) statement above appears to be somewhat
    useless as you are not doing anything with the return from
    the ParseExact method. Note also that the string from ToString()
    may need to be explicitly identified as being of the DE-de
    culture.

    (3) Try this small experiment with DateTime formatting
    and see if it helps focus where your own code needs
    amending. 

    static void Main(string[] args)
{
    System.Globalization.CultureInfo ci = 
       new System.Globalization.CultureInfo("DE-de");
    string format = "dd.MM.yyyy";


    DateTime dt = new DateTime(2021,8,23); 

    string str = dt.ToString();
    string strG = dt.ToString(ci);

    Console.WriteLine("{0}\n{1}", str, strG);

    DateTime parseddt = DateTime.Parse(dt.ToString(ci),ci);
    Console.WriteLine(parseddt);
    Console.WriteLine(parseddt.ToString(ci));
}

/*
8/23/21 12:00:00 AM
23.08.2021 00:00:00
8/23/21 12:00:00 AM
23.08.2021 00:00:00
*/
    • Wayne
    0 comments
  2. Timon Yang-MSFT 9,586 Reputation points
    2021-08-24T05:52:58.69+00:00

    This code is bound to cause an exception, because dataGridView2.Columns[6].ValueType is the data type of that column, not the actual datetime data. 

      System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("DE-de");  
  string format = "dd.MM.yyyy";  
  dataGridView2.Columns[6].HeaderCell.Value = "Data activarii";  
  DateTime.ParseExact(dataGridView2.Columns[6].ValueType.ToString(), format, ci);

    Moreover, although we set the format of the Datetime column in the datagridview to yyyy-mm-dd, when we use dataGridView1.Rows[i].Cells[4].Value.ToString() to get it, it still is the default format.

    Maybe you need code like this: 

                System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("DE-de");  
            dataGridView1.Columns[4].HeaderCell.Value = "Data activarii";  
            for (int i = 0; i < dataGridView1.Rows.Count-1; i++)  
            {  
                DateTime dateTime = Convert.ToDateTime(dataGridView1.Rows[i].Cells[4].Value.ToString(), ci);  
            }

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments