CODEWITHSUNDEEP
vbaexceldatedatetime
Absinthe
Absinthe

Reputation: 3391

Excel VBA importing .txt file results in wrong date format

I'm importing a text file (not .csv, a .txt) which is tab delimited. The first column contains dates, some are in dd/mm/yyyy format, others are in dd/mm/yyyy hh:mm:ss format.

When running the following code a few of the dates come out in mm/dd/yyyy format. There is nothing unusual about the ones that do, it appears to happen at random (some have the time, some dont but either way the source is still day-month-year)

Sub LQMTrend()

Dim fp, textLine As String
Dim iRow As Integer
Dim lineArr() As String
Dim ws As Worksheet

Set ws = Sheets("Data")

iRow = 1

fp = "//srv57data1\product_support\xChange\Beam Profile Image Tool\LQM Reviews\Log files\Log file.txt"

Open fp For Input As #1

Do Until EOF(1)
    Line Input #1, textLine
    lineArr = Split(textLine, vbTab)
    For x = 0 To UBound(lineArr)
        ws.Cells(iRow, x + 1) = lineArr(x)
    Next x
    iRow = iRow + 1
Loop

Close #1

I've tried declaring lineArr as a variant but it makes no difference. Any ideas?

Thanks

Edit: I appreciate this is similar to Excel VBA: importing CSV with dates as dd/mm/yyyy but the simplest answers are different in each case - for CSV files the 'use local date setting' import option solves the problem, this is not available when opening .txt files, the date must be converted on the fly using CDate or similar. Hope this helps clarify.

Upvotes: 1

Views: 4036

Answers (4)

ASH
ASH

Reputation: 20302

You should change the date AFTER you do the import. A text file, like a CSV file, has no formatting whatsoever.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

You need to convert your string date to a "real date" (serial number) before writing it to your worksheet. Here is one way to do that. Alter the array element to reflect the proper column in your original file.

'convert date to proper date before writing to worksheet
'Assuming date is in column 3

Dim DT As Date, TM As Date, V As Variant

V = Split(lineArr(2))
'is there a time segment
    If UBound(V) = 1 Then
        TM = TimeValue(V(1))
    Else
        TM = 0
    End If

'convert the date segment to a real date
V = Split(V(0), "/")
DT = DateSerial(V(2), V(1), V(0)) + TM

'write it back to linearr
lineArr(2) = DT

Upvotes: 0

SilentRevolution
SilentRevolution

Reputation: 1513

As Assylias mentioned, some dates can be ambiguous. To Excel a date is nothing but a formatted number which represents the number of days since 01/01/1900, today (March 3, 2016) to excel is nothing more than 42447. When using the number, there can be no ambiguity about the date format.

I Suggest changing

ws.Cells(iRow, x + 1) = lineArr(x)

to 

With ws.Cells(iRow, x + 1)
    If x = 0 Then
        .Value = CLng(CDate(lineArr(x)))
        .NumberFormat = "mm/dd/yyyy;@"
    Else
        .Value = lineArr(x)
    End If
End With

Upvotes: 2

Florent B.
Florent B.

Reputation: 42518

Excel first tries to convert a date string to the format of the local setting. If it fails, like when the month is superior to 12, it will then inverse the month and the days. Since you are dealing with the two types of formats, you best option is probably to parse the dates yourself:

Sub Macro1()
  Dim arr() As String
  Dim mydate As Date

  ' US format 6 may 2015
  arr = Split("05/06/2015", "/")
  mydate = DateSerial(Year:=arr(2), Month:=arr(0), Day:=arr(1))

  ' UK format 6 may 2015
  arr = Split("06/05/2015", "/")
  mydate = DateSerial(Year:=arr(2), Month:=arr(1), Day:=arr(0))

End Sub

Upvotes: 3

Related Questions