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”
then after the splitting by commas, new columns will appear but without the column headers eg. Invoices 1, …, Invoices 5
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?
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)
this is the input data, each invoice is separated by a comma
them this is after i split by comma which does not give column names.
so how will i go about getting the column headers to become Invoices 1, Invoices 2, …, Invoices 4
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?
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.
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,
→ 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,
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?
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.
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
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?
