Create Excel table from data with unknown # of rows

Hi, As the title says, we’re trying to automate the use of various Excel activities that first require that we format the data as a table. The hangup is that we usually can’t predict the number of rows in the reports and don’t want the tables filled with a bunch of empty values from just overestimating the table size. Any advice on how to create an adaptive table? I know that this is possible in Studio using For Each loops on Data Tables but I’m trying not to use that method due to the difficulty that a lot of the team is having with Data Tables.

Thanks!

you can use linq to remove all empty rows after processing all the rows:
dataTable = dataTable.Rows
.Cast()
.Where(row => !row.ItemArray.All(field => field is DBNull ||
string.IsNullOrWhiteSpace(field as string)))
.CopyToDataTable();

1 Like

In StudioX - dump the date into a worksheet and use the FormatAsTable activity on the entire worksheet. This will create a table only include the “used range” of the worksheet, meaning that the table will cover only the rows containing data:
image

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.