Data files excel values passing in azure pipelines

Product Forums Katalon Studio
1

Hi Everyone ,
how to pass data values in excel whenever i run in azure pipelines.

in my projects ,
there will changes in data files everytime so we need to have excel files has data files input to call and run it in azure pipelines.
please provide me step by step . so this could help me a lot.

1 Like
2

To pass dynamic Excel data files in Azure Pipelines for Katalon Studio tests, follow these steps:

Step 1: Parameterize Your Katalon Test

Modify your Katalon test scripts to accept a dynamic path to the Excel file.

  1. In your test case:
  • Use GlobalVariable.dataFilePath (or a custom variable) to reference the Excel file.
  • Define the variable in your test script or Profile (e.g., executionProfile).

groovy

// Example: Load data from a dynamic path
String dataFile = GlobalVariable.dataFilePath
ExcelData excelData = new ExcelData(dataFile, "Sheet1")
  1. In profiles/<profile>.json:

json

{
  "name": "default",
  "dataFilePath": "Data/data.xlsx" // Default path (overridden in CI)
}

Step 2: Organize Excel Files in Source Control

Store Excel files in your repository (e.g., a Data/ folder) or a shared location accessible to Azure Pipelines.

your-repo/
├── Tests/
├── Data/
│   └── testdata_202310.xlsx
└── ...

Step 3: Configure Azure Pipeline

Use the azure-pipelines.yml file to:

  1. Check out the repository (includes Excel files).
  2. Set the dynamic data file path via command-line arguments.

Example Pipeline Configuration:

trigger:
  branches:
    include: [main]

pool:
  vmImage: 'ubuntu-latest'

variables:
  dataFile: 'Data/testdata.xlsx' # Default path (override via Azure variables)

steps:
- checkout: self

- task: JavaToolInstaller@0
  inputs:
    versionSpec: '11'
    jdkArchitectureOption: 'x64'

- script: |
    # Download Katalon Runtime Engine (if not pre-installed)
    wget https://katalon.com/download -O katalon.zip
    unzip katalon.zip -d $(Agent.ToolsDirectory)/katalon

- script: |
    $(Agent.ToolsDirectory)/katalon/katalon -noSplash -runMode=console \
    -projectPath="$(System.DefaultWorkingDirectory)/Tests/YourProject.prj" \
    -retry=0 -statusDelay=15 -consoleLog \
    -dataFilePath="$(System.DefaultWorkingDirectory)/$(dataFile)" \
    -testSuitePath="Test Suites/YourTestSuite"
  displayName: 'Run Katalon Tests'

Step 4: Pass Dynamic Excel Paths at Runtime

Override the dataFile variable in Azure Pipelines:

  1. Manual Trigger:
  • When triggering the pipeline, specify a new Excel path under Variables:
dataFile=Data/new_testdata.xlsx
  1. Automated:
  • Use pipeline logic to dynamically set the path (e.g., based on date):
- script: |
    export TODAY=$(date +"%Y%m%d")
    echo "##vso[task.setvariable variable=dataFile]Data/testdata_${TODAY}.xlsx"

Step 5: Verify File Exists in Pipeline

Add a validation step to check if the Excel file exists:

- script: |
    if [ ! -f "$(System.DefaultWorkingDirectory)/$(dataFile)" ]; then
      echo "Error: File $(dataFile) not found!"
      exit 1
    fi
  displayName: 'Validate Excel File Exists'

Step 6: Handle Excel Dependencies

Ensure Excel drivers are available in the pipeline agent:

  • Most Azure agents include Apache POI (used by Katalon for Excel), but confirm in your setup.

Troubleshooting

  • Logging: Add println "Using data file: $dataFile" in your Katalon script to log the path.
  • Permissions: Ensure the pipeline has read access to the Excel file location.

By following these steps, your Azure Pipelines will dynamically pass the correct Excel data file to Katalon Studio during execution.

3

Hi @sandip_devaraj,

Welcome to our community. Thank you for sharing your issue.

To pass data values from an Excel file in Azure Pipelines, I would like to suggest that:

1: Store the Excel File in Your Repository
Ensure your Excel file (e.g., data.xlsx) is stored in your repository so that Azure Pipelines can access it during the pipeline run. You can also store it in Azure Storage or Azure DevOps Artifacts if it’s too large.

2: Install Dependencies
Since Azure Pipelines runs scripts in an agent, ensure that your pipeline includes dependencies for reading Excel files. For example:

  • If using Python, install pandas and openpyxl:
    - script: pip install pandas openpyxl
  displayName: 'Install dependencies'
  • If using PowerShell, install ImportExcel module:
    - script: Install-Module -Name ImportExcel -Scope CurrentUser -Force -AllowClobber
  displayName: 'Install PowerShell Excel Module'

3: Read Excel File and Pass Data as Variables
You need to extract data from the Excel file and pass it to the pipeline.

Option 1: Using Python to Read and Pass Variables
You can create a Python script (read_excel.py) to extract data and set them as environment variables:

import pandas as pd
import os

# Load the Excel file
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")

# Extract specific values (e.g., column A, first row)
value = df.iloc[0, 0]

# Print the value in a way Azure Pipelines can capture
print(f"##vso[task.setvariable variable=excelValue;]{value}")

Then, in your YAML pipeline, run the script and use the variable:

- script: python read_excel.py
  displayName: 'Read Excel Data'

- script: echo $(excelValue)
  displayName: 'Use Extracted Value'

Option 2: Using PowerShell to Read Excel Data
If you prefer PowerShell, create a script (read_excel.ps1):

$excelData = Import-Excel -Path "data.xlsx" -WorksheetName "Sheet1"
$excelValue = $excelData[0].Column1  # Replace Column1 with actual column name

Write-Host "##vso[task.setvariable variable=excelValue]$excelValue"

Then, in your YAML pipeline, run it:

- script: pwsh read_excel.ps1
  displayName: 'Read Excel Data'

- script: echo $(excelValue)
  displayName: 'Use Extracted Value'

Step 4: Run the Pipeline and Use the Data

  • Every time the pipeline runs, it reads the latest Excel file data and passes it as variables to subsequent tasks.
  • Modify the script if you need to extract multiple values or dynamically reference column names.
4

@sandip_devaraj let us know your solution

5

let me try it.

thanks for the info.

1 Like