Incorrect date format when importing .CSV

[PaulBarter]

Using Excel 2000

I am writing a macro that will open a selected .csv file and extract the data I want into the current spreadsheet.

The problem is that the dates in the .csv file get formated incorrectly.

All the dates in the file are in the format "dd/mm/yy" (including the quotes). If the file contains "25/11/05" then it converts to 25th November correctly, however if it contains "04/11/05" then it converts to 11th April 2005 (i.e. it assumed the first number is month if <= 12. How do I get it to treat the first 2 digits as day regardless.

It seems that if I manually open the .csv file then it reads it in correctly, however if a macro use a .Open method (or even .OpenText method) then it fails as above.

It seems the the .csv suffix is causing the problem as if I rename the file with .txt suffix and use the .OpenText method then all is OK.

Any ideas.

 

[PHV]

OpenText is a method of which object in Access VBA ?
I guess you're playing with Excel VBA, don't you ?
If so then use the macro recorder when opening manually the csv file.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PaulBarter]

I'm using Workbooks.Opentext in excel VBA.

Yes I used the recorder originally when I started writing the macro. Using .OpenText on a .csv file seems to ignore all the FieldInfo parameters.

For example run the following code

Sub ReadTextIn()
Dim strFole As String
strFile = "text.csv"
Workbooks.OpenText Filename:=strFile, _
    Origin:=xlWindows, _
    StartRow:=1, _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=True, _
    Space:=False, _
    Other:=False, _
    FieldInfo:=Array(Array(1, 4), Array(2, 2))
End Sub

with an input file text.csv consisting of

Date, Name
"29/03/2006","Record 1"
"06/04/2006","Record 2"
"12/04/2006","Record 3"
"15/04/2006","Record 4"

and records 2 & 3 will be converted incorrectly. Rename the file to "text.txt" and change the second line of the macro accourdingly then all is OK.

Help!

 

[PaulBarter]

OOPS - sorry I seem to be in the wrong forum somehow.

I'll repost in the correct one