question

ShubhamDhumal-1406 avatar image
0 Votes"
ShubhamDhumal-1406 asked SaurabhSharma-msft commented

How to add custom validation on excel/csv/sql ?

I want to add a custom validation like column which contain a date that follows a specific format like mm:yyyy and number with specific range in Azure data factory

azure-data-factoryazure-databricks
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @shubhamdhumal-1406,
Thanks for using Microsoft Q&A !!
Do you want validate if the column being read from Excel or CSV has specific formats like mm:yyyy and falls in specific range ? Could you please elaborate your ask (what you want to achieve, an example would be great). What action would you want your Azure Data Factory to perform when the validation is true or false ?

Thanks
Saurabh

1 Vote 1 ·

yes what actually I want is to define some rules for validation on data present in CSV like if there is a column of date contain the dates (02/10/2020)
so all dates present in that follow the format (mm:yyyy) and there is another column that contains integer data so each value is in a certain range(between 0 and 10) if validation is true then it will store data in database else send mail to a user

0 Votes 0 ·

@shubhamdhumal-1406 I do not think we have a direct way of doing that. You may need to write a custom logic which will be extensive as well as you need to validate each column data value like -
You can try using the Data Flow where you can conditionally split the data based on your date format or if the number is not range. Store the excluded records separately on blob then you can read the blob to send email notification to the user.

Please let me know if you have any follow-up questions.

Thanks
Saurabh

0 Votes 0 ·
Show more comments

0 Answers