Get Metadata is failing to extract metadata like structure, exists, file type for large data excel

[RajuArge]

Hi Team,

Get Metadata activity is failing to extract metadata like structure, exists, file type for large data excel file.

Also unable to copy data from it using Copy Activity.  Range option is not working for excel dataset. 

Could you please help us with these issues as soon as possible?

[v-hashadapu]

Hi @RajuArge , thank you for reaching out to the Microsoft Fabric Community Forum.

A. Metadata Activity Failure

The failure of the Get Metadata activity for large Excel files is a known issue. Please try the following:

1. If possible, split the large Excel file into smaller chunks to reduce the size of the metadata being extracted.
2. Implement paging in your API calls to handle large datasets by retrieving metadata in smaller batches.
3. Adjust the timeout settings for the activity to allow more time for processing large files.
4. Clean and optimize your data before importing it into Power BI to reduce processing time and improve performance.

B. Copy Activity Failure

Please try the following steps to troubleshoot this issue:

1. Ensure you are using the latest version of Power BI Desktop.
2. Verify that you have the necessary permissions to access the Excel file and its data.
3. If the range option is not working, consider using alternative methods such as exporting the data to a CSV file and then importing it into Power BI.
4. Use Data Flows for large files, as they allow better scaling and parallelism for larger files.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[RajuArge · Accepted solution]

Hi Team,

I have achieved metadata extraction and large data copy through DataFlow activity as mentioned in the document.

https://learn.microsoft.com/en-us/azure/data-factory/format-excel#handling-very-large-excel-files