Splitting Excel Cell into A single Datatable

Help Studio
1

I have an excel workbook which I am trying to analyse against a previous day workbook and display only unique rows.
One of the columns includes data in each row with multiple lines of data within the single cell

i.e
Name: Bob
Name: Chris
Name: Lucy

I need to split this data out and have a row per line in a new datatable.
Along with this data I will fill in the other columns with default values to cross reference.

2

@elliot.barling,

You can achieve this by reading the Excel into a DataTable, then iterating each row and using row("YourColumnName").ToString.Split({Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries) to separate the multi-line values. For each split value, create a new DataRow, assign the extracted value, and populate the remaining columns with your required defaults. Finally, add these rows into a new DataTable and write it back to Excel for further comparison.

3

@elliot.barling

  1. Read data into dt
  2. Use for each activity with Enumerable.Range(0,dt.RowCount).ToArray
  3. Inside loop use if condition with dt.Rows(currentitem)(0).ToString.Split({Environment.NewLine},StringSplitOptions.None).Count>1
  4. On else side leave blank and on then side use for each acticity with input as dt.Rows(currentitem)(0).ToString.Split({Environment.NewLine},StringSplitOptions.None).Skip(1).ToArray
  5. Inside that add data row and give first column value as currentitextitem (from inner loop) and other values using the datatable row dt.Rows(currentitem)(1).ToString this will give first column value for currentrow..so similarly fill all
  6. Now after the loop use assign and clear data from initial row dt.Rows(currentitem)(0) = dt.Rows(currentitem)(0).ToString.Split({Environment.NewLine},StringSplitOptions.None).First

Now after loop you will have all the values separately

Cheers

4

Hello @elliot.barling try this approach
Read the datatable using Read Range as dtInput then build DataTable as dtOutput with same schema then use For Each Row in dtInput then use Assign for these

names = row("YourColumnName").ToString.Split({Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries)

Then use For Each and loop through names then use separate Assign for these

newRow = dtOutput.NewRow()
newRow.ItemArray = row.ItemArray.Clone()
newRow(“YourColumnName”) = name.Trim()
dtOutput.Rows.Add(newRow)

Then use Write Range and use dtOutput

Cheers

5

My apologies but I am struggling to follow the flow.

@bhavesh.choubey
I have created a new datatable with the same column headers
I am now

  • Using a for each loop to loop through each row in the original data table

Then I am using the assign stages laid out as above but I am getting multiple errors for the assign steps within the ForEach loop

For context, I have the following column in the original table:

  • ID
  • Date
  • Name
  • Count
6

Can you share like what error you’re getting?

7

hi, @elliot.barling you can do this with simple steps
Read your Excel into a DataTable.
Create a clone of that DataTable (NewDT = OriginalDT.Clone).
For each row in your original DataTable- Split the multi line cell using row(“YourColumn”).ToString.Split({Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries)

For each item from the split use Add Data Row to NewDT filling other columns with defaults as needed

Example = For Each row In OriginalDT.Rows
For Each nameLine In row(“YourColumn”).ToString.Split({Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries)
NewDT.Rows.Add(nameLine, “Default1”, “Default2”)
Next
Next

Now NewDT will have one row per line of data with your default values in other columns

8

I am receiving the attached error

image
image1113×702 31.4 KB

9

Change the datatype of Names variable to Array of String.

10

In the Variables panel change your variable Names to type String (Array of String).

11

Thank you both
Once I have analysed this data against another set of data I need to put the data back to the original format.
How would I get the split out ‘Name’ data back into a single cell for a single row in a dt?
Thanks!

12

@arjun.shiroya
I have the following in my Array Assign but it doesn’t seem to be splitting the cell text correctly
They are still all coming through as one list item

row(“Names”).ToString.Split({Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries)

Have I written something incorrectly?
As far as I can tell the text is split with a line break, that is how it is shown in Excel

13

@elliot.barling i think that in excel line breaks inside a cell are usually stored as vbLf instead of Environment.NewLine. so thats y your split is not working.
Try this - row(“Names”).ToString.Split({vbLf}, StringSplitOptions.RemoveEmptyEntries)
it should split each line into separate

14

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