Hi Everyone,

I am having an issue with a small app I am writing.

It takes a huge report from one of our clients, extracts a few columns, makes date calculations and spits out a csv file for our systems to update the databases.

The problem is I am in the UK, the report comes in from the US, and all the dates are in American format.

When reading the report in, any dates that can be cast to a UK date format are read, all others are ignored.

I need to be able to read all the dates in, specifying that they are ALL in US format, so I can then perform dateAdd functions on them.

I got close in Excel VBA, but then it was managing to convert all dates it knew were US format, and left all the others alone strangely.

I then thought the simplest way to do it would be to code a small VB app to do it:

Dim sheetName, outStream As String
        Dim col1, col2, col3, col4 As String
        Dim readRow As Boolean = False
        With OpenFileDialog1
            .Title = "Select Report"
            .Filter = "Microsoft Excel Files (*.xls)|*.xls"
            .InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
            .Multiselect = False
        End With
        OpenFileDialog1.ShowDialog()
        TextBox1.Text = OpenFileDialog1.FileName
        Dim xlConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TextBox1.Text & ";Extended Properties=""Excel 8.0;HDR=Yes;""")
        xlConn.Open()
        Dim xlSchema As DataTable = xlConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
        Dim index As Integer = 0
        For Each rowtable As System.Data.DataRow In xlSchema.Rows
            If index = 0 Then
                sheetName = rowtable("TABLE_NAME").ToString
            End If
            index += 1
        Next
        Dim xlCmd As OleDbCommand = New OleDbCommand("SELECT [Fld1], [Fld2], [Fld3], [Fld4], [Fld5] FROM [" & sheetname & "] WHERE [Fld5] <> ''", xlConn)
        Dim xlDr As OleDbDataReader = xlCmd.ExecuteReader
        While xlDr.Read
            readRow = True
            If Trim(xlDr.GetString(4)) = "STAT1" Then readRow = False
            If Trim(xlDr.GetString(4)) = "STAT2" Then readRow = False
            If Trim(xlDr.GetString(4)) = "STAT3" Then readRow = False
            If readRow = True Then
                If Trim(xlDr.GetValue(0).ToString) <> "" Then
                    If Mid(Trim(xlDr.GetValue(0).ToString), 1, 1) = "4" Then
                        col1 = Trim(xlDr.GetValue(0).ToString)
                        col2 = xlDr.GetValue(1).ToString
                        If Trim(xlDr.GetString(2)) = "CFR" Then
                            col3 = "PPD"
                        Else
                            col3 = "CLT"
                        End If
                        outStream = outStream & col1 & "," & col2 & "," & col3 & vbCrLf
                        TextBox2.Text = outStream
                        Me.Refresh()
                    End If
                End If
            End If
        End While
        xlDr.Close()
        xlConn.Close()
        MsgBox("Finished!")
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.