Formating Issues when creating and writting to CSV file format

[RineshSingh]

Hi All,
I need some assistance please.

I am experiencing a formatting issue whenever I attempt to save a file as a CSV. For example, the data contains a column for date of birth which is in the below format:

dd-MM-yy

When I write this to CSV, the format changes as per below:

dd MM yyyy

The dashes are dropped and yy is changed to yyyy which is not what I want.

I use the below to get the collection as CSV

I then use the Append to Text File to write the data item to csv.

Thank you

[Walter.Koller]

My assumption is the data is written correctly to the CSV but Excel is doing some conversions when opening the file.

I did some test by creating a CSV file with the value 05-10-22 and then opening it in Excel to see 05.10.2022, which is my local standard format in Windows/Office.
Try to open the CSV in a text editor to see the actual content.

There are several options on how to change the behavior of Excel when opening CSV files. 
Maybe the easiest, without long explanation and permanently changing the behavior of Excel, is to open the Data menu in Excel and select 'import from text/CSV'. This will guide you through the steps and shows you what is imported and converted.

[RonanConsidine]

Hi Rinesh,

This issue will be occurring because you are trying to create a CSV. A Comma Separated Value document is just that, it has very little formatting or inbuilt intelligence to allow special characters etc.

Do you need the data to be in this format AND it to be in a csv? If the document can be in an xlsx format this would be possible.

Best Regards,

Ronan

[RineshSingh]

Thanks all for your suggestions, I managed to resolve this by changing the "system date" format on my PC which fixed the formatting issue.