cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PriyavartRajain
Frequent Visitor

Date format keeps changing in CSV file

I have a flow that creates a CSV table, populates a CSV file with it and saves it in SharePoint. For the example's sake, I only have one column in the CSV file, which is the Date Received. I want the format of the date in this column to be YYYY-MM-DD, but no matter what I do in the flow, the resulting CSV always has MM-DD-YYYY format. The following is my flow: 

Bug1.jpg

 

As you can see, I am only trying to create one column in the CSV table just to isolate this issue for now. The value for the Date Received header is: 

formatDateTime(body('Select')?[0]?['Date Received'],'yyyy-MM-dd')

(Yes, every row will have the same date, again, for simplicity. Later I'd like to change this such that each row has the date received found in each item of the Select output body, but that's a problem for later). 

Now I was expecting this to change the date value to year month date format, but it doesn't. 

I even do this in the select control just to be sure: 

Bug2.jpg

 

Here the expression is: 

formatDateTime(item()?['dtmDateReceived'],'yyyy-MM-dd')

Now I know for sure that the date is being changed to the desired format in the flow, as can be seen here in the run history: 

Bug.jpg

 But in the CSV file the format automatically changes to a different one: 

PriyavartRajain_0-1662102018816.png

 

I'd really appreciate it if someone could provide any insight into what's going on here. What am I missing? Also if I am able to fix this, how can I modify my value for the Date Received header so that it picks the date in each object from the Select output? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PriyavartRajain
Frequent Visitor

Update: 

I was able to fix this by using the following formula in the select action for Date Received: 

 

concat('="',formatDateTime(item()?['dtmDateReceived'],'yyyy-MM-dd'),'"')

 

It forces excel to evaluate this formula when populating the CSV. Cheers! 🙂

View solution in original post

3 REPLIES 3
Redders
Continued Contributor
Continued Contributor

Hi @PriyavartRajain,

 

I'm not too sure that there is a problem with your flow, although I'm prepared to be corrected.

 

I have created a notepad file with the values that your flow would output and when opened in Excel, it is showing the same as what you can see.

 

I believe what is happening is that Excel is auto changing the type of the data. When opening the same file using power query, you can see this happening as part of the applied steps

 

Redders_0-1662113867114.png

Redders_1-1662113879369.png

Redders_2-1662113895110.png

 

As you can see when I open the notepad at the side of the excel file, it is showing the different format

 

Redders_3-1662114008272.png

 

 

I hope this helps

 

Edit: Just to add, try opening the file in Notepad to see what your results actually are

I see, all this time I have been trying to figure out what was wrong in my flow but I tried what you did and it indeed is something that Excel is doing. Thank you so much! Now I at least know that the issue isn't with my flow 🙂

PriyavartRajain
Frequent Visitor

Update: 

I was able to fix this by using the following formula in the select action for Date Received: 

 

concat('="',formatDateTime(item()?['dtmDateReceived'],'yyyy-MM-dd'),'"')

 

It forces excel to evaluate this formula when populating the CSV. Cheers! 🙂

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (4,626)