Excel to CSV - Date format with wrong output

[ZéPiki]

Dear all,

I have created an excel which transform my xls file into a csv according with header needs.
One of the values that I need to export on my CSV is the date which on my xls file is DD/MM/YYYY, but when I export it for an CSV file it become MM/DD/YYYY

below the line where I export the date into the CSV file:

cs.Range("E2") = bs.Range("D8").Value 'Date
so basically I'm directly passing the value of my D8 cell into the E2 destination CSV file.

I tried to change .Value to .Text(date, "DD/MM/YYYY"), but I have a mismatch error.

Any tips to overcome this issue?

Thank you in advance,

[Eastw00d]

Hi, how about below or after above statement:

Please Login or Register  to view this content.

Cheers
Erwin

[Haluk]

CSV files are actually plain text files and do not support formatting. So, everything should be done at the source file before importing.

[CK76]

CSV, unlike Excel file, is a flat file and will not hold any formatting or schema info. Those will be lost when file is saved.

How you pass data from Excel to CSV will depend on CSV type. CSV is highly environment/Source system dependent.

In your case, it is likely that your system uses region setting that uses U.S. format as default, and your Excel has different date format (culture) applied.

It may be easier to read csv content into some sheet (via import wizard / code), then update values from your Excel file. And then save the sheet as CSV.

If you need further help, I'd recommend uploading sample workbook and csv file. Clearly indicating your desired result. (see yellow banner at top).

[ZéPiki]

Thanks a lot guys it was helpfull

[Haluk]

You're welcome and thx for the feedback.