asked on

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

Priest04

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

jcgeAudit

ASKER

Where does the code go? at the beginning?

Priest04

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

Priest04

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

jcgeAudit

ASKER

closer... the problem is that the date is not always in column 1. It is in a variable column named DateColVal.

ASKER CERTIFIED SOLUTION

Priest04

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial