CSV Import in VBA Not Treating DateTime Values Correctly

[shellp]

Hello

The code below imports a csv file into the Excel document at the last blank row and then sorts the various columns and also ensures that the formula in columns AA:AI are also copied down with the new data.

All works well on my computer BUT when importing data from another computer the date/time fields are being imported as text. The data that is in the document is m-dd-yyyy hh:mm:ss. When a new month is imported the date shows as 05/20/2020 11:17 but when I right click on it, the format is described as general. However, if I use isnumber(A2), it is false for new data but true for the old so I assume it isn't a number or date.

I even tried to convert the existing data to the mm/dd/yyyy hh:mm format but the new data still comes across as above and not recognized as a date. I want to avoid having to manipulate the csv data prior to importing if possible...can anything be added to the existing code so it formats accordingly? Thanks for any and all assistance.

Sub append_csv_file()
Dim csvfilename As Variant
Dim destcell As Range
Dim i As Variant
Dim ws As Worksheet
Dim Table As PivotCache

Set ws = Worksheets("Raw")

'this is setting an actual value for the row you want to add to
Set destcell = Worksheets("Raw").Cells(Rows.Count, "A").End(xlUp).Offset(1)

csvfilename = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)

If csvfilename = False Then Exit Sub

Worksheets("Raw").Select
With destcell.Parent.QueryTables.Add(Connection:="Text;" & csvfilename, Destination:=destcell)
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(3, 3, 3, 2, 2, 2, 2, 3, 2, 2, 2, 2, 3, 3, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With

destcell.Parent.QueryTables(1).Delete

'sort descending order
With Worksheets("Raw").Sort
.SortFields.Clear
.SetRange Range("A2:X" & Cells(Rows.Count, "W").End(xlUp).Row)
.SortFields.Add Key:=Range("E2"), Order:=xlAscending
.SortFields.Add Key:=Range("G2"), Order:=xlAscending
.SortFields.Add Key:=Range("T2"), Order:=xlAscending
.SortFields.Add Key:=Range("S2"), Order:=xlAscending
.SortFields.Add Key:=Range("C2"), Order:=xlAscending
.Header = xlNo
.Apply
End With

ws.Range("AA2:AI2").AutoFill Destination:=ws.Range("AA2:AI" & Cells(Rows.Count, "W").End(xlUp).Row)
End Sub

 

[Yongle]

Before amending the code, please test something by formula

This formula converts TEXTdate to an Excel date serial number
=DATEVALUE(A1)

Use a new column and apply the above formula against the column containing Textdates, and format the column with required date format
Does Excel properly convert ALL the dates?

Which column(s) contain dates imported as text? Should the original values be overwritten?

 

[shellp]

Good call, Yongle, it seems the existing data has a #Value! when using the datevalue(A2) and the newly imported is correctly showing a number. So I need to do the opposite and set up the current data like a date/time. I'll try that and see what happens!

EDIT: I am wrong...this means the new data is not a date and the old data is. So the data is not being imported as a date/time value.

 

[Yongle]

Which column(s) contain dates imported as text? Should the original values be overwritten?

Which columns should be converted from text to date by the code?
Are there headers in row 1?

 

[shellp]

The date/time columns are A, B, C and N; date only column is H. There are headers in row 1 that are correctly not being imported. Thanks.

 

[Yongle]

Test on a copy of your data
Data is initially dumped to temporary sheet and the 4 date columns converted (you may want to format those columns differently)

Untested

Sub append_csv_file()
    Dim csvfilename As Variant, destcell As Range, i As Variant, ws As Worksheet, Table As PivotCache
    Dim temp As Worksheet, lr As Long, r As Long, c As Variant
    csvfilename = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
    If csvfilename = False Then Exit Sub
   
'add new data to temporary sheet
    Set temp = Sheets.Add
    Set destcell = temp.Cells(1, 1)
    With destcell.Parent.QueryTables.Add(Connection:="Text;" & csvfilename, Destination:=destcell)
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(3, 3, 3, 2, 2, 2, 2, 3, 2, 2, 2, 2, 3, 3, 2, 2, 2, 2, 2, 2, 2, _
    2, 2, 2, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

'convert dates in specified columns
    lr = Split(temp.UsedRange.Address, "$")(4)
    For Each c In Array("A", "B", "C", "N")
        For r = 1 To Split(temp.UsedRange.Address, "$")(4)
            On Error Resume Next
            temp.Cells(r, c).Value = CDate(temp.Cells(r, c).Value)
            On Error GoTo 0
        Next r
    Next c
       
'now copy data to sheet "Raw"
    Set ws = Worksheets("Raw")
    Set destcell = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1)
    temp.UsedRange.Copy destcell
    ws.Select
       
'sort descending order
    With ws.Sort
    .SortFields.Clear
    .SetRange ws.Range("A2:X" & ws.Cells(Rows.Count, "W").End(xlUp).Row)
    .SortFields.Add Key:=Range("E2"), Order:=xlAscending
    .SortFields.Add Key:=Range("G2"), Order:=xlAscending
    .SortFields.Add Key:=Range("T2"), Order:=xlAscending
    .SortFields.Add Key:=Range("S2"), Order:=xlAscending
    .SortFields.Add Key:=Range("C2"), Order:=xlAscending
    .Header = xlNo
    .Apply
    End With
   
    ws.Range("AA2:AI2").AutoFill Destination:=ws.Range("AA2:AI" & Cells(Rows.Count, "W").End(xlUp).Row)

'delete temporary sheet
    Application.DisplayAlerts = False
    temp.Delete
    Application.DisplayAlerts = True
End Sub

 

[shellp]

You are a brilliant man, Yongle!! this worked perfectly, thanks so very much!!

 

[Yongle]

Thank you for your feedback

 

[shellp]

Sorry, revisiting this....how do I import a single column (H) with the date format of yyyy-mm-dd? Thanks!