Adding headers for nonempty columns eg. "x1, x2,... , x7"

I am given the “Invoices” column initally. Then I separated each row of “Invoices” by its commas, I will get each invoice on its own eg. {v, w, x, y, z} gives me 4 new columns on the right of it, with the initial Invoices column keeping onto the first string before the first comma. BUT for these columns newly created, they will not be assigned a header above. How will I be able to automate and name these columns accordingly as “Invoices 1”, … “Invoices 4”

image

then after the splitting by commas, new columns will appear but without the column headers eg. Invoices 1, …, Invoices 5

image

Thanks for the reply!

maybe you can share the initial input data, so we can refer the column structure

In general we would do:

  • Calculate the max Length of splits, (colvalue.Split(","c)
  • Setup dynamically the additional columns: Add DataColumn, using a loop counter
  • Loop over the datatable,
    • split the column value from Invoices
    • Insert the splits within the different columns

Hi Peter,

I managed to split the Invoices column via the commas already, so if I have 4 commas I will have 4 new columns for that row. The issue at hand would be to somehow be able to name those columns accordingly from the left most column without header being Invoices 1, then Invoices 2,… , till Invoices {the max amt of commas in the invoices col.}. Is this possible?

Thanks for your help

we can help.

so we can see where it starts

Hi @hellos

Read Range (SheetName: "Sheet1", Output: dataTable)

For Each Row in dataTable
  Assign: splitValues = row("Invoices").ToString().Split(","c)
  Assign: i = 0
  
  While (i < splitValues.Length)
    If: dataTable.Columns.Count <= i + 1
      Add Data Column (ColumnName: "Invoice" & (i + 1).ToString(), dataTable)
      
    Assign: row("Invoice" & (i + 1).ToString()) = splitValues(i).Trim()
    Assign: i = i + 1
Write Range (SheetName: "Sheet1", Input: dataTable)

Hope it helps!!

hi peter,

this is the input data, each invoice is separated by a comma
image

them this is after i split by comma which does not give column names.
image
so how will i go about getting the column headers to become Invoices 1, Invoices 2, …, Invoices 4

thanks

@hellos

Did you tried this

Flow like this:

with:
MaxSplitCount =
dtData.AsEnumerable.Max(Function (x) x("Invoices").toString.Trim.Split(","c).Length)

will work on a sample data like:
grafik

it has calculated the MaxSplitCount: 5
then it adds dynamically the remaining 4 datacolumns

hi peter, i added text to column activity such that the column “Invoice” will be split with delimiter of a comma. then i tried adding your suggestions after but it says “Invoices” already belong to this datatable as an error for Write Range. any way to debug that?

thanks

hi,

i tried the code but it gives an error about not being able to convert string() to string for the InvoiceValues part. do let me know what could be causing an issue.

thanks!

@hellos
InvoiceValues is of Array of String Datatype String

Hi @hellos

I have a approach to achieve the required output using Generate Table from Text activity, Check the below steps,
→ Use the Read Range workbook activity to read the excel and store in a datatable called Input_dt.
→ Create a String datatype variable called InputData and give the below LINQ Expression in assign activity,

- Assign -> InputData = String.Join(Environment.NewLine, Input_dt.AsEnumerable.Select(Function(X) X(0)).ToList())

→ Then use the Generate Data Table from Text activity to the expected output. Input is InputData variable and Output is Output_dt.
→ Use for each activity to iterate the each column in Output_dt.

Output_dt.Columns

→ Inside for each insert the assign activity and give the below expression,

- Assign -> Output_dt.Columns(index).ColumnName = "Invoice"+(index+1).ToString

→ outside for each insert the write range workbook activity to write the Output_dt data to the Excel.

Check the below workflow for better understanding,
Sequence11.xaml (10.4 KB)

Input -
image

Output -
image

If any new data will be added to the Input the column will automatically creates and write it in the excel file.

Hope it helps!!

hi, thanks for the help. i tried but it just adds the column name from the first column but i have columns in front from columns A till G already and this overwrites the header.

is there a way to let it add the column names only in headers without a name?

thanks

Okay @hellos

You have already columns in your excel right.

Then delete the for each data column activity and after generate datatable from text activity use the write range workbook activity but uncheck the add headers option in properties.

It will not override the columns it will keep columns as it is and write the rows as required.

Hope it helps!!

given this dataset how will i be able to split it? do u have pictures coz i removed the for each datacolumn activity and it just does nothing on my end

thanks

Hi @ppr

I attempted to use the “Add Data Columns” activity, but encountered an error stating that a column named “Invoices 1” already belongs to the DataTable. However, upon inspecting my Excel file directly, I could not find any such column present.

Could you please provide guidance on how to resolve this issue?

Regards,