CSV Import in VBA Not Treating DateTime Values Correctly

shellp

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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.

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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.
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Thank you for your feedback
 

shellp

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Sorry, revisiting this....how do I import a single column (H) with the date format of yyyy-mm-dd? Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,786
Messages
5,638,307
Members
417,020
Latest member
MSVII

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top