Read a file for which there is no “Process File As” available in the connector.
For example, you may want to read an Avro file from the SharePoint connector. However, when you check, there’s no Avro option available under “Process File As.”
A user might attempt to parse the file as CSV instead, which results in errors like:
Error: SharePoint Input (1): 'utf-8' codec can't decode byte 0xa2 in position 36: invalid start byte
Solution
Leverage Designer power.
First, download the file locally using the connector, and then use the Dynamic Input tool to read and parse the file.
Learn more about the Dynamic Input Tool here.
Dynamic Input works with
- SharePoint
- OneDrive
- Google Drive
- ADLS
- Box
Dynamic Output works with
- Box
For a Single File
- In the SharePoint connector, under the Select File section, choose Download file locally for later processing. Then click Run. The file will be downloaded and ready for further processing.
- Check the output and copy the information under the Save Path by selecting the cell and choosing Copy Selected Cells without Headers.
- Insert a Dynamic Input tool into the workflow.
- Set up the Dynamic Input tool:
-
- Click Edit under the Input Data Source Template section.
(Note: This option sets up the template or schema of the file. It is useful when reading multiple files.)
-
- Paste the path you copied in Step 2 into the section shown, and click OK.
-
- Click Run Once.
- Configure the Dynamic Input tool:
-
- In the Read a List of Data Sources section, select Field as Save Path and Action as Change Entire File Path.
- Run the workflow and review the output from the Dynamic Input Tool. Your file should now be parsed correctly and ready for further ETL processing.
For Multiple Files
The process is similar, but you first define which files you want to download. Example:
Then, copy the path of the file you want to use as the template in the Dynamic Input Tool. Example. The user wants the second file to be the template:
Set up the Dynamic Input Tool accordingly: