[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-11
8
Medium Priority
?
153 Views
Last Modified: 2013-11-25
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

0
Comment
Question by:jcgeAudit
  • 4
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Priest04
ID: 22935441
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
 

Author Comment

by:jcgeAudit
ID: 22935628
Where does the code go?  at the beginning?
0
 
LVL 18

Expert Comment

by:Priest04
ID: 22935781
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Expert Comment

by:Priest04
ID: 22935793
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
 

Author Comment

by:jcgeAudit
ID: 22936025
closer... the problem is that the date is not always in column 1.  It is in a variable column named DateColVal.
0
 
LVL 18

Accepted Solution

by:
Priest04 earned 1000 total points
ID: 22936406
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month19 days, 21 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question