On Oct 26, 2007, at 16:06 , Tom Hart wrote:
> What I'd like to do is add a field is_ok and then use sql or php
> (or whatever else, if there's an easier way) to determine the field
> datatype (text, numeric, bool, etc.) and then use some regex or
> something along those lines to attempt to verify that the data is
> good, and then mark the is_ok field (obviously a bool) as true, and
> use is_ok = TRUE in the insert/select statement.
This is one way to do it. Another option would be to process the csv
file beforehand using your favorite scripting language and COPY into
tables with the appropriate, expected datatypes. You know you haven't
processed the text file adequately if the COPY fails.
If you're going to do the cleaning inside the database (i.e., all
text fields), I'd stay inside the database using SQL and PL functions
rather than connecting to the database via some external script,
process the data, and stick it back in: that's just added overhead,
and if the process is automated enough to the point you can write a
script to handle it, pretty much anything you can do in an external
script you can do inside the database without the overhead of round-
tripping out and back.
However, if the cleaning is going to take significant user
interaction, you might consider using a simple web app that would
connect to the database so a user could view and clean the data. I
did this with some success for manually checking if names in an
imported file were properly split along given and family name lines.
Michael Glaesemann
grzm seespotcode net