Skip to main content

Notifications

Community site session details

Community site session details

Session Id : 4fPoeTC89WK1EJ1ZgHIJKH
Power Automate - Building Flows
Unanswered

How to create a fixed-width file

Like (5) ShareShare
ReportReport
Posted on 29 Jun 2021 21:44:45 by 306

We have the need to generate files with a fixed-width data format.  Has anyone figured out how to do this from a Power Automate Cloud Flow?  I'm able to create a CSV files, but not seeing any actions for fixed-width.  Do I need to create a CSV and write something else to convert it?

  • Suggested answer
    Lance_Raeper Profile Picture
    6 on 04 Aug 2024 at 00:42:05
    How to create a fixed-width file
    I know this is an old post, but I want to provide steps to do this within a cloud flow in case anyone else views this question like I did. My use case involved sending data from a SharePoint list via SFTP in a fixed-width text format. I had 64 columns and around 1,000 rows of data to process into a flat file with specified lengths and starting positions for each field. Some fields required transformation via expressions, some were hardcoded, and a couple didn't exist on our list but had to be included to maintain the required file structure. This was daily scheduled process that took around 30 minutes.
     
    The overall flow:
     
    Apply to each snippet:
     
    Each Compose action corresponds to a required field in the flat file, using a consistent expression structure. Below is an example. this expression concatenates the value of field_10 (my list's internal name for 'US State') from the current item in the "Apply to each" loop with two spaces, then extracts the first two characters of the resulting string. You just need to change the dynamic value for the field, the padding, and the number of characters based on your length requirement for each specific field. This ensures that each field will always meet the specified length requirement, regardless of whether it contains data--fields that didn't exist in my list would simply have a blank value ('') in place of the dynamic content.
    substring(
        concat(items('Apply_to_each')?['field_10'],
            '  '
        ),
        0,
        2
    )

    The 'Compose concatenated fields' action does as implied, concatenating the fields in the required order.
    Below is a generic example.
    concat(
        outputs('Compose_Field1'),
        outputs('Compose_Field2'),
        outputs('Compose_Field3'),
        outputs('Compose_Field4'),
        outputs('Compose_Field5')
    )

    Next, append the result to the fixedWidthText string variable as the final step in the loop.  I used the 'SFTP - SSH' connector at the end.
    To set this up, you need the Site, Port, Host server address, Username, and Password.

    You can also achieve this more efficiently using an office script to transform Excel data, or a custom connector, but I find the cloud
    flow worked out better for me as I had multiple transformations to make outside the sharepoint list, and the processing time
    wasn't important.


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

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - Building Flows

#1
stampcoin Profile Picture

stampcoin 87

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 70 Super User 2025 Season 1

#3
David_MA Profile Picture

David_MA 48 Super User 2025 Season 1

Overall leaderboard
Loading started
Loading complete