Creating CSV from excel table adds an extra empty line

Like (0) Share

Report

Posted on 2 Apr 2022 19:10:45 by Craig Stewart ABZ

957 Super User 2024 Season 1

As per the title, I'm having an issue where I'm trying to convert data from an excel table from a file stored in sharepoint into a CSV file. It's is working, but there is always a blank line at the end of the file;

When I pull this file into another flow to process the data it works fine except for this rogue row, which ultimately causes the flow to have a status of failed.

The end goal of this flow is to populate a journal in D365, so if the file has 10 rows, then the 'apply to each' shows 11. The first 10 all process as expected followed by the failure of this empty 11th row.

I've tried creating the csv file and saving it to sharepoint then pulling that into the flow and also tried avoiding the file creation piece and getting the data from within the flow to process by using compose actions but still get the same issue.

If I open the csv file and select all, copy and paste into a compose statement then delete the blank line the output works as expected.

Here's the failure screen in the processing flow, you can see it's looping through 127 line, but there should only have been 126 - there are no failures other than that empty line, the previous 126 lines are processed and appear in D365 F&O.

Thanks for reading and hopefully someone can point me in the direction of how to solve this conundrum.

All responses (9)

Answers (1)

abm abm 32,464 Most Valuable Professional on 02 Apr 2022 at 21:17:11

Like (0)

Report

Re: Creating CSV from excel table adds an extra empty line

Hi @CraigStewart

Thanks for your quick replies and explaining the requirement very well. Glad to hear that all working well.

Thanks
Craig Stewart ABZ 957 Super User 2024 Season 1 on 02 Apr 2022 at 21:14:30

Like (1)

Report

Re: Creating CSV from excel table adds an extra empty line

Amazing!

Thanks. This looks to have solved my headache.

126 records with the expression result as 'true', 1 empty line with 'false', 1 journal created with the expected 126 lines and a flow reporting as 'Test succeeded'.

Thank you very much for you help here.
Verified answer

abm abm 32,464 Most Valuable Professional on 02 Apr 2022 at 20:59:26

Like (1)

Report

Re: Creating CSV from excel table adds an extra empty line

Hi @CraigStewart

Thanks for the reply.

Add an IF condition inside your Apply To Each

Follow the below steps:

Change the condition to 'not equal to'

Please your cursor inside the Choose a value on left hand side

click expression and type the following

items('LoopData')?['FromTime')

Click ok

Next place the cursor in next choose a value (right hand side of the condition)

Click expression and type null

click ok

Move all the other action steps (set variable, set variable 2, create record 2) inside the Yes condition.

Thanks
Craig Stewart ABZ 957 Super User 2024 Season 1 on 02 Apr 2022 at 20:49:51

Like (1)

Report

Re: Creating CSV from excel table adds an extra empty line

When it gets to the final line
it fails at set variable

My select statement is showing a final line of
  {
    "Project": "",
    "Resource": null,
    "Category": null,
    "Date": null,
    "FromTime": null,
    "ToTime": null,
    "LineProperty": null,
    "FinancialDimensions": null,
    "Hours": null,
    "LegalEntity": null
}

I also think my Initialize variable was wrong

So I changed that, and also my loop which had a skip in for the headers
The problem is still there with that tidied up
abm abm 32,464 Most Valuable Professional on 02 Apr 2022 at 20:32:45

Like (0)

Report

Re: Creating CSV from excel table adds an extra empty line

Thanks for your reply. I want to see the mapping of the failed step.

Could you please expand these steps and post again?

Thanks
Craig Stewart ABZ 957 Super User 2024 Season 1 on 02 Apr 2022 at 20:18:21

Like (1)

Report

Re: Creating CSV from excel table adds an extra empty line

Thanks for the help.
Ok
At this point we have created the csv file and I'm picking it up with 'get file content'

The split on the select is
split(item(),',')?[0]

I then convert the date to the correct format
Create record creates the header of the journal
and the two set variables are conversions of times such as 07:00:00 to integer
Create record 2 creates the lines

126 records as expected
127th empty line at bottom of the csv

Does that help?

Thanks
abm abm 32,464 Most Valuable Professional on 02 Apr 2022 at 20:05:03

Like (0)

Report

Re: Creating CSV from excel table adds an extra empty line

Hi @CraigStewart

Could you please post the set variable mapping you trying to do. I will try to answer it.

Thanks
Craig Stewart ABZ 957 Super User 2024 Season 1 on 02 Apr 2022 at 20:02:03

Like (1)

Report

Re: Creating CSV from excel table adds an extra empty line

Hi @abm

I don't know. Yes, in theory this sounds ideal, I just don't know how to do it. If you could point me in the right direction that would be much appreciated.

My experience with Power Automate has been mainly using it to move files from one place to another or sending notifications.
This one has been built through trial and error, google and this forum over the past 3 days so I'm still very much in the learning stage here. 🙂

Thanks
abm abm 32,464 Most Valuable Professional on 02 Apr 2022 at 19:28:26

Like (1)

Report

Re: Creating CSV from excel table adds an extra empty line

Hi @CraigStewart

Can't you add the check whether the row is empty in your loop?

Thanks