Power Automate Exchange

Please login or click SIGN UP FOR FREE to create your FlowUG account to join this user group.
Expand all | Collapse all

Excel to SharePoint List - Text vs Numbers

  • 1.  Excel to SharePoint List - Text vs Numbers

    Posted Dec 28, 2020 04:25 PM

    Hello All,

    I am trying to set up a flow that will copy selected lines in an Excel file and send to a SharePoint List.  Some of the fields we need to be text, but they contain numeric characters (i.e.: vendor numbers would be 0234 or PO#s are 030456789, but we need the leading zeros and don't want them to to be formatted using commas so we can't use a numeric field in the SP list).  When I run the flow to copy the lines to the SharePoint list, any field that contains numbers are not going to SharePoint - the fields are blank.  Any ideas how to fix this?  It is driving me crazy.

    Thank you,

    Vicki

    .



    ------------------------------
    Victoria Becker
    Manager, Customer Support - Group Inventory
    ------------------------------


  • 2.  RE: Excel to SharePoint List - Text vs Numbers

    Bronze Contributor
    Posted Dec 29, 2020 04:55 AM
    Edited by Ron Kats Dec 29, 2020 04:57 AM

    Hello Victoria,

    Can you share the Flow formula were you convert the numbers to text with us?

    In advance you could try:

    substring(concat('0000', '234'),sub(length(concat('0000', '234')),4),length(concat('0000', '234')))

    Looks a bit complicated, but that is not the case :-)

    What it does it adds some leading zero's to your number and then get the last 4 characters. This should result in '0234'. Of course you must replace '234' with your sharepoint field.


    Edit: There is no 'left' or 'right' function in PowerAutomate flows, that makes it so 'complicated'.
    ------------------------------
    Ron Kats

    d[ o_0 ]b
    ------------------------------



  • 3.  RE: Excel to SharePoint List - Text vs Numbers

    Posted Dec 29, 2020 09:33 AM

    Hi Ron,

    Thank you for your reply.  I'm not exactly sure I follow though.  Maybe I didn't explain what I am trying to do properly.  The fields in Excel are already formatted as text, so the leading zeros hold.  But none of those fields actually populate in the SharePoint list when I run my flow.  They are all left blank.  Here is what my flow looks like:

    The only field that actually transfers from Excel to the SharePoint list is the Buyer Code.  Everything else is blank in the new record.  Does that make sense?

    Thank you,

    Vicki



    ------------------------------
    Victoria Becker
    Manager, Customer Support - Group Inventory
    ------------------------------



  • 4.  RE: Excel to SharePoint List - Text vs Numbers

    Bronze Contributor
    Posted Dec 30, 2020 01:54 PM

    What about the filename? Is that how the file on Sharepoint is called also? I mean is the filename in Flow the same as in SharePoint? 



    ------------------------------
    Ron Kats

    d[ o_0 ]b
    ------------------------------



  • 5.  RE: Excel to SharePoint List - Text vs Numbers

    Posted Dec 30, 2020 03:33 PM
    Hi Ron,

    Yes, the file name is correct even though it looks strange. For some reason, it displays as the object ID instead of the actual file name. It is connected to the correct file though, and I can trigger the flow correctly from the Excel file.

    Thanks,
    Vicki


  • 6.  RE: Excel to SharePoint List - Text vs Numbers

    Bronze Contributor
    Posted Dec 30, 2020 03:48 PM
    I would normally guess that the issue is a data type incompatibility.  i.e., the data from power automate doesn't match the data type of the field in Sharepoint.
    Confirm your sharepoint data types.  If they are correct, try forcing a text conversion in power automate not just a format.
    Try this link:
    https://powerusers.microsoft.com/t5/Connecting-To-Data/How-to-convert-integer-to-string-in-Select-action/td-p/374747

    ------------------------------
    Wallo Atkinson
    Green Bay
    ------------------------------