Datatable conversion

[B.D]

Hallo, I have a datatable, which has only 1 column with data. first I need to convert this datatable into datacolumn type (to remove headers with columnname.trim method), then back to datatable, but with all rows fused together to a single entry as string type.

[jack.chan]

@B.D

convert this datatable into datacolumn type (to remove headers with columnname.trim method)

what do you mean by convert this datatable into datacolumn type (to remove headers with columnname.trim method) ? can you give an example?

[AMRITANShlal]

Hi @B.D

You could simply use Remove Data Column activity and add to datatable

[B.D]

Hi Jack,

let’s say you have a datatable consisting of 4 columns and ~15 rows.
All this data has to go to a escel sheet, with each of these 4 colunns going into 4 column in the sheet (all next to each other) but all the rows merged into just 1 cell/row ?

I tried writing each column seperately in each column of the excel sheet. It would be the fastest way, but I couldn’t get it to work, . Now I am thinking about writing each row seperately like this:
Now when I run it it says something like “row” is part of “mydatatable” and can’t be trnasfered to “mydatatble_row”

[jack.chan]

lets say i hve the following table called dt

1. first i create a new empty datatable (newdt) with the same coluumn headers
   

2. add a new row to newdt (newRow variable is of type “DataRow”
   

3. i then loop over all the columns of newDt, and for each column, i merge all the rows of the same column in dt into an array then join the array with “,” so it becomes a string.
   I do this by assiging:

   String.Join(","c,dt.AsEnumerable.Select(function(row) row.Field(Of String)(column.ColumnName)).ToArray)

   to

   newRow(column.ColumnName)

   This means newRow(“Column1”) = “a,c,e,g”, amd newRow(“Column2”) = “b,d,f,h”

4. now use “Add datarow” activity to add datarow to your newDt
   

   

5. finally, write the newDt to excel
   

   

6. result
   

[B.D]

Hi Jack thanks, I will try it out. How would it be a,b,c,d are written without comma under each other like:

a
c
e
g

[jack.chan]

@jack.chan

String.Join(","c,dt.AsEnumerable.Select(function(row) row.Field(Of String)(column.ColumnName)).ToArray)

if you dont want a comma, just replace

String.Join(","c,dt.AsEnumerable.Select(function(row) row.Field(Of String)(column.ColumnName)).ToArray)

with

String.Join(Environment.Newline,dt.AsEnumerable.Select(function(row) row.Field(Of String)(column.ColumnName)).ToArray)

result looks like this

[B.D]

Thank you very much Jack, it works. I tried to solve this last part for over a week now. I was getting frustrated.

Does write range overwrite the cell content with the new one ?
Because the process writes diffrent results in the same cell,. Is only the data from the last result in the cell ? And if so how can I add instead of overwriting it each time ? It should only overwrite/or empty at the beginning (the old data)…

Edit: I solved it, was kinda dumb to ask. Thanks again Jack

[jack.chan]

no problem, good luck!