Combine Columns to create a new column or Append all columns in first column

Help Activities
, , ,
1

Hi,
Inputs can be any two examples below.:

image

image

Output:

image
Combine all the columns and made a one column.

2

in general:

Use build datatable and prepare an empty datatable with one data column - dtResult

Assign Activity:
LHS: dtResult
RHS:

(From d in YourOriginDataTable.AsEnumerable
From x in d.ItemArray
Select r = dtResult.Rows.Add({x})).CopyToDataTable

1 Like
3

Hi @ShekharRPA ,

Could you give this a try?
image

image

(From r In Enumerable.Range(0,dt_sampleData.RowCount)
Let item = Enumerable.Range(0,dt_sampleData.ColumnCount).Select(Function(c) dt_sampleData.Rows(c)(r).ToString).Toarray()
From ra In item
Select dt_result.Rows.Add(ra)).CopyToDataTable()

ConcatColumnValues.xaml (8.0 KB)

Kind Regards,
Ashwin A.K

4

About the first table we would assume that the cell value is to split on Linebreak

So give a try on (assumed that dtResult is prepared as above)

(From d in YourOriginDataTable.AsEnumerable
From x in d.ItemArray
From y in x.ToString.Split({vbLF}, StringSplitOptions.RemoveEmptyEntries)
Select r = dtResult.Rows.Add({y})).CopyToDataTable

5

Giving an Error,
image

One more thing i want to highlight, which is Any cell of column can have more than one data as screenshot in Query.

6

just give me a little time

7

If i change the length of Column, It is giving an error of
“Cannot find Column 3”

8

grafik
grafik1120×770 88.2 KB

grafik

Find starter help here:
LinebreakDemo.xlsx (9.2 KB)
Excel_ManyColsAndLines_ToSingleColStructure.xaml (9.6 KB)

9

Hi @ShekharRPA ,

Sorry, here is the updated code:

(From r In Enumerable.Range(0,dt_sampleData.RowCount)
Let item = Enumerable.Range(0,dt_sampleData.ColumnCount).Select(Function(c) dt_sampleData.Rows(r)(c).ToString).Toarray()
From ra In item
Select dt_result.Rows.Add(ra)).CopyToDataTable()

Kind Regards,
Ashwin A.K

1 Like
10

in short your implementation differs from suggestion e.g. with the datatable cloning which results to more then 1 column. However, above we shared some start help with you

1 Like
11

Thanks @ppr

12

Thanks @ashwin.ashok

13

if There is change in Input data?
image

14

The question is unclear. Just test it as it organized in a dynamical approach

15

Yeah, understood. But if the separator is a Newline, New cell or Column, Or any other thing. So can we make this dynamic? It will work for all cases?

In actual Excel, There will be persons name inside the cells, The Separator of the names will be newline in cell or New cell or Special character line Comma.

16

ok got it. Also Split when comma. Give a try on:

(From i In Enumerable.Range(0,dtData.Columns.Count)
From x In dtData.AsEnumerable.Select(Function (d) d(i))
From y In x.ToString.Split({vbLF,","}, StringSplitOptions.RemoveEmptyEntries)
Select r = dtResult.Rows.Add({y})).CopyToDataTable
17

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