VBA CSV File Import Date Issue

K

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
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
Code: 
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
 
Sort by date Sort by votes
To change dates in column E from E6 onwards:

Code: 
Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
Set DtRange = Range("E6:E" & Cells(Rows.Count, "E").End(xlUp).Row)
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
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
xenou said:
Try:

Code: 
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)?
Click to expand...

My sample data is at work, so i will test this on Monday.

One question dont i need to have this in DMY format as i want date conversion in DMY format?


xlMDYFormat</pre>
 
Upvote 0
I will test on Monday and let you know of result.


syntaxed said:
To change dates in column E from E6 onwards:

Code: 
Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
Set DtRange = Range("E6:E" & Cells(Rows.Count, "E").End(xlUp).Row)
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
Click to expand...
 
Upvote 0
My sample data is at work, so i will test this on Monday.

One question dont i need to have this in DMY format as i want date conversion in DMY format?


xlMDYFormat
Click to expand...

You want the setting to match what is in the csv file (the source file). Actually, what is the formatting of that data...?

If there is a difference between my approach and that of syntaxed is that I would prefer to address the problems during import, rather than fixing them after the data is in Excel. If possible, that is. I'm always paranoid Excel will wrongly read a date such as 6/5/2010. If it sees this date, does it become Jun 5 or May 6? You may want to test such cases with your import, in fact.

ξ
 
Upvote 0
Xenou, the dates in CSV file itself is correct, its only when the code imports to Excel it turns weird where some dates are correct in DMY format and some in MDY format.


xenou said:
You want the setting to match what is in the csv file (the source file). Actually, what is the formatting of that data...?

If there is a difference between my approach and that of syntaxed is that I would prefer to address the problems during import, rather than fixing them after the data is in Excel. If possible, that is. I'm always paranoid Excel will wrongly read a date such as 6/5/2010. If it sees this date, does it become Jun 5 or May 6? You may want to test such cases with your import, in fact.

ξ
Click to expand...
 
Upvote 0
Okay, that's what I thought. What is the date format in the csv file? What happens to dates such as 6/5/10 (is it read correctly as May 6 or incorrectly as Jun 5).
 
Upvote 0
Hi, i did a test using your code, but it did not reverse the highlighted cells

Excel Workbook
E
131/12/2010
212-Feb-10
312-Mar-10
412-Jun-10
512-Jul-10
612-Aug-10
712-Oct-10
814/12/2010
916/12/2010
1022/12/2010
1129/12/2010
1231/12/2010
1331/12/2010
1412-Mar-10
1512-Jun-10
1612-Aug-10
1714/12/2010
1816/12/2010
1923/12/2010
2029/12/2010
2131/12/2010
2231/12/2010
2312-Jul-10
2417/12/2010
2531/12/2010
Sheet1







syntaxed said:
To change dates in column E from E6 onwards:

Code: 
Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
Set DtRange = Range("E6:E" & Cells(Rows.Count, "E").End(xlUp).Row)
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
Click to expand...
 
Upvote 0
xenou said:
Okay, that's what I thought. What is the date format in the csv file? What happens to dates such as 6/5/10 (is it read correctly as May 6 or incorrectly as Jun 5).
Click to expand...

Xenou the code did not work on the highlighted cells, i need these to be dd/mmm/yy

Excel Workbook
E
131/12/2010
212-Feb-10
312-Mar-10
412-Jun-10
512-Jul-10
612-Aug-10
712-Oct-10
814/12/2010
916/12/2010
1022/12/2010
1129/12/2010
1231/12/2010
1331/12/2010
1412-Mar-10
1512-Jun-10
1612-Aug-10
1714/12/2010
1816/12/2010
1923/12/2010
2029/12/2010
2131/12/2010
2231/12/2010
2312-Jul-10
2417/12/2010
2531/12/2010
Sheet1
 
Upvote 0
Hi, i have taken the below code from cpearson website, now i managed to import the CSV file using the below Sep and then in excel i did Text to columns, separate by comma, that seems to have done the job. Question how do i get cpearson code to only import data from CSV file from row 4 onwards?


Sep:="|"



Code: 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ImportTextFile
' This imports a text file into Excel.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.row

Open FName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ImportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub
 
Sub DoTheImport()
ImportTextFile FName:="C:\Test.txt", Sep:="|"
End Sub
 
Upvote 0
This is hatchet job but should work:
Code: 
Dim iLineCount As Long  '//Add this to your variable declarations
While Not EOF(1)

    Line Input #1, WholeLine
[COLOR="Blue"]    iLineCount = iLineCount + 1
    If iLineCount > 4 Then[/COLOR]
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
[COLOR="Blue"]    End If[/COLOR]

Wend

I should still like to know what format the text file dates are in. It's really impossible to give good advice otherwise. Your dates in post 17, by the way, look fine. All you should really need to do is format the cells in Excel (a rather simple adjustment, whether in the code or after it finishes)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
648
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA code clean up
Replies
9
Views
593
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
932
epactheactor
E

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top