How do I import a long date that will not create an extra column on import?

I have VB app that reads in in a file from  various formats, then converts it and allows it to be processed.  One format that has been problematic is where the date that comes in is in long format (November 11, 2008) causes an extra colum to be produced something like this:

September 8, 2008    Widgit       Jones
Should produce
9/8/2008    Widgit    Jones

but instead produces
9/8      [blank cell]     Widgit       Jones

is there some sort of coding that will bring in the long date and treat it as if it were written 10/08/2008?
Public Sub FillGridText(ByVal SepOI As String)
 
        'Create Data Table and add to Dataset
        Dim dt As New DataTable
        Dim c As Integer
        Try
            DS = New System.Data.DataSet
            'Set DS number of columns to 40
            For c = 1 To 40
                dt.Columns.Add("Field" & c)
            Next
            DS.Tables.Add(dt)
        Catch ex As Exception
            MsgBox(ex.Message & ": " & ex.StackTrace)
        End Try
 
        'Process csv file into DS
        Dim sr As System.IO.StreamReader
        Dim LineOI As String
        Dim Fields() As String, f As Integer
        Dim dr As DataRow
        Try
            sr = New System.IO.StreamReader(frm.txtFilePath.Text & frm.txtFileName.Text, System.Text.Encoding.Default, True)
            Do
                LineOI = sr.ReadLine
                If SepOI = "TAB" Then
                    If Not (LineOI Is Nothing) And (InStr(LineOI, Chr(9)) > 0) Then
                        'MsgBox("before fix " & LineOI)
                        'LineOI = FixLine(LineOI)
                        LineOI = Replace(Replace(LineOI, Chr(34), ""), "$", "")
                        'MsgBox("after fix " & LineOI)
                        Fields = Split(LineOI, Chr(9))
                        dr = dt.NewRow()
                        For f = 0 To Fields.Length - 1
                            If f < c - 1 Then 'Only works with first 40 columns in the file
                                dr(f) = Fields(f)
                            End If
                        Next
                        dt.Rows.Add(dr)
                    End If
                Else
                    If Not (LineOI Is Nothing) And (InStr(LineOI, SepOI) > 0) Then
                        'MsgBox("before fix " & LineOI)
                        '??? LineOI = FixLine(LineOI)
                        LineOI = Replace(LineOI, Chr(34), "")
                        'MsgBox("after fix " & LineOI)
                        Fields = Split(LineOI, SepOI)
                        dr = dt.NewRow()
                        For f = 0 To Fields.Length - 1
                            If f < c - 1 Then 'Only works with first 40 columns in the file
                                dr(f) = Fields(f)
                            End If
                        Next
                        dt.Rows.Add(dr)
                    End If
                End If
            Loop Until LineOI Is Nothing
            With frm.dgFileData
                .DataSource = DS
            End With
            DSRowCount = DS.Tables(0).Rows.Count
            sr.Close()
        Catch ex As Exception
            If CmdLine Then
                EventLogOI.WriteEntry("EASBulkTrans execution halted: " & _
                "Error filling dataset with Text/CSV file (" & FPath & FileOI & "). " & ex.Message)
                End
            Else
                MsgBox("Error filling dataset with Text/CSV file (" & FPath & FileOI & "). " & ex.Message & ": " & ex.StackTrace)
            End If
        End Try
        'sr = Nothing

Open in new window

jcgeAuditAsked:
Who is Participating?
 
Priest04Connect With a Mentor Commented:
hmmm... then we would need to parse each value and see if its a date. I have coded wuthout compiler, so there could be syntax errors.
public sub CheckDateColumnIndex(string value as Integer
dim fields() as string = value.Split(Chr(9))
 
    dim temp as DateTime
    for i as integer = 0 to fields.GetUpperBound(0)
        if DateTime.TryParse(s, out temp) then
            return i
        endif
    next
 
    return -1
end sub
 
' ........
 
Try
    Dim start as boolean
    dim dateColumnIndex As integer = -1
 
    sr = New System.IO.StreamReader(frm.txtFilePath.Text & frm.txtFileName.Text, System.Text.Encoding.Default, True)
 
    If SepOI = "TAB" Then
        Do
            LineOI = sr.ReadLine
    
            If Not (LineOI Is Nothing) And (InStr(LineOI, Chr(9)) > 0) Then
                if (start) then
                    dim dateColumnIndex As integer = -1
                    dateColumnIndex = CheckDateColumnIndex(Line01)
                    start = false
                endif
 
                'MsgBox("before fix " & LineOI)
                'LineOI = FixLine(LineOI)
                LineOI = Replace(Replace(LineOI, Chr(34), ""), "$", "")
                'MsgBox("after fix " & LineOI)
                Fields = Split(LineOI, Chr(9))
                dr = dt.NewRow()
                For f = 0 To Fields.Length - 1
                    If f < c - 1 Then 'Only works with first 40 columns in the file
                        if (f=dateColumnIndex) then
                            Dim tempDate As DateTime
                            DateTime.TryParse(str(dateColumnIndex), tempDate)
                            ' if first column type in datatable is date then uncomment bellow line, and comment 2nd and 3rd
                            ' dr(dateColumnIndex) = tempDate
                            Dim result As String = tempDate.ToString("M/d/yyyy")) ' now you get the format 9/8/2008
                            dr(dateColumnIndex) = result
                        else
                            dr(f) = Fields(f)
                        end if
                    End If
                Next
                dt.Rows.Add(dr)
            End If
        Loop Until LineOI Is Nothing
    Else ' .... rest of the code

Open in new window

0
 
Priest04Commented:
When reading line from file, and when TAB is a delimiter, after Split command code will retrieve array with 3 elements:

September 8, 2008
Widgit
Jones

to convert September 8, 2008 to 9/8/2008 format, you will need to parse string to date, and then to format it the way you like. Example:


While Not sr.EndOfStream
    Dim temp As String = sr.ReadLine()
    Dim str() As String = temp.Split(Chr(9))
    Dim tempDate As DateTime
 
    DateTime.TryParse(str(0), tempDate)
    Dim result As String = tempDate.ToString("M/d/yyyy")) ' now you get the format 9/8/2008
End While

Open in new window

0
 
jcgeAuditAuthor Commented:
Where does the code go?  at the beginning?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Priest04Commented:
I didnt give you the working code, I just gave you an example how it can be done. I dont know what is the datatable structure, but you could try it like this ( I assume the type of the first column that receives the date is string. IF not, then you will set dr(f) to tempDatein the first
For f = 0 To Fields.Length - 1
    If f < c - 1 Then 'Only works with first 40 columns in the file
        if (f=0) then
            Dim tempDate As DateTime
            DateTime.TryParse(str(0), tempDate)
            ' if first column type in datatable is date then uncomment bellow line, and comment 2nd and 3rd
            ' dr(0) = tempDate
            Dim result As String = tempDate.ToString("M/d/yyyy")) ' now you get the format 9/8/2008
            dr(0) = result
        else
            dr(f) = Fields(f)
        end if
    End If
Next

Open in new window

0
 
Priest04Commented:
Ah sorry, I have made a mistake when I copied code. Line number 5 should be like this


DateTime.TryParse(Fields(0), tempDate)

Open in new window

0
 
jcgeAuditAuthor Commented:
closer... the problem is that the date is not always in column 1.  It is in a variable column named DateColVal.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.