Skip to main content

Notifications

Power Automate - General Discussion
Answered

When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

(0) ShareShare
ReportReport
Posted on by 389

Hi All ,

 

When I utilize a flow to import excel table to a list , an error occurs as below message. 

 

GUOGUO_0-1627023477845.png

 

The format of the column in excel as below:

GUOGUO_1-1627023769977.png

 

the format or sharepoint list column as below:

GUOGUO_2-1627023857567.png

 

Thanks a lot

 

 

 

  • GUOGUO Profile Picture
    GUOGUO 389 on at
    Re: When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

    Hi Eric,

    I am writing this message to ask if i create a new Excel workbook with some same items (ID items ,which is indexed column)but with different content compared with  blank cell of the fist version of item. How should i compare them and update item in the list? Thanks

  • GUOGUO Profile Picture
    GUOGUO 389 on at
    Re: When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

    Thanks a lot ,Eric

  • Verified answer
    eric-cheng Profile Picture
    eric-cheng 5,171 on at
    Re: When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

    Hi @GUOGUO ,

     

    I build a similar flow so you can copy it.

     

    1) Excel with datetime column

     

    ericcheng_0-1627037693415.png

     

    2) Get Tables and then List Rows Present In Table

    ericcheng_3-1627038244171.png

     

     

    3) For each row, Parse JSON value and then use the below formula

     

    formatDateTime(addDays('1899-12-30', int(items('Apply_to_each_2')?['LIB Global Item Status'])),'MM/dd/yyyy')
     

    ericcheng_4-1627038274748.png

     

     

    4) Finished result

     

    ericcheng_1-1627038178570.png

     

    --------------------------------------------------------------------------
    If I have answered your question, please mark my post as a solution
    If you have found my response helpful, please give it a thumbs up

     

  • GUOGUO Profile Picture
    GUOGUO 389 on at
    Re: When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

    Thanks a lot ,i will try

  • eric-cheng Profile Picture
    eric-cheng 5,171 on at
    Re: When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

    Hi @GUOGUO ,

     

    I am not sure why you have the 'Apply To Each 3' loop.  You should be able to move the 'Create Item' action out and into 'Apply To Each 2'. Then update your expression to:

     

    Apply_to_each_2

     

    You can try updating the expression first and see if it works too but looks to be alot of actions which are required.

     

    --------------------------------------------------------------------------
    If I have answered your question, please mark my post as a solution
    If you have found my response helpful, please give it a thumbs up

     

  • GUOGUO Profile Picture
    GUOGUO 389 on at
    Re: When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

    Hi Eric

    Yes,the Column in Excel named as

    GUOGUO_2-1627035061747.png

     

     

    GUOGUO_0-1627034911559.pngGUOGUO_1-1627034981828.png

     

  • eric-cheng Profile Picture
    eric-cheng 5,171 on at
    Re: When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

    Hi @GUOGUO ,

     

    Can you please take a screenshot of the loop from your get items and confirm the column from your excel is LIB Global Item Status?  If possible, please add an output of this field and share the value.

  • GUOGUO Profile Picture
    GUOGUO 389 on at
    Re: When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

    Hi Eric .

     

    Thanks for your solution. Though , it doesn't work. Could you please help to check ?

     

    GUOGUO_1-1627029000439.png

     

     

    GUOGUO_0-1627028923358.png

     

  • Verified answer
    eric-cheng Profile Picture
    eric-cheng 5,171 on at
    Re: When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

    Hi @GUOGUO ,

     

    1. Clear out the field

    2. Click on the field 

    3. Click on the  'expression' tab in the flyout

    4. Paste the below in and replace xxxx with the column name in Excel.  If you renamed your Apply to each loop, you will need to change that too.

     

    formatDateTime(addDays('1899-12-30', int(items('Apply_to_each')?['xxxx'])),'MM/dd/yyyy')

     

    Screenshot_20210723-180654_Chrome.jpg

    --------------------------------------------------------------------------
    If I have answered your question, please mark my post as a solution
    If you have found my response helpful, please give it a thumbs up

     

  • GUOGUO Profile Picture
    GUOGUO 389 on at
    Re: When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

    Hi Eric

    Thanks a lot.

    Though, how should  I use the expression to convert the column that I get from Excel table.

    GUOGUO_0-1627025740499.png

     

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,343

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,703

Leaderboard