How to convert a table into one row table

Hi

I would like to convert multiple rows data table into one row’s data table, any idea how to do this?

Before
image

After

The actual table length is much longer than this and many many different lines small table, that’s why I prefer not to use the method to read line by line and write them to excel directly.

Just wondering if I would like to achieve this process within datatable, is there a method to do so?

Also, that would be wonderful if excel has this function, which I didnt get it from google…

Thanks

Hi @jingwang0222
Follow the following steps.

  • Read your data using Read Range and store it in DataTable variable.
  • Use For Each Row to iterate the data-table.
  • Inside it get all four columns in Four different variables using row(“columnName”).tostring.
  • Then Use DataTableVar.Columns.Add(YourVariable) . do this four times for four variables.
  • Assign this to Data.DataColumn variable.
  • Finally Write it using Write Range.

Regards

1 Like
3 Likes

Hi
Thanks for your suggestion.
I was using this solution before actually, but the problem is, my table is quite long, I do not want to define every column name and add them one by one.

Here is a quicker solution FYI:
Main.xaml (9.4 KB)
define every row’s different columns to an array, use for each item to achieve this, then add them to a list, separate the list’s items with comma, write to text file but save it as csv.

define a list: ListA
For each row in datatable, for each item in row.itemArray
add item to ListA

Then write string.Join(",",ListA) to test.csv file.

But the problem of this one is: if the row content has “,” then it will be split into two cells, as text to csv conversion use comma to separate cells. If I found solution I will post here.

Thanks

1 Like

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