I have a file which is present as a unstructured txt in ADLS. I want to read the entire file content as text and pass it to a Stored Procedure in Azure data factory. Can this be done through Azure data factory? Below is an example of the file:
Number: 101095
This is a sample 1
ABC
XYZ
END
Number: 101096
This is a sample 2
ABC2
XYZ2
hhhh2
END
Number: 101096
This is a sample 3
ABC3
XYZ3
MMMM3
END
2
Answers
I was able to achieve this through dataflows inside the ADF. As per microsoft documentation - https://learn.microsoft.com/en-us/azure/data-factory/format-delimited-text, copy activity does not allow "no row delimiter" and " no column delimiter". But mapping dataflows do allow for "no row delimiter" and "no column delimiter".
I leveraged mapping dataflow to read the entire file, which had no specified row or column delimiters. Subsequently, the entirety of the file's content was outputted to a database. Following this, a stored procedure was successfully able to retrieve the content from this database.
To read entire content from .txt file you need to use lookup activity.
Add the ADLS dataset with .txt file and column and row delimiter character which is not present in your data example " ` ".
data preview of file:
Then you can access it using
@activity('Lookup1').output.value[0].Prop_0