How to read ODS file by Azure Data Factory

0
votes

Overview: I have created Azure Data Factory Pipeline for Copy Data via HTTP and I can successfully download it in Azure Blob or Data Lake Storage in CSV and XLSX format. Also, I can read the file in my local system in Excel Application.

Here, I need help to read the downloaded ODS file (as the file having different sheets).

I found Azure Data Factory is not able to read the file in the different pipelines so that I can access the different sheets, and perform other actions.

enter image description here

1
azureazure-web-app-serviceazure-data-factoryazure-data-factory-2azure-data-factory-pipeline

1 Answers

0
votes

Since you are able to successfully download the file into Azure Blob, you can read the file from Blob, but you will need to specify the SheetName. See this example below:

Specify Dataset Parameters in your Blob Dataset:

Dataset example

Here is a mock excel file stored in Blob (since you said you could get the file downloaded to Blob):

Mock excel

Use the Dataset in your Copy Activity to copy Sheet 2 data to a CSV in same Blob:

SOURCE:

Source details

SINK:

Sink Details

Results of my Pipeline Run:

My Output

This shows that the pipeline successfully targeted Sheet2 and wrote the data over to a CSV file. Please use Dataset Parameters so you can specify sheet names in your pipeline activities. In your case you will need a copy data activity per sheet you want to copy. Cheers.