Updating csv Field Values When Empty

Get help with installation and running here.

Moderators: DataMystic Support, Moderators

Post Reply
SoreHeed
Posts: 6
Joined: Fri Jan 24, 2020 12:07 am

Updating csv Field Values When Empty

Post by SoreHeed » Mon Jan 27, 2020 10:27 pm

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

User avatar
DataMystic Support
Site Admin
Posts: 2199
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Updating csv Field Values When Empty

Post by DataMystic Support » Wed Feb 05, 2020 8:59 pm

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
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

SoreHeed
Posts: 6
Joined: Fri Jan 24, 2020 12:07 am

Re: Updating csv Field Values When Empty

Post by SoreHeed » Thu Feb 06, 2020 10:29 pm

Yep - it is easy! Thanks.

Post Reply