VBA CSV File Import Date Issue

[Katy Jordan]

Hi, i have a code which imports CSV file data into excel, the code imports the data successfully but the dates in the date column change to US format, not all but most of them, how can i get around this.

Sample CSV Data

<TABLE style="WIDTH: 129pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=171 border=0 x:str><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=66 height=17>Date</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=105>Amount</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="43012">4-Oct-17</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="9976364"> 9,976,364.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="42798">4-Mar-17</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="11772997"> 11,772,997.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40847">31-Oct-11</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1884984"> 1,884,984.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="44196">31-Dec-20</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="-20000000">- 20,000,000.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="41125">4-Aug-12</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="-250000000">-250,000,000.00 </TD></TR></TBODY></TABLE>

rngFile = C:\Test\RG

This is the code i have

Sub Test()
 
Dim strFileName          As String
Dim lstRow               As Long
Dim lstRowSrc            As Range
Dim lstSrcCellRwNum      As Long
Dim shtDst               As Excel.Worksheet
Dim shtSrc               As Excel.Worksheet
Dim wkbThis              As ThisWorkbook
Dim wkbSrc               As Workbook
 
strFileName = Range("rngFile") & ".csv"
    If Dir(strFileName) = "" Then
            MsgBox prompt:="The File does not exist", _
            Buttons:=vbOKOnly + vbInformation
        GoTo ExitRoutine
      Else
         Set wkbSrc = Workbooks.Open(strFileName)
         Set shtSrc = wkbSrc.Worksheets(1)
         Set wkbThis = ThisWorkbook
         Set shtDst = wkbThis.Sheets("Test")
    End If
     
    With shtSrc
        Set lstRowSrc = .Cells(.Rows.Count, "A").End(xlUp)
                lstSrcCellRwNum = lstRowSrc.Row
         With .Range("A2:M" & lstSrcCellRwNum)
             .Copy
             shtDst.Range("A6").PasteSpecial xlPasteValues
         End With
    End With
End Sub

 

[Katy Jordan]

*bump*

 

[xenou]

It might work to open the file with the OpenText method. This allows you to specify that a certain column is a date, and also that it is YMD, DMY, MDY, or what have you. If this is not familiar to you, change the extension of the file to .TXT and open it using the open text wizard (with the macro recorder on). Feel free to post the recorded code here for further assistance.

ξ

 

[Katy Jordan]

It might work to open the file with the OpenText method. This allows you to specify that a certain column is a date, and also that it is YMD, DMY, MDY, or what have you. If this is not familiar to you, change the extension of the file to .TXT and open it using the open text wizard (with the macro recorder on). Feel free to post the recorded code here for further assistance.

ξ

So you saying something like this, the date is in Col E6:E, do i need to change any of the below?

Workbooks.OpenText Filename:= _
    varFileName, Origin:= _
    437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, xlMDYFormat), Array(2, 1), _
    Array(3, 1)), TrailingMinusNumbers:=True

 

[xenou]

Yes. But this code is saying the dates are in the first column of your text file. Array(1,xlMDYFormat) means the first column is MDY. The rest don't matter since all the 1's for the other columns is the default (general format).
For column E I might have expected: Array(Array(1,1),Array(2,1),Array(3,1),Array(4,1),Array(5,xlMDYFormat))
Again. most of the columns have a "1" for general format.
Proof is in the pudding though!

Workbooks.OpenText Filename:= _
    varFileName, Origin:= _
    437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, _
    FieldInfo:=Array(Array(1, xlMDYFormat)), TrailingMinusNumbers:=True

 

[syntaxed]

I don't know if this will help, but I experienced a similar problem when importing CSV files once. Some dates were being translated as US format dates (I use UK format) with month and date reversed, and others were remaining in "General" number format. I selected the date range and ran the following code and all the dates were converted to my default UK format.

Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Cells.Count = 1 Then
    Set DtRange = ActiveCell
Else
    Set DtRange = Selection
End If
With Application
    On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
    For Each oCell In DtRange.SpecialCells(xlConstants)
        oTxt = oCell.Text
        If UBound(Split(oTxt, "/")) = 2 Then
            oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/" & Split(oTxt, "/")(2))
        End If
    Next oCell
End With
End Sub

 

[xenou]

I.e, this might work for dates in column 1 ("A"):

Workbooks.OpenText Filename:= _
    varFileName, Origin:= _
    437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, _
    FieldInfo:=Array(Array(1, xlMDYFormat)), TrailingMinusNumbers:=True

And this might work for dates in column 5 ("E"):

Workbooks.OpenText Filename:= _
    varFileName, Origin:= _
    437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, _
    FieldInfo:=Array(Array(5, xlMDYFormat)), TrailingMinusNumbers:=True

As you see, we can ignore many of the false default values for delimiter types, and accept the default data types for most columns (I think...)

 

[Katy Jordan]

Is there a way for the code to select the Date range rather than me manually doing it, so code to select E6:E?

I don't know if this will help, but I experienced a similar problem when importing CSV files once. Some dates were being translated as US format dates (I use UK format) with month and date reversed, and others were remaining in "General" number format. I selected the date range and ran the following code and all the dates were converted to my default UK format.

Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Cells.Count = 1 Then
    Set DtRange = ActiveCell
Else
    Set DtRange = Selection
End If
With Application
    On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
    For Each oCell In DtRange.SpecialCells(xlConstants)
        oTxt = oCell.Text
        If UBound(Split(oTxt, "/")) = 2 Then
            oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/" & Split(oTxt, "/")(2))
        End If
    Next oCell
End With
End Sub

 

[Katy Jordan]

Xenou, what have i dont wrong here, when i try to put this in my code this turns to red.

Set wkbSrc = Workbooks.OpenText Filename:= _
                        strFileName, Origin:= _
                         437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
                        ConsecutiveDelimiter:=False, Tab:=True, _
                         FieldInfo:=Array(Array(5, xlMDYFormat)), TrailingMinusNumbers:=True

I.e, this might work for dates in column 1 ("A"):

Workbooks.OpenText Filename:= _
    varFileName, Origin:= _
    437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, _
    FieldInfo:=Array(Array(1, xlMDYFormat)), TrailingMinusNumbers:=True

And this might work for dates in column 5 ("E"):

Workbooks.OpenText Filename:= _
    varFileName, Origin:= _
    437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, _
    FieldInfo:=Array(Array(5, xlMDYFormat)), TrailingMinusNumbers:=True

As you see, we can ignore many of the false default values for delimiter types, and accept the default data types for most columns (I think...)

 

[xenou]

Try:

Workbooks.OpenText Filename:= blahblahblah
Set wkbSrc  = ActiveWorkbook

Do you have some sample data as it appears in the text file (not as it appears in Excel)?