cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aishak12
Helper IV
Helper IV

Removing header (first row) in CSV files

All I have a flow that is picking up content for all the files in my SharePoint folder. The flow then creates a master file of all the combined content. However I want to skip the header (1st) row of all files. Ideally I only want one header in the master file. Not duplicates.

 

aishak12_0-1643406905378.png

 

aishak12_1-1643407134571.png

 

Any Idea how to approach this? 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Expiscornovus
Super User
Super User

Hi @aishak12,


That blank row is probably from my earlier concat function. I was adding an additional a newline character at the end of each file.

 

Maybe remove the concat function and try and see if that works better:

join(skip(split(replace(base64ToString(body('Get_file_content')['$content']), decodeUriComponent('%0D'), ''), decodeUriComponent('%0A')), 1), decodeUriComponent('%0A'))

 

View solution in original post

7 REPLIES 7
Expiscornovus
Super User
Super User

Hi @aishak12,

 

I would look into the skip function of Power Automate. With that function you can skip a number of items in the beginning of an array. In your case we can just skip 1 in the content of your file. 

 

Below is an example of that approach:

This sample assumes that you have CR (%0D)  LF (%0A) characters in your csv files. Obviously, change it to whatever delimiter you are using for the split. With the join I am turning it back into a string value again. So, it can be saved into your Enter variable.

 

concat(join(skip(split(replace(base64ToString(body('Get_file_content')['$content']), decodeUriComponent('%0D'), ''), decodeUriComponent('%0A')), 1), decodeUriComponent('%0A')), decodeUriComponent('%0A'))

 

crlf_skip_split.png

 

Thank you this does work. Now all the data from each file is separated in the master CSV by one blank row. Is their a way to eventually remove this blank row so all the data is together?

 

aishak12_0-1643642342305.png

 

Expiscornovus
Super User
Super User

Hi @aishak12,


That blank row is probably from my earlier concat function. I was adding an additional a newline character at the end of each file.

 

Maybe remove the concat function and try and see if that works better:

join(skip(split(replace(base64ToString(body('Get_file_content')['$content']), decodeUriComponent('%0D'), ''), decodeUriComponent('%0A')), 1), decodeUriComponent('%0A'))

 

scleme
New Member

Hi @Expiscornovus 

 

This is great solution.

 

I have tried this and it seems working for the extra blank lines but with this function, the headers of all my csv files are removed.

 

Can you help me retain just 1 header at the top (header of the first file)?

 

Regards,

Scleme

Expiscornovus
Super User
Super User

Hi @scleme,

 

You could first retrieve the content of the first CSV file before the apply to each and append it to the string variable with header. In the Apply to each you can use another skip to skip the CSV file.

 

Below is an example of that approach.

 

1. Retrieving first item in Get File Content - First CSV with header action:

outputs('Get_files_(properties_only)')?['body/value'][0]['{Identifier}']

 

2. Append to string (with header)

concat(join(split(replace(base64ToString(body('Get_file_content_-_First_CSV_With_Header')['$content']), decodeUriComponent('%0D'), ''), decodeUriComponent('%0A')), decodeUriComponent('%0A')), decodeUriComponent('%0A'))

 

3. Using a skip in the Select output from previous steps field in the Apply to Each action

skip(outputs('Get_files_(properties_only)')?['body/value'], 1)

 

skipping_firstcsv.png

Iver199
Helper I
Helper I

 

@Expiscornovus 

 

Very helpful solution, thank you for sharing!

 

First, thank you @Expiscornovus! This is already the cleanest version of this solution, and all of the steps have succeeded for me. But I have beginner question here...
What is the second Append to string expression? (not the first instance, Append to string - First CSV with header)?Screen Shot 2022-05-11 at 2.38.12 PM.png

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,745)