Page 1 of 1

Updating csv Field Values When Empty

Posted: Mon Jan 27, 2020 10:27 pm
by SoreHeed
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.

Re: Updating csv Field Values When Empty

Posted: Wed Feb 05, 2020 8:59 pm
by DataMystic Support
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

Re: Updating csv Field Values When Empty

Posted: Thu Feb 06, 2020 10:29 pm
by SoreHeed
Yep - it is easy! Thanks.