Microsoft Flow连接SFTP大Excel文件报错，求Get Rows替代方案

Hey there! I get it—dealing with large Excel files in Power Automate (formerly Microsoft Flow) can be a pain when you hit those size limits. Since you can't shrink your source data, here are some reliable workarounds to get that big Excel data into your flow without using the standard "Get Rows" action:

1. Upload to SharePoint/OneDrive First, Use Excel Online Actions #

The standard Excel "Get Rows" action via SFTP has a strict 2MB limit, but Excel Online (Business) has much higher thresholds. Here's how to use it:

• First, use the SFTP - Get file content action to pull the large Excel file's content (this action supports files up to 100MB in most plans).
• Next, use OneDrive for Business - Create file or SharePoint - Create file to upload the file content to your cloud storage.
• Finally, use the Excel Online (Business) - List rows present in a table action to read the data. This operation can handle Excel files well above 2MB, depending on your plan.

2. Convert Excel to CSV (Using Azure Function or Power Automate Desktop) #

CSV files are lighter and easier to parse in Power Automate with fewer size restrictions. Try this approach:

• Grab the file content with SFTP - Get file content.
• Use an Azure Function (write a simple script with OpenPyXL or Pandas to convert Excel to CSV) or Power Automate Desktop (PAD has built-in Excel conversion tools) to turn the Excel file into CSV format.
• Once you have the CSV content, use the Parse CSV action or split the content with Power Automate's string functions to extract your rows.

3. Use Power Automate Desktop (PAD) for Direct File Handling #

PAD has far more flexible limits for large files compared to cloud flows. Here's the workflow:

• In PAD, create a flow that uses SFTP - Download file to save the large Excel to a local temporary folder.
• Use PAD's Excel - Launch Excel and Excel - Read from worksheet actions to pull the data directly from the file.
• Pass the extracted data back to your cloud Power Automate flow to continue your processing steps.

4. Pre-Chunk the Excel File on SFTP #

If you can run scripts on your SFTP server, split the large Excel into smaller files before processing:

• Write a Python or Excel VBA script that splits your large table into multiple smaller Excel files (each under 2MB).
• In Power Automate, use SFTP - List files to get all the split files.
• Loop through each file with the standard Excel - Get Rows action, then merge the results into a single dataset in your flow.

Quick Notes #

• Make sure your Power Automate plan supports the actions you choose (e.g., Excel Online requires a Business plan, PAD needs a dedicated license).
• For Azure Function conversions, adjust the function's memory and timeout settings to handle large files smoothly.
• When merging split files, remember to skip duplicate headers to avoid messy data.

内容的提问来源于stack exchange，提问作者Mufaddal