locked
VB.NET - Datagridview export .csv date format RRS feed

  • Question

  • Hi,

    I get an issue everytime I export a Datagridview in a .csv file. Date time column, that look like this (dd/MM/yyyy) comes in the csv file like this (dd/MM/yyyy 00.00.00). It's something that I don't want because I already have a time column in that datagridview. Here is the code I'm using, how can I get rid of those 'zeros'?

                SaveFileDialog5.Filter = "CSV Files (*.csv*)|*.csv"
                SaveFileDialog5.Title = "Save CSV Files"
                SaveFileDialog5.FileName = ("Backup Database GDI1 al " & DateAdd(DateInterval.Year, -2, Now()).ToString("dd_MM_yyyy"))
                If SaveFileDialog5.ShowDialog() <> DialogResult.OK Then
    
                    Exit Sub
                    bd.Rows.Clear()
                    bd.Columns.Clear()
                    RichTextBox1.AppendText(vbCrLf & "" & DateTime.Now.ToString("dd/MM/yyyy HH':'mm':'ss") & " La compilazione del backup è stata cancellata.")
                    RichTextBox1.ScrollToCaret()
                Else
    
                End If
    
                Dim fileName As String = Me.SaveFileDialog5.FileName
                Try
                    Dim StrExport As String = ""
                    For Each C As DataGridViewColumn In BackupDataGridViews.Columns
                        StrExport &= """" & C.HeaderText & ""","
                    Next
                    StrExport = StrExport.Substring(0, StrExport.Length - 1)
                    StrExport &= Environment.NewLine
                    For Each R As DataGridViewRow In BackupDataGridViews.Rows
                        For Each C As DataGridViewCell In R.Cells
                            Dim timecolumnindex As Integer = Nothing
                            If C.ColumnIndex = timecolumnindex Then
                                If Not C.Value Is Nothing Then
                                    StrExport &= """" & C.Value.ToString & ""","
                                Else
                                    StrExport &= """" & "" & ""","
                                End If
                            Else
                                If Not C.Value Is Nothing Then
                                    StrExport &= """" & C.Value.ToString & ""","
                                Else
                                    StrExport &= """" & "" & ""","
                                End If
                            End If
                        Next
                        StrExport = StrExport.Substring(0, StrExport.Length - 1)
                        StrExport &= Environment.NewLine
                    Next
    
                    Dim tw As IO.TextWriter = New IO.StreamWriter(SaveFileDialog5.FileName)
                    tw.Write(StrExport)
                    tw.Close()
                    RichTextBox1.AppendText(vbCrLf & "" & DateTime.Now.ToString("dd/MM/yyyy HH':'mm':'ss") & " La creazione del file .CSV del backup è stata effettuata correttamente.")
                    RichTextBox1.ScrollToCaret()
                Catch ex As System.Exception
                    RichTextBox1.AppendText(vbCrLf & "" & DateTime.Now.ToString("dd/MM/yyyy HH':'mm':'ss") & " " & ex.Message & ". La creazione del file .CSV del backup non è andata a buon fine.")
                    RichTextBox1.ScrollToCaret()
                    go = False
                End Try

    Thank you.

    Thursday, June 6, 2019 2:31 PM

Answers

  • Hi,
    you use the ToString method without formatstring.

                                    StrExport &= """" & C.Value.ToString & ""","

    Expand your If-construct for the Date (DateTime) column and use the formatstring like this:

                                    StrExport &= """" & C.Value.ToString("dd/MM/yyyy") & ""","


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by pb1990 Thursday, June 6, 2019 2:44 PM
    Thursday, June 6, 2019 2:40 PM

All replies

  • Hi,
    you use the ToString method without formatstring.

                                    StrExport &= """" & C.Value.ToString & ""","

    Expand your If-construct for the Date (DateTime) column and use the formatstring like this:

                                    StrExport &= """" & C.Value.ToString("dd/MM/yyyy") & ""","


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by pb1990 Thursday, June 6, 2019 2:44 PM
    Thursday, June 6, 2019 2:40 PM
  • Hi,
    you use the ToString method without formatstring.

                                    StrExport &= """" & C.Value.ToString & ""","

    Expand your If-construct for the Date (DateTime) column and use the formatstring like this:

                                    StrExport &= """" & C.Value.ToString("dd/MM/yyyy") & ""","


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)

    Hi Peter and thank you for the answer,

    what do you mean with "expanding"?

    Best regards

    Thursday, June 6, 2019 2:46 PM
  • Hi,
    I mean a construct like this:

    If C.ColumnIndex = timecolumnindex Then If Not C.Value Is Nothing Then StrExport &= """" & C.Value.ToString & """," Else StrExport &= """" & "" & """," End If ElseIf C.ColumnIndex = datecolumnidex If Not C.Value Is Nothing Then StrExport &= """" & C.Value.ToString("dd/MM/yyyy") & """," Else StrExport &= """" & "" & """," End If Else If Not C.Value Is Nothing Then StrExport &= """" & C.Value.ToString & """," Else StrExport &= """" & "" & """," End If End If



    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks




    Thursday, June 6, 2019 2:55 PM
  • Hi,
    I mean a construct like this:

    If C.ColumnIndex = timecolumnindex Then If Not C.Value Is Nothing Then StrExport &= """" & C.Value.ToString & """," Else StrExport &= """" & "" & """," End If ElseIf C.ColumnIndex = datecolumnidex If Not C.Value Is Nothing Then StrExport &= """" & C.Value.ToString("dd/MM/yyyy") & """," Else StrExport &= """" & "" & """," End If Else If Not C.Value Is Nothing Then StrExport &= """" & C.Value.ToString & """," Else StrExport &= """" & "" & """," End If End If



    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)




    Hi Peter,

    I tried it... I doesn't give me any error but situation doesn't change...

    Regars.

    Friday, June 7, 2019 7:52 AM