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

Question

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

Shubham Dhumal 1

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

Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-04-06T18:26:58.247+00:00

Hi @Shubham Dhumal ,
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
Shubham Dhumal 1 Reputation point

2021-04-07T05:19:19.007+00:00

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
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-04-14T21:41:19.283+00:00

@Shubham Dhumal 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
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-04-16T15:39:15.55+00:00

@Shubham Dhumal Please let me know if you have any other questions.

Your answer

Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-04-06T18:26:58.247+00:00

Hi @Shubham Dhumal ,
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
Shubham Dhumal 1 Reputation point

2021-04-07T05:19:19.007+00:00

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
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-04-14T21:41:19.283+00:00

@Shubham Dhumal 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
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-04-16T15:39:15.55+00:00

@Shubham Dhumal Please let me know if you have any other questions.

Share via

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

Your answer