Add data to Excel from a fixed width text file

(1) Share

Report

Posted on by ShaneMeisnerTH

195

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:

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.

Test Fixed Width ...

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (1)

ShaneMeisnerTH 195 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

claudiovc 94 on at

Like (0)

Report
Copy link

Link copied!

Hi @ShaneMeisnerTH

I started from the arrangement of lines of text:

My flow:

My flow detail:

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

trim(item())

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

I hope it helps you
Greetings!

Was this reply helpful? Yes No
ShaneMeisnerTH 195 on at

Like (0)

Report
Copy link

Link copied!

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!!!

Was this reply helpful? Yes No