# Alphanumeric data clean up

**URL:** <https://www.ruby-forum.com/t/alphanumeric-data-clean-up/151787>\
**Category:** Ruby\
**Created:** [October 25, 2008, 6:00pm UTC](https://www.ruby-forum.com/t/alphanumeric-data-clean-up/151787 "2008-10-25T18:00:32Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Charles\_LSSnyder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@Charles\_LSSnyder](https://www.ruby-forum.com/u/Charles_LSSnyder)\
**Post date:** [October 25, 2008, 6:00pm UTC](https://www.ruby-forum.com/t/alphanumeric-data-clean-up/151787/1 "2008-10-25T18:00:32Z")

</div>

Hi

I have a .csv file with terrible mixed text and numeric data:

Case ID,DATE,SERIAL,NAME,Gender,Code1,Code2  
A822DEA04BA7814B011074A096C687B7,2008-09-05  
10:28:38.47,1013726,Smith,Male,550.92 RED INGUINAL HERNIA,49500  
Inguinal Hernia Reducible \>6 Months \< 5 Years  
BEC7E6B8B4F0C53544BA9D2524A3746B,2008-09-04  
10:29:47.81,1035331,Bailey,Male,752.51 UNDESCENDED TESTIS,54692 LAPS  
ORCHIOPEXY F/INTRA-ABDL TSTIS  
…hundreds more lines…

I need to extract -\>  
date as: mo/day/Year  
Code1 as: numeric part only  
Code2 as: numeric part only  
eg.,

DATE,SERIAL,Code1,Code2  
9/5/2008,1013726,550.92,49500  
9/4/2008,1035331,752.51,54692

I have tried  
f = File.open(“datadump.csv”, “r”)  
v = File.open(“results.txt”, “w”)

f.each\_line do |z|  
myarray=[]  
myarray=z.chomp.split(",")  
myarray.each do |g|  
g = g.gsub(/\D/,’’)  
puts g  
end  
end

… no good  
(and it doesn’t address the date conversion)…

Any help appreciated!

clsnyder

---

<div class="post-metadata">

**Author:** ![Charles\_LSSnyder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@Charles\_LSSnyder](https://www.ruby-forum.com/u/Charles_LSSnyder)\
**Post date:** [October 25, 2008, 6:22pm UTC](https://www.ruby-forum.com/t/alphanumeric-data-clean-up/151787/2 "2008-10-25T18:22:13Z")

</div>

FasterCSV [[http://fastercsv.rubyforge.org/](http://fastercsv.rubyforge.org/) ] should help you tokenize  
data  
from the file. Once you have that, you should be able to munge the  
values as  
you see fit.

Regards,  
Craig

---

<div class="post-metadata">

**Author:** ![Charles\_LSSnyder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@Charles\_LSSnyder](https://www.ruby-forum.com/u/Charles_LSSnyder)\
**Post date:** [October 25, 2008, 6:31pm UTC](https://www.ruby-forum.com/t/alphanumeric-data-clean-up/151787/3 "2008-10-25T18:31:34Z")

</div>

On Oct 25, 2008, at 10:59 AM, Charles L. Snyder wrote:

> DATE,SERIAL,Code1,Code2  
> 9/5/2008,1013726,550.92,49500  
> 9/4/2008,1035331,752.51,54692

How’s this:

$ cat in.csv  
Case ID,DATE,SERIAL,NAME,Gender,Code1,Code2  
A822DEA04BA7814B011074A096C687B7,2008-09-05  
10:28:38.47,1013726,Smith,Male,550.92 RED INGUINAL HERNIA,49500  
Inguinal Hernia Reducible \>6 Months \< 5 Years  
BEC7E6B8B4F0C53544BA9D2524A3746B,2008-09-04  
10:29:47.81,1035331,Bailey,Male,752.51 UNDESCENDED TESTIS,54692 LAPS  
ORCHIOPEXY F/INTRA-ABDL TSTIS  
Galactica:~/Desktop$ cat filter\_csv.rb  
#!/usr/bin/env ruby -wKU

require “date”

require “rubygems”  
require “faster\_csv”

FCSV.filter(:headers =\> true, :return\_headers =\> true) do |row|

# remove unwanted columns

row.headers.each do |h|  
row.delete(h) unless %w[DATE SERIAL Code1 Code2].include? h  
end

unless row.header\_row?  
# convert the DATE field  
row[“DATE”] = Date.strptime(row[“DATE”]).strftime("%m/%d/%Y")  
# convert CodeN fields  
1.upto(2) do |n|  
f = “Code#{n}”  
row[f] = row[f].to\_s[/\d+(?:.\d+)?/]  
end  
end  
end  
Galactica:~/Desktop$ ruby filter\_csv.rb in.csv \> out.csv  
Galactica:~/Desktop$ cat out.csv  
DATE,SERIAL,Code1,Code2  
09/05/2008,1013726,550.92,49500  
09/04/2008,1035331,752.51,54692

Hope that helps.

James Edward G. II

---

<div class="post-metadata">

**Author:** ![Charles\_LSSnyder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@Charles\_LSSnyder](https://www.ruby-forum.com/u/Charles_LSSnyder)\
**Post date:** [October 26, 2008, 1:40am UTC](https://www.ruby-forum.com/t/alphanumeric-data-clean-up/151787/4 "2008-10-26T01:40:52Z")

</div>

Charles L. Snyder wrote:

> Case ID,DATE,SERIAL,NAME,Gender,Code1,Code2  
> A822DEA04BA7814B011074A096C687B7,2008-09-05  
> 10:28:38.47,1013726,Smith,Male,550.92 RED INGUINAL HERNIA,49500  
> Inguinal Hernia Reducible \>6 Months \< 5 Years  
> BEC7E6B8B4F0C53544BA9D2524A3746B,2008-09-04  
> 10:29:47.81,1035331,Bailey,Male,752.51 UNDESCENDED TESTIS,54692 LAPS  
> ORCHIOPEXY F/INTRA-ABDL TSTIS

DATA.each\_line{|line|  
ary = line.split(’,’).values\_at(1,2,5,6).  
map{|s| s[/^\S\*/] }  
ary[0] = ary[0].split("-").map{|s| s.to\_i}.  
values\_at(1,2,0).join("/")  
puts ary.join(",")  
}

**END**  
A822DEA04BA7814B011074A096C687B7,2008-09-05  
10:28:38.47,1013726,Smith,Male,550.92 RED INGUINAL HERNIA,49500  
Inguinal Hernia Reducible \>6 Months \< 5 Years  
BEC7E6B8B4F0C53544BA9D2524A3746B,2008-09-04  
10:29:47.81,1035331,Bailey,Male,752.51 UNDESCENDED TESTIS,54692 LAPS  
ORCHIOPEXY F/INTRA-ABDL TSTIS

— output —  
9/5/2008,1013726,550.92,49500  
9/4/2008,1035331,752.51,54692

---

<div class="post-metadata">

**Author:** ![Charles\_LSSnyder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@Charles\_LSSnyder](https://www.ruby-forum.com/u/Charles_LSSnyder)\
**Post date:** [October 28, 2008, 1:35am UTC](https://www.ruby-forum.com/t/alphanumeric-data-clean-up/151787/5 "2008-10-28T01:35:55Z")

</div>

Those work perfectly

Thanks again for the help!

cls