Date format incorrect from Text File

[Glaswegian]

Hi again

I'm importing a large text file, 120,000 rows, using Chip Pearson's code - this all works fine. However, I have found a problem with some dates. Some, not all, appear in Excel as US formatted dates - mm/dd/yyyy. Looking at the original data in the text file, the format is as I would expect - UK format - dd/mm/yyyy. In Excel, if I format the column in question to General, the US style dates change to a numerical value - which suggests to me they are being seen as real dates. The UK style dates remain unchanged - which suggests they are being seen as text. Dates appear in the formula bar exactly as they appear on the worksheet. I've already tried to format the column using some code at the end of the main import routine, but it does not make any difference. Neither does manually formatting once the import routine is finished. I could use a formula to change the US style values, but since there is no pattern to this, it would have to be done manually (I think) - not a job that appeals to me. No matter what I do, I cannot make the US style dates appear as they should - UK style dates. Is this a problem with the text file, with Excel or with me? Any help or guidance greatly appreciated.

[mdmackillop]

Can you post what you have with a small sample of the text file with troublesome dates?

[Glaswegian]

Hi Malcolm

Couple of (slightly edited) sample files attached.

Thanks for your help.

[mdmackillop]

Try this

 
Option Explicit
Sub Macro2()
    Dim txtfile As String
    txtfile = ActiveWorkbook.Path & "\" & "Sample Text File.txt"
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & txtfile, Destination:=Range("A1"))
        '.Name = "Sample Text File"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 4, 1, 1, 1, 1, 1, 1, 4, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With
End Sub

[Glaswegian]

Hi Malcolm

Thanks. I do have some questions...

I removed some columns before posting the sample files - do I just add in the relevant data type to the array? Should your code be run on the text file instead of Chip's routine? Or should I run your code first, then use Chip's code?

[mdmackillop]

Run my code from Excel. It is basically a recorded macro using Data Import in which I changed any date columns to dmy format.

[Glaswegian]

Ah...thanks.

[Glaswegian]

Certainly fixes the date issue but I now need to add this in (in some way) to Chip's code - the text file is 120,000 rows. I also have code that fixes some other format issues.

Thanks for your help.

[mdmackillop]

Which of Chip's codes are you using and are you 2007 or earlier?

[Glaswegian]

XL2002 - it's the ImportBigTextFile routine.

http://www.cpearson.com/excel/ImportBigFiles.htm

[mdmackillop]

If Colndx + C_START_COLUMN <= WS.Columns.Count Then
    Dim tmp
    tmp = Arr(Colndx)
    If IsDate(tmp) Then tmp = DateValue(tmp)
        WS.Cells(RowNdx, Colndx + C_START_COLUMN).Value = tmp 'Arr(Colndx)
    Else
        TruncatedCount = TruncatedCount + 1
        Exit For
   End If

[Glaswegian]

Malcolm - many thanks indeed - that works perfectly!