Skip to main content

Notifications

Power Automate - General Discussion
Answered

Add data to Excel from a fixed width text file

(1) ShareShare
ReportReport
Posted on by 175

We have a department that gets information from a 3rd party that sends the data over as a Fixed Width Text file.

We are trying to use Power Automate to take the Rows of data, then break out the data for each row to write back to Excel columns.
The issue we are finding is that the Fixed Width is not always the same and so when we try to add the data to the Excel file, data is either missing, or getting added to the wrong columns.
We've created an Array Variable from the file content:

ShaneMeisnerTH_0-1720545507980.png

We then used a compose to split the Text out for each row using the expression - split(outputs('Compose_2'),' ')

But having a heck of a time, due to the Data not always being in the same spot, to add the data to the Excel file in the correct columns.
I've attached the Test Text file, as well as the Output we get back from the Split expression.

Thanks in advance with any help you can give us.

  • ShaneMeisnerTH Profile Picture
    ShaneMeisnerTH 175 on at
    Re: Add data to Excel from a fixed width text file

    Thank you, this is GREAT!!
    The department gets about 10 different formatted Fixed Width Text files from the 3rd party.
    So this code will work for all and the data we have back can then be added to the Excel file!
    thanks again!!!

  • Verified answer
    claudiovc Profile Picture
    claudiovc 94 on at
    Re: Add data to Excel from a fixed width text file

    Hi @ShaneMeisnerTH 

     

    I started from the arrangement of lines of text:

     

    My flow:

    FlujoCompleto.PNG

    My flow detail:

    detalleFlujo1.PNG

     

    detalleFlujo2.PNG

    items('Aplicar_a_cada_uno')
    length(item())
     
     

    detalleFlujo3.PNG

    trim(item())

     

     

    detalleFlujo4.PNG

    item()['valor'][0]
    item()['valor'][1]
    if(equals(length(item()['valor'][2]),1),item()['valor'][3],item()['valor'][2])
     
    My output in a html table
    claudiovc_0-1720724856681.png

     

    I hope it helps you

    Greetings!

     

     

  • ShaneMeisnerTH Profile Picture
    ShaneMeisnerTH 175 on at
    Re: Add data to Excel from a fixed width text file

    Hoping someone might have a "cleaner" way of doing this.. Below is part of what we have so far( there are a total of 19 composes) that seems to be working.
    If anyone has any ideas on how to simplify this, I'd be glad to hear it..
    We tried to use Parse Json, but it does NOT like fixed width text files, so this is what we cam up with.

    ShaneMeisnerTH_0-1720696539449.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,666

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,996

Leaderboard