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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
