Revisiting - Errors with Importing CSV File Date/Time data

shellp

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Yongle was kind enough to write the code below which works BUT for some reason some of the data in the csv file is not formatting correctly. I think it is the data versus Yongle's code because even importing the data file using data/import/text produces the same erroneous rows of data occur. I checked while importing and the field is formatted like the rest of the data and looks like 07/06/20 16:58 (mm/dd/yy) but once imported is 2007-06-20 16:58. Looking more closely at the data and it seems it is the single digit days i.e. 06, 07, 08 etc. are the ones causing this issue. Any way to fix this? Thanks.

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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

shellp

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

I seem to have figured out some of the issue which is that I changed the TextFileColumnDataTypes to 4 (for DMY) from 3 (MDY) so that solves it for all but the birthdate column (H) (which isn't currently in the code above but I added it in my testing). But what is happening there is because the report is only providing 2 numbers for the year so some of the birth years are showing as 2029 instead of 1929. Any assistance to fix that via the code? Thanks very much.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,263
Messages
5,600,597
Members
414,392
Latest member
Josephkaipa

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