Finance | Project Operations, Human Resources, ...

Once DateTime field value pass to csv File from x++,'AM','PM' value hiding

(0) Share

Report

Posted on by Sanjay kumar singh

240

Hi Team,

I have developed a functionality,to export to csv from x++.while value got expoted,AM/PM value of Time is getting hidden.

1222.csvFile.png

Code Part for csv is

3757.Code1.png

I have the same question (0)

All responses (12)

Answers (0)

Suggested answer

Martin Dráb 237,961 Most Valuable Professional on at

Like (0)

Report

If you want to see what exactly you have in the CSV file, open it in a text editor.

You're confused by the fact that Excel applies formatting - in your case, it seems to be displaying dates in 24-hour format (which doesn't need any AM/PM).

If you want to see different format in Excel, change the format of the cell. But note that this has nothing to do with CSV - CSV stores just values, nothing else. To keep your formatting, you must save the file as Excel spreadsheet and not a mere text file (= CSV).

Was this reply helpful? Yes No
MATTGUO 22,349 Moderator on at

Like (0)

Report

Hi Sanjay,

If you want to see them in .CSV file, you can change the consiBiReqTable.ExpiryDatetime to datetime2str(consiBiReqTable.ExpiryDatetime).

Was this reply helpful? Yes No
Sanjay kumar singh 240 on at

Like (0)

Report

Thanks Martin,
Once DateTime is exporting to csv,its using custom Format.

Even in Notepad,its considering 24-hour format.

I think third party software during integration, needs to consider 24-hour Format.

Was this reply helpful? Yes No
Martin Dráb 237,961 Most Valuable Professional on at

Like (0)

Report

If you're interested in what is inside CSV, stop looking at Excel interpretation of this CSV. These are two different things and if Excel isn't relevant for you, it brings only confusion and no positive value.

If you need a specific format exported from AX, explicitly converting the datetime value to string using the required format is indeed the right approach.

You didn't mention any "third party software during integration" before - how is it related to this topic? Are you saying that the CSV isn't what was exported from AX, but it was transformed by another application? Or are you saying that you want to pass the CSV to a third -party application and this application expects 24-hour format? If so, then you don't have any problem, because you're saying that it's what you have in CSV, right?

Was this reply helpful? Yes No
Sanjay kumar singh 240 on at

Like (0)

Report

Hi Martin,

The Data from AX we are exporting to csv(with x++ code),Storing csv files at Azure Blob Container.

My Functional is expecting those csv File will be consumed by some third party integration(we are guessing).

Our task is to add ExpiryDateTime in generated csv (which are storing in Blob Container).

Was this reply helpful? Yes No
MATTGUO 22,349 Moderator on at

Like (0)

Report

Hi Sanjay,

If the requirement is not to see AM / PM or a specific format, then the CSV file is enough to complete your task.

Was this reply helpful? Yes No
Sanjay kumar singh 240 on at

Like (0)

Report

All My efforts(directly sending the field value, or convert to specific string format) are taking value of DateTime as 24- hour Format only.

I think we need to convince client with 24-hour format only in csv file.

Was this reply helpful? Yes No
Martin Dráb 237,961 Most Valuable Professional on at

Like (0)

Report
If I need a specific date/time format, my preferred solution is using .NET. Doing it with native X functions is usually more complicated.

For example:

System.DateTime dateTime = DateTimeUtil::utcNow(); str s = System.String::Format('{0:g}', dateTime);

Was this reply helpful? Yes No
Sanjay kumar singh 240 on at

Like (0)

Report

with above code also, it consider 24-hour format.'AM','PM' we can not be able to retain in csv file.

If manually also we enter 2/27/2020 8:00:00 AM in a csv file cell, it gets converted into 24-hour format.

Was this reply helpful? Yes No
Martin Dráb 237,961 Most Valuable Professional on at

Like (0)

Report

Converted? What are you talking about? If you put a value to file and save the file, the value should be what you put there, right?

Aren't you again doing the same mistake and confusing visualization in Excel with the context of the CSV file? If so, please look again at beginning of this discussion and correct your approach.

Was this reply helpful? Yes No