How to recognize time and date in CSV file

[Ivo77]

I'm building an application in which a certain CSV schedule is read and searched for time and date values.

I want to read each cell in the CSV file with a streamreader and have VB determine if the contents of the cell represent a date (like 31-10-2011), a time (like 22:30) or random text data which is not formatted like a date or time.

How can I make VB recognize a string as either a date, a time or neither one, so I can use this string formatted as Date, Time or discard of it?

The contents of the CSV file will look something like this:

;Schedulename;text;text;31-10-2011;text;text;6-11-2011;text;;;text

Although the CSV file will never contain much data, the position of the date and time fields in a row may vary, so I cannot tell VB which cell to read as date/time. It has to recognize the value as date, time, or unusable text.



Dim CSVReadline As New System.IO.StreamReader(schedule.csv")
Cellcheck = CSVReadline.ReadLine()
String = Cellcheck.Split(";")
For i = 0 to 10
Scheduledate = String(i) ............ etc.

[dbasnett]

I thought CSV file fields were separated by commas?

[kareninstructor]

Seems strange that in the example you have there are two different date formats rather than one. If there was one date format you could use the following to check for dates but alas needs more logic for multiple date formats.

    Sub Main()
        Dim Lines = (From line In IO.File.ReadAllLines("schedule.csv") _
                     Where line.Length > 0 _
                     Let Items = line.Split(";"c)).ToList

        For Row As Integer = 0 To Lines.Count - 1
            For Col As Integer = 0 To Lines(Row).Items.Count - 1
                Dim Item As String = Lines(Row).Items(Col).Trim
                If Item.Length > 0 Then
                    If Date.TryParse(Item, Nothing) Then
                        Console.WriteLine("Date [{0}]", Item)
                    Else
                        Console.WriteLine("Not [{0}]", Item)
                    End If
                End If
            Next
        Next
        Console.ReadLine()
    End Sub

[Harddisk]

Ivo77, will the date always be formatted in dd-mm-yyyy, or it would be random like yyyy-mm-dd, or mm-dd-yyyy? Since you have got the separator part worked out, I believe finding the date column is not difficult. Then do a check if it's a valid date or do a manual substring of text, concat them into a proper date format and verify if it's a valid date.

dbasnett, nowadays, CSV no longer means it has to be separated with only commas. Due to the commonality of comma in addresses and the impotency of Excel splitting up double-quoted string into proper column. It would be better choice to use a some other character as delimiter.

[stanav]

If the dates are always in certain columns, you can get the date fields by the column indexes and convert them into date objects. If you don't know which column contains a date, your can use regular expression to test each and every field first. Once you find a match, convert it to a date object.

[nbrege]

Seems strange that in the example you have there are two different date formats rather than one.

There is not necessarily two date formats in his example ... 6-11-2011 could be (and likely is) Nov 6, 2011 and not Jun 11, 2011. If that's the case then your method should work.

[Ivo77]

There is not necessarily two date formats in his example ... 6-11-2011 could be (and likely is) Nov 6, 2011 and not Jun 11, 2011. If that's the case then your method should work.

Thanks for your help so far guys.
Indeed, I don't have two different formats. I'm using the European notation, so dd-mm-yyyy and 24 hour time format. These will always be the same.

However, the dates and times will not always be in the same location. Basically, I want to let VB read a line with cells separated by ";" and determine if and which of these cells contain a date, a time or any other string. If it's not a date or a time, I can dispose of this data and move on to the next cell.

[Harddisk]

Stanav has the best solution to your problem then.