DataMystic

Hi
I am using TextPipe Pro to extract csv formatted records from a file and insert them into a SQL table. All fields in the file are qualified using single quotes.

However field 4, see below, is considered by SQL as an empty string and the Insert fails as field 4 is a numeric field in the DB. In this scenario, my intended solution was to use TextPipe to insert 0 into field 4 but only when it contained an empty string.

'FALSE','2011-03-30 10:11:37','2011-03-30 10:11:37','','2012-11-22 22:34:16'

I think using CSVfield maybe the right approach but I’m new to pattern matching and I can’t find any relevant usage examples.

So how do I reference field 4 then test it for an empty string?

Pointers or answers would be gratefully received.
Thanks

This is pretty easy. Use the Filter Library\Restrict\Delimited fields (CSV, Tab, Pipe, etc), except use a single quote instead of the standard double quote as the delimiter. Then restrict to field 4 only, and just use an exact match to find '' and replace with a zero

Restrict fields:4
| [X] Process fields individually
| [ ] Exclude delimiter
| [ ] Exclude quotes (if present)
| Delimiter type: 0
| Custom delimiter: ,
| Text qualifier : 2
| Custom qualifier: '
| [ ] Has Header
|
+--Replace [''] with [0]
[ ] Match case
[ ] Whole words only
[ ] Case sensitive replace
[X] Prompt on replace
[ ] Skip prompt if identical
[ ] First only
[ ] Extract matches

Yep - it is easy! Thanks.