Incorrect date format when writing to csv

[Tinnin]

I'm using the following code to write to a csv

        Dim str As New StringBuilder
        Dim dr As DataRow
        Dim field As Object
        Dim column As DataColumn

        myds.Tables(0).AcceptChanges()

        For Each column In myds.Tables(0).Columns
            str.Append(column.ColumnName.ToString & ",")
        Next
        str.Replace(",", vbNewLine, str.Length - 1, 1)

        For Each dr In myds.Tables(0).Rows
            For Each field In dr.ItemArray
                str.Append(field.ToString & ",")
            Next
            str.Replace(",", vbNewLine, str.Length - 1, 1)
        Next

        Try
            My.Computer.FileSystem.WriteAllText("C:\Users\filePath\myFile.csv", str.ToString, False)
        Catch ex As Exception
            MessageBox.Show("Write Error")
        End Try

One of my fields holds dates in the format 'dd/mm/yy' but when written to the csv appear as 'dd/mm/yy 00:00:00'. Is there a way I can specify the date format on writing to the csv?

[Tinnin]

is there a way to do it using a schema.ini file?

[Tinnin]

From what I can understand, Excel is doing the formatting on the csv.

[Reverend Jim]

You can format the output using

.ToString("yyyy-mm-dd")

as in

Now().ToString("yyyy-mm-dd")

[tinstaafl]

It looks to me like you'll have to look for the field with the date then change the output format. Since the row is coming from a table the index of the field you want should be constant. By changing the for each to a for loop, it might be easier to find the right field. Something like this might work

For Each dr In myds.Tables(0).Rows
    'Loop only to the second last item
    For I = 0 to dr.ItemArray.Count - 2
        Dim TempStr as String = dr.ItemArray(I).ToString

        If I = 4 Then
            str.Append(TempStr.ToString("dd/mm/yy") & ",")
        Else
            str.Append(TempStr & ",")
        End If
    Next
    'Add the last item with the newline instead of a comma
    str.Append(TempStr & vbNewLine)
Next

[Tinnin]

The date is in the correct format (as a string) right up to the moment the csv is created.

[Reverend Jim]

A csv is simply a text file so if the string is correct then the file should be as well. What does it look like if you open the file in Notepad? If you are opening it in Excel it is possible that Excel is displaying it as a date in the default system date format. In that case you can either select the column from within Excel and change the formatting or you can change the system date format from the control panel.

[Tinnin]

It is like this when viewing in notepad. Also, the system date format is correct to how I need it to be. Excel is displaying the column as datetime rather than just date when I open it in excel. But I'm looking to have the field formatted correctly automatically rather than having to do it manually each time in excel.

[tinstaafl]

If you're opening the .csv file within a workbook, you can save the customized workbook as a template, then when you import the .csv file the column will be formatted right.