Expand my Community achievements bar.

Join us at Adobe Summit 2024 for the Coffee Break Q&A Live series, a unique opportunity to network with and learn from expert users, the Adobe product team, and Adobe partners in a small group, 30 minute AMA conversations.
SOLVED

Adobe Report Builder: how to avoid empty rows in file format CSV

Avatar

Level 2

Hello,


I have build a report with Adobe Report builder and use as filter "Top100"
The result varies from 1 to 100 rows.

In the data sheet I have in every row a formula's.

When I now schedule this report and choose .csv as file format, I got a file which looks like:

1231163_pastedImage_0.png

Question:

How can I avoid to get the ",,,,,,,,,,," lines in mine .csv file?

Thanks for helping me in advance.

Greetings,
Jeroen Boland

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

Basically, try to recreate the table using pivot table functionality.

  1. Select the output report builder table 
  2. Go to Insert tab and click Pivot Table
  3. Select all the columns in the same order as required
  4. Go to Design tab (will show up after selecting anywhere on the pivot table)
    • Change Subtotals to "Do not show subtotals"
    • Change Grand Totals to "Off for Row and Columns"
    • Change Report Layout to "Show in Tabular Form"
  5. The next part is add a filter to remove blank values in the first row
  6. The last step is to keep the pivot table sheet as the first sheet.

Schedule this workbook

Example table:

1579899_pastedImage_8.png

Pivot table created:

1579900_pastedImage_9.png

Change in Subtotals, Design and Layout:

1579910_pastedImage_25.png

After making the changes:

1579911_pastedImage_26.png

If you want, you can remove/update the formatting, but should not matter since you will be getting a CSV

View solution in original post

9 Replies

Avatar

Employee Advisor

Hi Jeroen,

How many dimensions and metrics do you have here? I am guessing there should not be any rows which are completely blank. May need to look at it more deeply to understand what is happening.

However, there are ways to remove rows where a particular dimension doesn't have a value.

-Hyder

Avatar

Level 2

Hi Hyder,

For the "Row Labels" I have the Top 1-1000 because I don't know how many External Promotions there are.

1233980_pastedImage_0.png

For each External Promotions I want to know Click Through and several other Metrics. I can't run them in 1 requests, because the Metrics have different segments for selection.

To get the data in an .CSV file I have created one sheet where I put all the data together for each External Promotions.

Therefor I have a sheet with up to 1.000 lines with all kind of formulas to get the data together.

Because there are not always 1.000 External Promotions there are line with a formula that create a blank in the cell

1233981_pastedImage_6.png

1233982_pastedImage_7.png

So from line 823 there are only blanks in the cell and that creats the empty lines in the .CSV file.

1234007_pastedImage_8.png

I think I can't use a Macro, because I want to Schedule the report and run in daily without opening the report.

Do you have any idea how to solve the empty lines on the bottom?

Regards,

Jeroen

Avatar

Employee Advisor

Totally forgot about this.

I tested, this and yes, it is sending out as you have mentioned.

The workaround I found was to create a pivot table and form the exact same table through it. Keep this as the first tab in the workbook and schedule this as a CSV.

Worked for me!

Avatar

Level 2

Hi, I am also having same problem of extra empty rows. Could you please explain the steps in detail about "creating a pivot table and form the exact same table through it". I tired but did not work. In case, I might be doing wrong. Thanks

Avatar

Level 2

Hi Jeroen,

Did you find the workaround to the extra empty rows? I tried pivot table option, but did not work for me. Could you please help

Avatar

Correct answer by
Employee Advisor

Basically, try to recreate the table using pivot table functionality.

  1. Select the output report builder table 
  2. Go to Insert tab and click Pivot Table
  3. Select all the columns in the same order as required
  4. Go to Design tab (will show up after selecting anywhere on the pivot table)
    • Change Subtotals to "Do not show subtotals"
    • Change Grand Totals to "Off for Row and Columns"
    • Change Report Layout to "Show in Tabular Form"
  5. The next part is add a filter to remove blank values in the first row
  6. The last step is to keep the pivot table sheet as the first sheet.

Schedule this workbook

Example table:

1579899_pastedImage_8.png

Pivot table created:

1579900_pastedImage_9.png

Change in Subtotals, Design and Layout:

1579910_pastedImage_25.png

After making the changes:

1579911_pastedImage_26.png

If you want, you can remove/update the formatting, but should not matter since you will be getting a CSV

Avatar

Level 2

Hi Hyderziaee,

The above solution did work and help. So first sheet is pivot table sheet in excel and 2nd is the normal one. I schedule daily report sending to FTP from 1st sheet. Now the problem is, everyday same day data is going. I did schedule the report but everyday it should go different data but it is going for same day data everyday.

Before pivot table option, it was going different files everyday but with empty rows. Now I implemented pivot option to get rid of empty rows then same day data is going to FTP server. Can you please help in what mistake I might be doing ?

Avatar

Employee Advisor

Hi James.

You could try creating a simple request and pivot table and see what happens. I am not sure what could be wrong here.

Try scheduling in XSLX and check if the pivot and the request table are updating.