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!")