change last digit of numeric fields based on rule

Get help with installation and running here.

Moderators: DataMystic Support, Moderators, DataMystic Support, Moderators, DataMystic Support, Moderators

Post Reply
hemashah
Posts: 4
Joined: Tue Jun 09, 2009 2:40 pm

change last digit of numeric fields based on rule

Post by hemashah »

Hi,

I have csv file. I need to replace last digit of numeric field with the following sample rule:

Replace last digit with { when last digit is 0 and it is +ve no
Replace last digit with 1 when last digit is 1 nd it is + ve no
Replace last digit with a when last digit is 1 and it is -ve no.

How can we replace last digit of each column? There are 50 columns in the csv file.

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

Re: change last digit of numeric fields based on rule

Post by DataMystic Support »

Use something like this:

Code: Select all

Restrict fields:Comma-delimited field 1 .. field 50
|  [X] Process fields individually
|    [X] Exclude delimiter
|      [X] Exclude quotes (if present)
|  Delimiter Type: 0
|  Custom delimiter: 
|  [ ] Has Header
|
|--Perl pattern [.$] with []
|     [ ] Match case
|     [ ] Whole words only
|     [ ] Case sensitive replace
|     [ ] Prompt on replace
|     [ ] Skip prompt if identical
|     [ ] First only
|     [ ] Extract matches
|     Maximum text buffer size 4096
|     [ ] Maximum match (greedy)
|     [ ] Allow comments
|     [X] '.' matches newline
|     [ ] UTF-8 Support
|   
+--Perl pattern [] with []
      [ ] Match case
      [ ] Whole words only
      [ ] Case sensitive replace
      [ ] Prompt on replace
      [ ] Skip prompt if identical
      [ ] First only
      [ ] Extract matches
      Maximum text buffer size 4096
      [ ] Maximum match (greedy)
      [ ] Allow comments
      [X] '.' matches newline
      [ ] UTF-8 Support
    
hemashah
Posts: 4
Joined: Tue Jun 09, 2009 2:40 pm

Re: change last digit of numeric fields based on rule

Post by hemashah »

What pattern match can I use so that it checks for +ve and -ve numeric fields?
How can I check for last digit only and replace last digit with predefined character.

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

Re: change last digit of numeric fields based on rule

Post by DataMystic Support »

For a negative number, use

Code: Select all

-[\d.]*(\d)
Then set the replace to send variable 1 to a subfilter which contains your search/replace list.

For a positive number, use

Code: Select all

[^-][\d.]*(\d)
ie 'not a hyphen'
Post Reply