Change datatable header row

Help
,
1

Hello,

I am have a bunch of tab-delimited txt files, that I am trying to read into a datatable and eventually output to an excel file. Currently, read the text file to a string variable, and generate a datatable from that string.
It works for the files where there are no column headers at all, or they are the very first row.
But the problem is, that in some cases, the first row of the file is some dummy data, while the second row are the actual headers.

I cannot seem to figure out how to make that second row my datatable headers as well. I can ignore the first row, but then the default headers (Column1…) are added on top. Is there any way to drop the defaults and use second row as the headers?

2

Hi @SSavickas,

Are you using Write Range activity? There’s a parameter there that says AddHeaders
image

image
image1098Ă—486 12 KB

3

The thing is, that I need to do some dt modifactions, before moving them to excel, and they depend on the header names (which do not change).

Even if I could immediately output it, using the write range activity without that option selected, it would output the real headers as a regular data row, as at the dt generation stage, it either takes the first row, or adds dummy headers.

I guess I could ignore the first row of txt, add dummy headers, then change them to whatever value is in the second row of dt one by one and then drop that row. But I am open for any easier suggestions

4

Fine
Hope these steps would help you resolve this
–once after the datatable is ready named dt we can use a assign activity like this
dt.Columns(“yourcolumnname”).ColumnName = “your new column header”
similarly we can mention for all the columns we have
–if we have more than 10 columns and if we want to make the second row in the datatable as header then use a FOR EACH activity where pass this as input dt.Rows(1).ItemArray and change the type argumentt as string in the property panel
–inside the loop use a assign activity with same above expression but with small change
dt.Columns(counter).ColumnName = item.tostring

where counter is a variable of type int32 with default value as 0 defined inthe variable panel
–next to this assign activity use another assign activity to increment the counter value
counter = counter +1

–once after this FOR EACH activity use REMOVE DATAROW activity where mention the row index we want to delete and the datatable name in datatable property

kindly try this and let know for any queries or clarification
Cheers @SSavickas

4 Likes
5

@SSavickas,

Can you check that you have selected the AddHeaders in the “Read Range” activity?

image
image979Ă—160 7.41 KB

Also what do you have in Range in the activity? If you leave it blank it will read all.

6

I do not use read range, as it does not work with txt files. As I mentioned in the original post: read text file to string and generate dt from it

7

were we able to process with the above mentioned steps
Cheers @SSavickas

8

Ok, Generate DT is not always reliable, you can complement this using build datatable and using add data row. The best way is to start with a build a datatable and then input the info using add Data Row so the info doesn’t move places and to make sure you always have the header.
image

9

Nope, does not work. Somehow it only picks up the last value, and changes the header of the first column to it only. Ignores all the others. Debug shows that vairables are looping correctly though. Still trying to debug it now.

10

Do you ahve any more details, why the Generate activity would not be reliable?
For the “add data row” to work, I would need to split my text a couple of times though, which can get messy as well, right?

11

Solved it. Using similar logic to what @Palaniyappan suggested. For future reference:

I loop through columns, by using a for loop:

For Each col in DT.Columns

And then inside the loop, I have an assign activity:

col.ColumnName = GeneratedDT.Rows(1).Item(col.ColumnName.tostring).tostring

then I just drop the rows I do not need.

Thank you both for the suggestions.

8 Likes
12

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