Unanswered

Txt file to Excel table

(0) Share

Report

Posted on by Holywill

I have a text file that looks like a CSV file without headers

2020-02-04,16:03:06.209,<40001>,798,324,61,61,61,60,60,60,60,0,0,0,0,0,0,0,1549,1531,1481,1453,1525,1532,0,0,-8192,-106,-8192,-413,-8192,26,0,0,0,63,0,0,0,0,0,0,0,0,0,0,32,2,16,4,2,20,172,16,210,0,0,0,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,105,30
2020-02-04,16:03:11.143,<40001>,799,321,61,61,61,60,60,60,61,0,0,0,0,0,0,0,1557,1529,1482,1464,1512,1510,0,0,-8192,-122,-8192,-402,-8192,26,0,0,0,63,0,0,0,0,0,0,0,0,0,0,37,2,16,4,2,20,172,16,210,0,0,0,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,105,30
2020-02-04,16:03:16.097,<40001>,807,332,61,62,61,60,61,60,60,0,0,0,0,0,0,0,1547,1520,1497,1455,1506,1559,0,0,-8192,-106,-8192,-406,-8192,25,0,0,0,63,0,0,0,0,0,0,0,0,0,0,42,2,16,4,2,20,172,16,210,0,0,0,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,105,30

I was successfully able to split the lines, then to get an output every element separately

it works fine in terms of isolating every element from the text file,

now the struggle is to put this data in Excel table, I can see that with my approach every element will be on a separate line in the table.

I need to have every line in a row across 103 columns.

can anyone please help with this? that would be legendary!

Thanks

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (1)

Verified answer

DeepakS 2,301 Most Valuable Professional on at

Like (3)

Report

Hi @Holywill

If you are able to split the data in two an Array, then you need to loop through that array and do the following :
1) Split the line by "," this will give an Array of all columns for a row
2) use "Add a row into a table" action and use index expression like : outputs('Compose_3')[0] (first column),
outputs('Compose_3')[1] (second column
I created a sample Power Automate that is using a little different approach to convert CSV to an array and then add a row to Excel

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
Holywill 32 on at

Like (0)

Report

You're a legend! , thank you @DeepakS !

Just one last thing, by the end of every row I have this "/r" for new-line, how can I get rid of it?

Thanks again you legend!

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

Hi @Holywill,

If the "\r" is a string, you could get rid of it by using the replace() function.

For example:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Holywill 32 on at

Like (0)

Report
Hi @v-litu-msft
Thank you for your help,
But I'm confused, where to put the replace
I have this expression, but I'm not sure where to put another replace expression
split(uriComponentToString(replace(uriComponent(variables('CSV')),'%0A','*')),'*')

Thanks again

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

Hi @Holywill ,

Could you please share the output of the string with "\r"?

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
DeepakS 2,301 Most Valuable Professional on at

Like (0)

Report

Hi @Holywill

/r is rendered as %5Cr , try to us this instead %0A. I did not test it so let me know if this works.
if does not share you CSV file

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

Hi @Holywill,

Please have a try the following expression:

decodeUriComponent(replace(encodeUriComponent('The string with /r'),'%0D',''))

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Holywill 32 on at

Like (0)

Report
Thank you for your reply,

for some reason, I've been struggling the last couple of days with this error
InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '1' and column '2812': 'The template language function 'encodeUriComponent' was invoked with invalid parameters. The parameter at index '0' cannot be converted to URI component.'.
while it was working fine.

any ideas?

Thanks

Was this reply helpful? Yes No
Holywill 32 on at

Like (0)

Report

@DeepakS @v-litu-msft
Hi gents,

Could you please help me with this issue, I've tried everything to no avail.
I tried different files and recreated the flow and nothing seem to work, while it was working just fine and out of a sudden, it stopped.

Thank you for your help in advance

Was this reply helpful? Yes No
mmorley 90 on at

Like (0)

Report

@DeepakS this hepled me resolve my issues of getting a csv imported into a sharepoint list
I just had to change the replace on the uricomponent to '%0D%0A' to get rid of '\r\n'
thanks again

@Holywill put a condition check to not add the entry if the length is >1 after 'compose_3' then move 'add_a_row_into_a_table' into the 'yes' section of the condition

the error you get is that the apply to each has reached the last line and if debug you will see it is probably just ''

Was this reply helpful? Yes No