Extract Data from Multiple Excel File

Help Studio
, , , , , , ,
1

Hello Team,

How can we perform following activity
In Local Drive Documents–>InventoryAutomation–>InputReceived_Documents
1)It contains downloaded Excel Files
2)How can we Read different excel files in InputReceived_Documents Folder which have a standard format
3)Extract the standard details Like
File path/File Name,User Name,User Id No,Brand , Ship_Code , Ship_Id and add to queue
image

There would be multiple Excel files as Test Shipper 1.xlsx
In which the same operaton would be performed as Test Shipper 1.xlsx

Test Shipper 1.xlsx (4.7 KB)

Your ideas are Welcomed

2

Hello @NISHITHA

Assign Activity:

  • Variable: excelFiles
  • Value: Directory.GetFiles(“C:\Users<YourUsername>\Documents\InventoryAutomation\InputReceived_Documents”, “*.xlsx”)

For Each Activity:

  • TypeArgument: String

  • Values: excelFiles

    Excel Application Scope Activity:

    • WorkbookPath: item

    Read Range Activity:

    • Range: Specify the range based on your standard format

    Add Queue Item Activity:

    • QueueName: “YourQueueName”
    • ItemInformation:
      • “FilePath”: item
      • “UserName”: ExtractedUserName
      • “UserId”: ExtractedUserId
      • “Brand”: ExtractedBrand
      • “Ship_Code”: ExtractedShipCode
      • “Ship_Id”: ExtractedShipId

Thanks & Cheers!!!

3

Hello @Kartheek_Battu

Thanks for the Prompt Response

Just one question the data is in Different Format in Excel as attached in Screenshot how can that be extracted
Example:The User Name and User Id No are in Horizontal format
Where as other details we can extract row wise loop

How can we capture that

4

@NISHITHA

Read Range Activity:

  • Range: Specify the range to cover the entire sheet
  • Output: dt (DataTable variable)

Assign Activity:

  • Left side: userName
  • Right side: dt.Rows(0)(“A”).ToString() // Adjust the column and row indices as needed

Assign Activity:

  • Left side: userId
  • Right side: dt.Rows(0)(“B”).ToString() // Adjust the column and row indices as needed

For Each Row Activity:

  • Input: dt
  • Body:
  • Extract other vertical data using row(“ColumnName”) or row(ColumnIndex)