How to extract info from excel

Help Studio
1

Here is my problem,

  1. I need to fetch where the table starts in Excel ( that’s not an exact table in Excel, it looks like table format, and I can recognize it by column heading). so I need to remove all other info in Excel expect that table.
  2. after identifying i need to take all columns heading alone for checking.
  3. I need a cell number from active excel cell but it can’t get it.
2

Hi @MICHKEL_ANGLO_J ,

image
image600×652 30.8 KB

I think that file has many headers above and the data that needs to be retrieved below
We’ll read the whole thing first
Then find the location where the data starts and read it again from here
Could you share file, I will code in detail
regards,

3

as i am new to forum, i am forbidden to share my file here. Yes, ur screenshot exactly correct matches mine. but there is no space after column name, there may be full blank column inside sometimes. but column starts some distance from start of page thats right as urs. Here is te sample excel file. i need that table values alone without any white space. I need all column headings separartely to check missed heading in file in exception scenorio
ecl.xlsx (34.6 KB)

image
image1131×628 62.7 KB

h

4

I see
If it’s private you can send me in message if you want code in detail.
In here, we will read all file to get data table call is dt
then use for each row in dt find row header which we need get index → call this is index
then read file again from the index above
we will have data what we need get
eg: with your file, we can find index is 9
regards,

5

in general we can do:

  • read range without header
  • calculate the row index of the column headers - output: eg.idxRow

then

  • read range2 and setting the range to "A" & idxRow.ToString

As an alternate we can postprocess first read range datatable by row removal and column names adaptions

6

Hi @MICHKEL_ANGLO_J,

You can do this in a single step. Once you have the DataTable, you can execute the following LINQ in an assign activity to achieve your target,

dt_YourDT = dt_YourDT.AsEnumerable.SkipWhile(Function(row) Not row(0).ToString.Equals("S.no")).CopyToDataTable

Cheers

7

Hi @MICHKEL_ANGLO_J

Excel Application Scope (FilePath: “yourExcel.xlsx”)
Read Range (Range: “”, Output: dtFullSheet)

intRowIndex = -1
For Each Row in dtFullSheet (Output: rowIndex)
    If condition to identify the start of the table
        intRowIndex = rowIndex
        Break
    End If
Next

If intRowIndex > -1
    Delete Range (Range: "A1:A" + (intRowIndex-1).ToString)
    Delete Range (Range: "A" + (intRowIndex+yourTableHeight).ToString + ":End")
    Read Range (Range: "A" + intRowIndex.ToString, Output: dtColumnHeadings)
    
    For Each column in dtColumnHeadings.Columns
        // Process column names
    Next
End If

// To get the active cell address if Get Active Cell activity is not working
Invoke VBA (Code: yourMacroCode, MethodName: "GetActiveCellAddress")