Re: date mask in SQL*Loader

On Feb 10, 10:05 am, "John K. Hinsdale" <h..._at_alma.com> wrote:
> DA Morgan wrote:
> > Define a discard file and send those records back to be fixing.
>
> > Covering for other people's inadequacies is a recipe for working
> > evenings and weekends. And no one will remember you did it three
> > weeks later except to expect you to do it yet again.
>
> I once worked on a web site that took credit card orders.
> The web site instructions said to "please enter the
> credit card number with NO spaces or dashes." It said this
> very loudly, to the point of distraction. Try as they might,
> users entered their credit card numbers with spaces and dashes.
> These were rejected by the system. In fact, the system went
> so far as to detect dashes and say specifically "hey, you had
> a dash in there! take it out!" This behavior was known to
> management but not considered an issue since it was advertised
> that the dashes and spaces were required.
>
> After some time, management did a usability analysis of how
> many users coming to the site, and initiating a purchase
> transaction, but were able to complete the purchase. This is the
> part I was hired to do. We discovered a lot people bailing out
> at the credit card form, specifically after typing in a card
> # with the correct digits, but with those annoying dashes and
> spaces. It didn't take long to understand why they did so:
> the delimiters appear on both their card (as spaces) and
> statements (as dashes) and serve to help chunk up the 16
> or so digits so as to enable easy visual verification. It's
> precisely these people who were making an effort to get the
> correct numbers into the form. Tthere were many other users
> who bailed after entering invalid digits, but far fewer than
> the dashes-and-spaces people.
>
> The usability analysis revealed that about 15% of purchase
> transactions were lost on the last screen due to the dashes
> and spaces thing. I proposed that we simply strip non-digits
> form the form input and use that as the card number. I presented
> a one-line implementation in perl. During this proposal the
> original site developer objected, saying that "if these users
> cannot follow the instructions, we don't want their money."
>
> I'll let you guess what management did with this
> developer ;)
>
> -- JH

I am afraid we don't talk about the same thing here. Your example is about UI processing and data validation in one field in one form.

The whole point of this discussion, at least from my perspective, is to load CSV/Excel files in bulk in the back-end.

Data validation on the UI is one thing and your approach is definitely making a lot of sense. Having an automated or semi-automated back end process is something totally different.

In the UI case, you are talking about a simple problem (validating a CC# for instance) that needs to be run only once.

In the back end processing, we are talking about validating potentially hundreds or thousands of data fields (sometimes it is MUCH more complex that making sure you get 14 or 16 digits in a field).

In the UI, to get a CC#, it is very easy to validate and on top of it, you will probably run a small remote call to some bank processing system to validate the CC# (the work is done for you).

In the back end, you are totally on your own and you must run the full tests/validations yourself, including referential integrity.

In the UI, you have access to an external customer and if you cannot strip out your digits, you can simply send an error message.

In the back end, this is automated. There is nobody to ask for a fix, only a log file/table and maybe sending an email to let someone know that something went wrong.

Back-end processing is a beast of its own. The volume of data and the referential integrity problems are on a different scale. Received on Sat Feb 10 2007 - 22:00:23 CST