Skip to main content

Notifications

Power Automate - General Discussion
Answered

Date conversion from CSV to SharePoint

(0) ShareShare
ReportReport
Posted on by 10

Hello everyone! I'm working on a flow that aims to migrate records to a sharepoint list.

 

The flow follows the following steps:
1. Receive the CSV file
2. Rebuild the files with a 'Select'
3. Iterate over each record with an 'Apply to each'
4. Apply a series of filters.
5. Save the current record in a variable of type array ('registroArrayFecha').
6. Format the date1
7. Format the date2
8. Evaluate which date is larger with a condition.
9. Create item in sharepoint list and export the record.

 

The structure of the record saved in the variable ('registroArrayFecha') would be something like this:

 

 

 

[
 {
 "Resource Name": "User, Name",
 "Work Order": "17992541",
 "Work Country": "Spain",
 "Start Date": "21-2-23",
 "Created Date": "9-2-23",
 }
]

 

 

 

 

The first problem occurs in point 6, when I try to extract the value of 'Start Date' from the variable 'registroArrayFecha' and give it a format to be able to later evaluate this date in a condition.

 

The expression I use to give this format is the following: (This expression is inside a 'Compose')

 

 

 

formatDateTime(concat('20',
split(variables('registroArrayFecha')[0]['Start Date'], '-')[2], '-',
split(variables('registroArrayFecha')[0]['Start Date'], '-')[1], '-',
split(variables('registroArrayFecha')[0]['Start Date'], '-')[0]),
'dd-MM-yyyy')

 

 

 

The error occurs in certain records. On the one hand we have records in which the date is maintained, for example:

Correct conversionCorrect conversion

 

And on the other hand we have records in which we obtain a completely different Compose output, example:
Incorrect conversionIncorrect conversion

 

Entry dates always come in 'dd-MM-yyyy' format.

To summarize, I can't get all the dates to import in the same format so I can evaluate them.

  • usernull_1234 Profile Picture
    usernull_1234 10 on at
    Re: Date conversion from CSV to SharePoint

    Hi @SamLed,

     

    Thank you very much for the response, very useful!

  • Verified answer
    SamLed Profile Picture
    SamLed 2,326 on at
    Re: Date conversion from CSV to SharePoint

    Hi @usernull_1234,

     

    You should use parseDateTime() function:

     

    SamLed_0-1708118401676.png

     

    Results without specifying any format (ISO 8601 compliant by default) :

    SamLed_1-1708118472317.png

     

    [Edit] found a nice article explaining this function and providing a list for locales: Power Automate ParseDateTime Function Explained (zeitgeistcode.com)  

    ______________________________________________________________

    If I have answered your question, please Accept the post as solution.
    If you like my response, please Thumbs Up.

     

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard