Messed up records - separator inside field content
If you know what each field is supposed to be, perhaps you could write a regular expression which would match that field type only (ignoring tildes) and capture the match, then replace the original string in the file?
I am provided with text files containing data that I need to load into a postgres database.
The files are structured in records (one per line) with fields separated by a tilde (~). Unfortunately it happens that every now and then a field content will include a tilde.
As the files are not tidy CSV, and the tilde's not escaped, this results in records containing too many fields, which cause the database to throw an exception and stop loading.
I know what the record should look like (text, integer, float fields).
Does anyone have suggestions on how to fix the overlong records? I code in per but I am happy with suggestions in python, javascript, plain english.