Read Range misses a Column

[Shinjid]

I have this excel file that looks like this

I read range this file from A2(Since row one has for premium users), until the end.
Problem is, when I output the datatable, it misses the column “Discount” and all it’s row values.
I tried range range from row 1 and it reads it ok, but my headers(column) will obviously be a data row not a column. Is there anyway to fix this without reading it all, removing columns, then add headers? Thank you.

[Palaniyappan (Palaniyappan P)]

Hi

Fine let’s go one by one

For this
Ensure it it Mentioned as “A2” in cell range so that it will cover all the columns for sure

@Shinjid

Problem is, when I output the datatable, it misses the column “Discount” and all it’s row values.

And this

@Shinjid

tried range range from row 1 and it reads it ok, but my headers(column) will obviously be a data row not a column. Is there anyway to fix this without reading it all, removing columns, then add headers

Ofcourse you can read it as you said
And in changing the column name here are the steps

1. first read the range with activity from the fist row and get the datatable as dt

2. now the column names without any name will be like Column-1, Column-2,…

3. then get the second row of the datatable which actually has the column, as array of string like this

arr_headers = dt.Rows(1).ItemArray.Select(Function(a) a.ToString).ToArray()

This will save the Datarow having column names as array of string

4. now skip the first two rows like this so that we can then make the above array as headers

dt = dt.AsEnumerable().Skip(2).CopyToDatatable()

—now use a assign activity like this

counter = 0

Where counter is a variable of type int32

5. now finally use a FOR EACH activity and pass the array variable as input and change the type argument as string

-inside the loop use a Assign activity like this

dt.Columns(counter).ColumnName = arr_headers(counter).ToString

And next to this assign inside the loop use another assign like this to increment counter

Counter = counter + 1

Hope this would help you resolve this

Cheers @Shinjid

[Shinjid]

Thanks for replying! fortunately my read range somehow worked properly now. I tried your guide too, and I know it works cause I tried it in an another xaml file, but for some reason. My super buggy program somehow, doesn’t know how to “+1” on an integer. I am sure of the data type and assign value, it just doesn’t want to add +1.