Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Flat File Source: prevent records from loading with a valid date but in the wrong format


Flat File Source: prevent records from loading with a valid date but in the wrong format

Author
Message
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
Hi Guys, Is there a simple solution to this.

I have a flat file which is being generated by a 3rd party. They are supposed to pass me dates in the format '2013-03-29' but in a recent file the date was actually '29 MAR 2013' which the system sucessfully parsed and pushed through the data pipe, however I want to error if the data is not in the specified format.


Short of reading it in as a string (rather than a date) and then deriving the dateparts is there a simple way to reject these lines. ( have 48 files to import with around 100 date fields and I don't really want to run this derivation on them all, even if it ends up being a CLR function.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8300 Visits: 19447
If you're a fan of Regular Expressions, you could do a Regex match on it to reject anything other than

NNNN-NN-NN

where N is in the range [0-9].


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
Phil, Thanks but no thanks Smile

I hate regex. The regex would need to treat it as a string (either explicitly or cast in a derived column) and then apply rules such as

1st digit of month can only be 0 or 1. If it is 0 then the second digit can only be 1 thru 9. If the first digit is a 1 then the 2nd digit can only be a 0,1 or 2.

The 1st digit of the day can only be 0,1,2,3. If it is 0 then the second can only be 1-9 if it is 1 or 2 then the second can be 0-9 unless the month is 02 in which case it is only 0-8 unless the year is divisible by 4 but not 100 in which case is 0-9. If the first digit is 3 then the second can only be 0 or 1 unless the month is 02 in which case it is invalid!
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8300 Visits: 19447
aaron.reese (7/23/2013)
Phil, Thanks but no thanks Smile

I hate regex. The regex would need to treat it as a string (either explicitly or cast in a derived column) and then apply rules such as

1st digit of month can only be 0 or 1. If it is 0 then the second digit can only be 1 thru 9. If the first digit is a 1 then the 2nd digit can only be a 0,1 or 2.

The 1st digit of the day can only be 0,1,2,3. If it is 0 then the second can only be 1-9 if it is 1 or 2 then the second can be 0-9 unless the month is 02 in which case it is only 0-8 unless the year is divisible by 4 but not 100 in which case is 0-9. If the first digit is 3 then the second can only be 0 or 1 unless the month is 02 in which case it is invalid!



But it would trap the case you mentioned very easily. If you had included all of these additional requirements in your original post, I would not have suggested it.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search