How to read Data from Excel when it have Multiple Headers

Help Activities
, ,
1

Hello Team,

Kindly help me with my scenario, I have an excel where it has Multiple Headers as below.

image
image1480×138 6.57 KB

and question here, it is possible to change the data table to unique column names for subheader by appending main header to subheaders? like below

image
image1504×136 5.79 KB

Kindly Help me with this, Thanks in advance.!!

2

You’d have to read the data into a datatable with the headers checkbox not checked, so that your headers end up being rows 0 and 1 in the data. Then you loop through the columns and change the column names based on the data in rows 0 and 1. Then delete rows 0 and 1.

3

Hi Paul,

Could please share any sample code please to understand in detail?

And in excel columns are not always side by side some cases columns might be interchanged.

4

I just looked back at your data, and realized there is a simpler way. Just read range and define the range as A2:Axxxx where xxxx is a number large enough to ensure you’ll get all the data. This will be DT_A. Then read range again into DT_B but set the range to F2:Fxxxx. Then merge datatable to combine the two into one.

5

@ramesh_kola

                                 1) Create a template with same header  Assume Datatable as TempDt
                                 2) Read your input excel and skip first two rows (InputDt)
                                 3) Use this query to skip two rows 
                                 Assign   InputDt=  InputDt.asenumerable.skip(2).copytodatatable
                                 4) Use Append Range for template and pass Datatable as InputDt
                                 5) Than  read append range excel
6

Hi @ramesh_kola ,

As observed my @postwick , we do see that the table data or Chunks of data is organized in a Group Table, and each group contains 5 Column data or rather the same Columns.

Hence, this several group of data can be combined into a Single Table/Datatable by Merging the n number of Group tables available dynamically.

We could follow the below Steps :

  1. Use Read Range Activity to read the data as a Datatable, say DT with range specified as “A3” as that is the start of row data.
    image

  2. Next, we create a Output Datatable using Build Datatable Activity with the required column Names as observed/available in one group.
    image

  3. Next we get the Total Number of Groups present in the Data by Dividing the Total Number of Columns by 5. We perform an Iteration upto this number. Selecting the Column Chunks (every 5 columns) and Merging the Selected Columns Data to the Output Datatable.
    image

  4. Next, If required we could write the Output datatable to an Excel file using Write Range Activity.

Check the workflow below :
Excel_Combine_TableChunks.zip (10.1 KB)

Let us know if the method is not agreeable or if the Grouping assumption is wrong.

7

I have resolved the issue, Thanks for your all responses

8

@ramesh_kola ,

We would like to know what was the method you have used to Solve the Issue, If the Method used is not a part of the suggestions listed in this topic, we would like you to post your solution/method so that others facing similar issues will be able to use the method as well.

If the method used is one of the suggestions provided above, do mark the appropriate post as the solution, so other users could benefit from this topic easily.

9

can you please share idea how you resolve this issue ?
appreciate

10

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