locked
Datagrid Export to Csv - Datetime problem RRS feed

  • Question

  • I'm exporting my datagridview to csv. it works perfectly but I face a problem with dateformat in csv file. I have "dd/mm/yyyy" in datagridview but I get "dd/mm/yyyy hh:mm:ss". I'm running the exporting procedure with this code: 

    Private Sub Last10ExportCSVButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Last10ExportCSVButton.Click
            Try
                Dim StrExport As String = ""
                For Each C As DataGridViewColumn In ProdottiDataGridView.Columns
                    StrExport &= """" & C.HeaderText & ""","
                Next
                StrExport = StrExport.Substring(0, StrExport.Length - 1)
                StrExport &= Environment.NewLine
                For Each R As DataGridViewRow In ProdottiDataGridView.Rows
                    For Each C As DataGridViewCell In R.Cells
                        If Not C.Value Is Nothing Then
                            StrExport &= """" & C.Value.ToString & ""","
                        Else
                            StrExport &= """" & "" & ""","
                        End If

                    Next
                    StrExport = StrExport.Substring(0, StrExport.Length - 1)
                    StrExport &= Environment.NewLine
                Next
                Dim tw As IO.TextWriter = New IO.StreamWriter("Test1.CSV")
                tw.Write(StrExport)
                tw.Close()
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
        End Sub

    What can I do to solve my problem?

    Thank you.

    Tuesday, May 21, 2019 7:17 AM

All replies

  • Hi,

    fix code,delete .ToString()

     If Not C.Value Is Nothing Then
                            StrExport &= """" & C.Value & ""","
                        Else
                            StrExport &= """" & "" & ""","
                        End If

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 21, 2019 8:53 AM
  • Hi Alex,

    thank you for support. 

    Should I delete the piece of code? Could it affect the rest of columns?

    Tuesday, May 21, 2019 8:57 AM
  • Hi Alex,

    thank you for support. 

    Should I delete the piece of code? Could it affect the rest of columns?

    Hi,

    You can try it, I test it, it works well.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 21, 2019 9:00 AM
  • Hi Alex,

    thank you for support. 

    Should I delete the piece of code? Could it affect the rest of columns?

    Hi,

    You can try it, I test it, it works well.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi Alex,

    I've just tried it and returns this error to me:

    System.InvalidCastException

    pointing to this row:

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

    Tuesday, May 21, 2019 9:09 AM
  • Hi,

    I did not report an error, or the code below

      For Each R As DataGridViewRow In DataGridView1.Rows
                    For Each C As DataGridViewCell In R.Cells
                    If C.ColumnIndex = timecolumnindex Then
                        If Not C.Value Is Nothing Then
                            StrExport &= """" & Convert.ToDateTime(C.Value.ToString).ToString("yyyy-MM-dd") & ""","
                        Else
                            StrExport &= """" & "" & ""","
                        End If
                    Else
                        If Not C.Value Is Nothing Then
                                StrExport &= """" & C.Value.ToString & ""","
                            Else
                                StrExport &= """" & "" & ""","
                            End If
                        End If
    
    
                    Next

    Best Regards,

    Alex



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 21, 2019 9:28 AM
  • Hi Alex

    timecolumnindex

    is a generic name? should I substitute it with some specific column name? I wrote it as you did and it marks as error in the code.

    Thank you very much.

    Tuesday, May 21, 2019 10:00 AM
  • Hello,

    Generally speaking loading a DataGridView using the DataSource offers more options in cases like this. For instance, let's say we have three columns, first and last name and join date. Create a class to represent the data along with a property to export the data e.g.

    Public Class DataItem
        Public Property FirstName() As String
        Public Property LastName() As String
        Public Property JoinDate As Date?
        Public ReadOnly Property Line() As String
            Get
                Dim dateValue = ""
                If JoinDate.HasValue Then
                    dateValue = JoinDate.Value.ToString("dd/mm/yyyy")
                End If
                Return $"{ControlChars.Quote}{FirstName}{ControlChars.Quote},{ControlChars.Quote}{LastName}{ControlChars.Quote}{dateValue}{ControlChars.Quote}"
            End Get
        End Property
    
    End Class
    

    Have a language extension method to get the headers as they may have different names via HeaderText then the property names.

    Public Module DataGridViewExtensions
        <Runtime.CompilerServices.Extension()>
        Public Function Header(sender As DataGridView) As String
            Dim sbHeader = New Text.StringBuilder()
            Dim headers = sender.Columns.Cast(Of DataGridViewColumn)()
    
            sbHeader.Append(String.Join(",", headers.Select(Function(column) column.HeaderText)))
    
            Return sbHeader.ToString()
        End Function
    End Module

    Then in a button click event put this all together

    Imports System.IO
    
    Public Class ExampleForm
        Private Sub exportButton_Click(sender As Object, e As EventArgs) _
            Handles exportButton.Click
    
            Dim sb As New Text.StringBuilder
    
            Dim columnHeaders = ProdottiDataGridView.Header()
            sb.AppendLine(columnHeaders)
    
            CType(ProdottiDataGridView.DataSource, List(Of DataItem)).
                ForEach(Sub(row)
                            sb.AppendLine(row.Line)
                        End Sub)
    
    
            File.WriteAllText("Test1.CSV", sb.ToString())
    
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, May 21, 2019 10:09 AM
  • Hi Karen,

    is there any way to get the string as text so that Excel o Open Office don't get it as a dateformat? Is not this an alternative way?

    Best Regards.

    Tuesday, May 21, 2019 10:31 AM
  • Hi Karen,

    is there any way to get the string as text so that Excel o Open Office don't get it as a dateformat? Is not this an alternative way?

    Best Regards.


    Change the format in ToString to the format you want. You might try (I have not) it, note the single quote which should tell Excel that the date is a string.

    Return $"{ControlChars.Quote}{FirstName}{ControlChars.Quote},{ControlChars.Quote}{LastName}{ControlChars.Quote}'{dateValue}{ControlChars.Quote}"


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, May 21, 2019 10:50 AM
  • Hi Alex

    timecolumnindex

    is a generic name? should I substitute it with some specific column name? I wrote it as you did and it marks as error in the code.

    Thank you very much.

    Hi,

    timecolumnindex is datetime column index

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 22, 2019 8:41 AM