Hi Dave,

Thanks for your informations.

As you said in the below that its better to convert the .csv file to .txt
file, the problem here is the txt file allways separate the columns based on
Comma, so suppose if there are two columns like Names and Amounts, and if
any name contain comma, the name will be separated to next column where
amount was suppose to be.

"Dave Peterson" wrote:

I'm surprised that there was any difference in the way the data was treated.

In my simple tests (using USA date settings (mdy order)), both worked the same
way.

But (if I recall correctly), opening CSV files via code will use USA settings.

I wouldn't do it. Instead I'd rename (or copy) the .csv files to .txt (manually
or in code) and use .opentext to open the file(s). Then I could specify each
field the way I wanted.

I'd be very concerned about date fields coming in as date fields--but not
representing the date in the actual source (01/02/03 could come in as Jan 2,
2003 or Feb 1, 2003 or ...).

I'd want to make sure all the ambiguous dates would be brought in correctly.

Ranjith Kurian wrote:

I have a lot of csv file, in that file the date column contain both text and
date values, but when i change my system settings to UK and if i manually
change the date format using custom type "dd-mmm-yy", all the date and text
value of that column will easily change to dd-mmm-yy, but when i created a
macro to do the same thing, the problem iam facing here is when it open each
csv files the date is getting changed(the column contains both text and date
values)

when i open the file using the below code it opens the file in a actual
format(date values will not change)

ChDir "C:\Ranjith Report\FGA DEBT\Raw"
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\ABC.csv"

but when i open the file using the below code it opens in a different
format(date value changes)

arrWorkBook = Array("ABC.csv","DEF.csv")
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\" &
arrWorkBook(intTemp)

--

Dave Peterson
.