Extract Rows from Excel in Power Automate

PDF4me Extract Rows from Excel action enables extraction of specified rows from Excel documents as structured JSON data in Power Automate with comprehensive filtering and formatting options. This powerful data extraction feature supports selective row and column range extraction, automatic header row processing, hidden row/column exclusion, empty row filtering, and flexible data type handling (numeric or text export), perfect for data integration, API feeding, database population, and automated data transformation workflows across Microsoft 365.

Authenticating Your API Request

To access the PDF4me Web API through Power Automate, every request must include proper authentication credentials. Authentication ensures secure communication and validates your identity as an authorized user, enabling seamless integration between your Power Automate flows and PDF4me's powerful Excel data extraction services.

Get your API Key

Key Features

• JSON Output Format: Extract Excel data as structured JSON for seamless integration
• Flexible Row/Column Selection: Specify exact ranges or extract all data
• Header Row Support: Use first row as headers or auto-generate column names
• Advanced Filtering: Exclude hidden rows, hidden columns, and empty rows
• Data Type Control: Export as native types (numbers, dates) or all as text
• Empty Cell Handling: Include or exclude empty cells from output

Parameters

Complete list of parameters for the Extract Rows from Excel action. Configure these parameters to control data extraction.

Important: Parameters marked with an asterisk (***) are required. All row/column indexes use 0-based numbering (Row 0 = first row, Column 0 = column A).

Parameter	Type	Description	Example
File Content***	Base64	Source Excel File Content • Map Excel file from previous action output • Supports Excel files from SharePoint, OneDrive, email attachments • Can be dynamically retrieved from flow variables • Must be valid Excel document (.xlsx, .xls formats)	[File Content from Get File]
File Name***	String	Excel Document Name • Specify Excel file name with extension (.xlsx, .xls) • Used for processing and output file naming • Must include proper file extension • Supports dynamic naming from flow variables	data.xlsx
Worksheet Name	String	Target Worksheet Name • Name of worksheet to extract from • Null or empty defaults to first worksheet (index 0) • Must match worksheet name exactly • Case-sensitive worksheet name	Sheet1
First Row***	Number (int)	Starting Row (0-based) • First row to extract (0-based indexing) • 0-based indexing: Row 0 = Excel row 1, Row 1 = Excel row 2, etc. • Must be ≥ 0 • Required parameter • Note: Error messages display 1-based row numbers	0
Last Row	Number (int)	Ending Row (0-based) • Last row to extract (0-based indexing) • -1 = extract to last row with data (MaxDataRow) • Default: -1 • Must be ≥ FirstRow	-1
First Column	Number (int)	Starting Column (0-based) • First column to extract (0-based indexing) • 0-based indexing: Column 0 = A, Column 1 = B, Column 2 = C, etc. • Default: 0 • Must be ≥ 0	0
Last Column	Number (int)	Ending Column (0-based) • Last column to extract (0-based indexing) • -1 = extract to last column with data (MaxDataColumn) • Default: -1 • Must be ≥ FirstColumn	-1
Has Header Row	Boolean	First Row as Headers • True - Use FirstRow as column headers (excluded from data) • False - Auto-generate headers ("Column1", "Column2"...) • Default: false • Affects data start row	true
Exclude Hidden Rows	Boolean	Skip Hidden Rows • True - Skip rows that are hidden in Excel • False - Include hidden rows in extraction • Default: false • Useful for filtering report data	false
Exclude Hidden Columns	Boolean	Skip Hidden Columns • True - Skip columns that are hidden in Excel • False - Include hidden columns in extraction • Default: false • Useful for filtering report data	false
Exclude Empty Rows	Boolean	Skip Empty Rows • True - Skip rows where all cells are empty • False - Include empty rows in output • Default: false • Improves data quality	false
Export Empty Cells	Boolean	Include Empty Cells • True - Include empty cells with empty string values • False - Omit empty cells from JSON output • Default: false • Affects JSON structure	false
Export Values As Text	Boolean	Text Export Mode • True - Export all values as text strings (e.g., "30", "50000.50", "true") • False - Preserve data types: Numeric → number, DateTime → ISO string, Boolean → boolean, Text → string, Empty → "" (if ExportEmptyCells = true) • Default: false • Data Type Handling: When false, data types preserved from Excel; when true, all values converted to strings	false

Output

The PDF4me Extract Rows from Excel action returns comprehensive JSON output data for seamless Power Automate flow integration:

Table

Table View

Response data in a structured table format:

Parameter	Type	Description
document	Base64	JSON data as byte array (serialized)
RowData	Array	Extracted rows as array of objects with column name keys
Success	Boolean	true if operation successful, false if failed
Error Message	String	Error description (null if successful)
Errors	Array	List of detailed error information (empty array if successful)

JSON

JSON Response Format

The raw JSON response from the action:

{
  "document": "[Base64 JSON Data]",
  "RowData": [
    {
      "Name": "John Doe",
      "Age": 30,
      "Salary": 50000.50
    },
    {
      "Name": "Jane Smith",
      "Age": 28,
      "Salary": 55000.00
    }
  ],
  "Success": true,
  "ErrorMessage": null,
  "Errors": []
}

With Auto-Generated Headers (HasHeaderRow = false):

{
  "RowData": [
    {
      "Column1": "John Doe",
      "Column2": 30,
      "Column3": 50000.50
    },
    {
      "Column1": "Jane Smith",
      "Column2": 28,
      "Column3": 55000.00
    }
  ]
}

Error Response Example:

{
  "document": null,
  "RowData": null,
  "Success": false,
  "ErrorMessage": "Invalid row range: Row 1001 exceeds maximum data row (50)",
  "Errors": [
    {
      "Code": "ROW_RANGE_ERROR",
      "Message": "Row 1001 exceeds maximum data row (50). Valid row range for worksheet 'Sheet1': 1 to 50"
    }
  ]
}

Flow Integration

Power Automate Flow Usage

Use extracted JSON data in subsequent actions:

• API Integration: Send Excel data to REST APIs as JSON payload
• Database Population: Insert extracted rows into SQL databases
• Data Transformation: Process Excel data with Azure Functions or Logic Apps
• CRM Updates: Update Dynamics 365 or Salesforce records with Excel data
• Reporting: Feed Excel data into Power BI or analytics platforms
• Email Distribution: Send extracted data as JSON attachments or email body

Common Error Messages

Understanding and troubleshooting errors helps ensure smooth Excel data extraction workflows:

Error Message	Cause	Solution
"Request is empty"	Request object is null	Provide valid request object
"Document is empty"	Document object is null	Provide valid Excel document
"Workbook contains no worksheets"	Excel file has 0 worksheets	Provide valid Excel file with worksheets
"Worksheet '{name}' not found"	WorksheetName doesn't exist in workbook	Use existing worksheet name
"Invalid worksheet selection"	Worksheet access error	Verify worksheet name and file integrity
"Available worksheets in workbook (X total)"	Informational message when worksheet validation fails	Review worksheet name and use valid value from X available worksheets
"Invalid start row {row}. Row numbers must be positive."	FirstRow < 0	Use FirstRow ≥ 0 (0-based indexing)
"Row {row} exceeds maximum data row ({maxRow})"	LastRow > MaxDataRow	Use valid row range within worksheet data
"Invalid range {start}-{end}: End row cannot be less than start row"	LastRow < FirstRow	Ensure LastRow ≥ FirstRow

Note: Error messages show 1-based row numbers (e.g., Row 1, Row 2) while parameters use 0-based indexing (0, 1).

Workflow Examples

The PDF4me Extract Rows from Excel action in Power Automate provides comprehensive workflow templates designed for real-world business scenarios:

API Integration

Automated Excel to REST API Integration Workflow

Transform your data integration with automated Excel to API data feeding:

Complete Workflow Steps:

1. Trigger: Excel file uploaded to SharePoint "API Data" folder
2. Get Excel File: Retrieve uploaded Excel from SharePoint
3. Extract All Rows: Set FirstRow = 0, LastRow = -1, HasHeaderRow = true
4. Parse JSON: Convert RowData array to individual objects
5. Loop Through Rows: Use Apply to each for RowData items
6. Call REST API: HTTP POST each row to external API endpoint
7. Log Results: Record successful/failed API calls
8. Send Summary: Email admin with processing summary

Business Benefits:

• Integrates 100+ Excel rows with external APIs weekly
• Eliminates manual data entry to API systems
• Provides structured JSON format for API consumption
• Automates data synchronization saving 10 hours weekly

Create Flow

Database Load

Automated Excel to SQL Database Loading Workflow

Streamline your database population with automated Excel data extraction:

Complete Workflow Steps:

1. Trigger: Scheduled daily import at 3 AM
2. Get Excel Report: Retrieve daily data export from SharePoint
3. Extract Data Rows: FirstRow = 1, LastRow = -1 (skip header in row 0)
4. Set Parameters: HasHeaderRow = true, ExcludeEmptyRows = true
5. Parse Extracted JSON: Process RowData array
6. Insert to SQL: Execute SQL INSERT for each row using SQL connector
7. Update Audit Log: Record import timestamp and row count
8. Archive File: Move processed Excel to "Imported" folder

Business Benefits:

• Imports 500+ Excel rows to database daily automatically
• Ensures data consistency with header-based column mapping
• Excludes empty rows for clean data insertion
• Reduces manual database loading by 95%

Create Flow

CRM Synchronization

Automated Excel to Dynamics 365 CRM Sync Workflow

Optimize your CRM updates with automated Excel data extraction:

Complete Workflow Steps:

1. Trigger: Excel contact list updated in SharePoint
2. Get Contact List: Retrieve updated contacts Excel file
3. Extract Contacts: FirstRow = 0, LastRow = -1, HasHeaderRow = true
4. Filter Options: ExcludeHiddenRows = true, ExcludeEmptyRows = true
5. Loop Contacts: Apply to each on RowData array
6. Check CRM: Search Dynamics 365 for existing contact by email
7. Update or Create: Update if exists, create if new
8. Log Sync Results: Record created/updated contact counts

Business Benefits:

• Syncs 200+ contacts from Excel to CRM weekly
• Excludes hidden rows (inactive contacts) automatically
• Maintains data quality by skipping empty rows
• Saves 15 hours per week in manual CRM data entry

Create Flow

Data Validation

Automated Excel Data Quality Check Workflow

Enhance your data quality with automated extraction and validation:

Complete Workflow Steps:

1. Trigger: New data submission Excel uploaded to validation folder
2. Get Submission: Retrieve Excel file from SharePoint
3. Extract All Data: FirstRow = 0, LastRow = -1, HasHeaderRow = true
4. Set Export Mode: ExportValuesAsText = true for consistent validation
5. Loop Through Rows: Apply to each for data validation rules
6. Validate Fields: Check required fields, formats, and business rules
7. Build Error Report: Compile list of validation errors with row numbers
8. Email Submitter: Send validation report with pass/fail status

Business Benefits:

• Validates 50+ data submissions weekly automatically
• Exports all values as text for consistent validation logic
• Provides detailed error feedback to data submitters
• Reduces data quality issues by 80%

Create Flow

Industry Use Cases & Applications

IT & System Integration

IT & System Integration Use Cases

• API Data Feed: Extract Excel data as JSON for REST API consumption
• Database Migration: Export Excel data to SQL, NoSQL, or cloud databases
• ETL Processes: Extract Excel data for transformation and loading pipelines
• System Synchronization: Sync Excel data with multiple business systems

Sales & Marketing

Sales & Marketing Use Cases

• Lead Import: Extract lead lists from Excel for CRM import
• Campaign Data: Export campaign results to marketing automation platforms
• Contact Sync: Synchronize Excel contact lists with email marketing systems
• Sales Data Integration: Feed Excel sales data to analytics platforms

Finance & Accounting

Finance & Accounting Use Cases

• Transaction Import: Extract transaction data for accounting system import
• GL Posting: Export general ledger entries from Excel to ERP systems
• Budget Upload: Extract budget data for financial planning systems
• Reconciliation Data: Export Excel data for automated reconciliation processes

Human Resources

Human Resources Use Cases

• Employee Data Import: Extract employee information for HRIS systems
• Payroll Integration: Export timesheet data to payroll processing systems
• Benefits Enrollment: Extract enrollment data for benefits administration
• Training Records: Export training completion data to LMS platforms

Operations & Supply Chain

Operations & Supply Chain Use Cases

• Inventory Updates: Extract stock level data for inventory management systems
• Order Processing: Export order data from Excel to order management systems
• Supplier Data: Extract vendor information for procurement systems
• Logistics Integration: Feed shipment data to logistics platforms

Education & Research

Education & Research Use Cases

• Student Data Import: Extract student rosters for SIS systems
• Grade Upload: Export grades from Excel to learning management systems
• Research Data: Extract experimental data for analysis platforms
• Survey Results: Export survey responses for statistical analysis tools

Get Help

PDF4me ConnectGet your API KeyPower Automate Documentation