Copy activity gives format error while reading the excel from SFTP location

Question

Hi there,

The excel worksheet files I receive on SFTP location are in .xlsx format. Still while processing the file thru ADF copy activity to read the individual tabs from it, I am getting this error.

If the same file uploaded on blob storage and thru ADF copy activity while reading the individual tabs it runs without error.

Answer 1

Since the file works correctly when uploaded to blob storage but not from the SFTP location, the issue could be with file encoding, corruption during transfer, or how the SFTP server packages the file for download.

1. Ensure that the file is not being altered during the SFTP transfer, especially if it's being compressed or encoded differently. Try downloading the file manually from the SFTP server and uploading it to blob storage to see if the file is still valid.
2. Make sure the Excel file is truly in .xlsx format and not corrupted. Sometimes, even though the extension is .xlsx, the internal format may not be correct due to issues during the file creation process.
3. Use an ADF pipeline to first download the file from the SFTP location to blob storage and then perform the copy activity from the blob. This workaround could help if the issue is tied to the SFTP connection.
4. Test the same pipeline with another Excel file from the SFTP location to rule out file-specific issues.
5. If possible, check for any updates or patches to Azure Data Factory, especially for connectors dealing with SFTP and Excel files, as some bugs or limitations might have been addressed.

If none of these steps resolve the issue, I suggest examining the file transfer process and reviewing logs from the SFTP server side to see if there are any anomalies in how the file is handled.