Unanswered

Convert Fixed Width file to JSON

(0) Share

Report

Posted on by PoohBear

I am trying to convert a Fixed Width Text File to JSON. For the life of me I cannot figure it out. Has anyone done this before?

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @PoohBear

If it is fixed width, you would split() on a return line (I often insert a return into a compose and split on that compose) to create an array of lines and then using a select action with the new array as input, create keys in the left column and in the right column use substring(item(),starting integer, length) for each of the values.

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

Was this reply helpful? Yes No
PoohBear on at

Like (0)

Report

This only gives me the very first line. Not the first 3 characters of every line. Did I enter something incorrectly?

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @PoohBear

Looks like you've got the idea - what does the history of your compose look like? It should show the fixed width file as input and an array of lines as output. I note you've split on \r\n. File encodings and return characters are a fun game. I've done a bit on it here https://www.damobird365.com/how-to-parse-a-csv-to-json-array-flow/

If you had a compose called FileContent, this expression should handle the encoding automatically and return to you an array of line objects.

if(equals(indexof(outputs('FileContent'), decodeUriComponent('%0D%0A')), -1), if(equals(indexof(outputs('FileContent'), decodeUriComponent('%0A')), -1), decodeUriComponent('%0D'), decodeUriComponent('%0A')), decodeUriComponent('%0D%0A'))

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

Was this reply helpful? Yes No
PoohBear on at

Like (0)

Report

If I understood you correctly you are stating to put the output of the "Get File Content" into the Compose "FileContent", then have another compose and enter your formula above. If that is what you are stating to do, the Compose with your formula is blank

Was this reply helpful? Yes No
PoohBear on at

Like (0)

Report

I am looking through your link. In step two you have a delimiter which is a ",", but I do not have a delimiter. If this was a regular CSV file this would be easy. However, this fixed width file is not the same. This is what is giving so much trouble.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @PoohBear

Yeah, ignore the rest of the article, just the bit at the bottom explaining file encodings and return lines. The important bit to look at is your compose action history. What does it look like?

Damien

Was this reply helpful? Yes No