One of the things computers are good at is moving data. When you have to migrate data from one type to another, I have found that Ruby makes my job a lot easier. A while back I had this a task that involved moving data. We were getting dozens of data sets that needed to be converted from CSV to a fixed length text file. This is the story of how we got it done.
Defining the Finished Output.
We are given a, let’s say, definition file on how the data needed to be formatted for importing. Let’s say it looked like this.
FIELD NAME FORMAT
NAME A(50)
ADDRESS1 A(50)
ADDRESS2 A(50)
CITY A(50)
STATE A(2)
ZIP A(10)
CONTACT A(50)
CONTACTPHONE A(10)
ACCOUNTOPENED 9(8)
What does this mean? We’re really focused on the format. ‘A’ means alphanumeric with white space added to the right. ‘9’ means numeric with white space added to the left. The number means length of the field, so ‘A(10)’ is a alphanumeric field of 10 characters.
Our source data is in a CSV file. Something like:
name,address1,address2,city,state,zip,contact,contactPhone,accountOpened
Wonder widgets,1600 Vassar Street,,dallas,dallas,tx,75220,Tim Smith,214-555-1212,12052001
Timmy's Bikes,2723 Auburn Street,Building 3,Erie,ERIE,PA,16508-1234,,814-555-4321,03232011
The output just needs to be a text file.
Wonder widgets 1600 Vassar Street dallas tx75220 Tim Smith 214-555-121220011205
Timmy's Bikes 2723 Auburn Street Building 3 Erie PA16508-1234 814-555-432120110323
How would you do that?
Time to Start Transmogrifing
How would you transmogrify the data? From the target definition, we know the name will be 50 characters long. You can write a method to take the name column and make it 50 characters long. Want to plow down that path? We can re-factor later. Remember you have to pass in text.
def nameFixing(name)
name = name.ljust(50)
end
Good old ljust will add the extra white space we need. Does this seem to easy? It is Ruby, but maybe we should test this.
require 'test/unit'
def nameFixing(name)
name = name.ljust(50)
end
class NameLengthTest < Test::Unit::TestCase
def test_name_short_should_equal_50
name = nameFixing "boo"
assert_equal(name.length, 50)
end
end
Save that file and go ahead and run the test.
$ ruby ljust_test.rb
Loaded suite ljust_test
Started
.
Finished in 0.000802 seconds.
1 tests, 1 assertions, 0 failures, 0 errors, 0 skips
Test run options: --seed 27164
Sweet, It did work.
What happens if it’s more than 50 characters long? Go ahead and take stab at it.
require 'test/unit'
def nameFixing(name)
name = name.ljust(50)
end
class NameLengthTest < Test::Unit::TestCase
def test_name_short_should_equal_50
name = nameFixing "boo"
assert_equal(name.length, 50)
end
def test_long_short_should_equal_50
name = nameFixing "dgrtefdgdfshrtyutyuykhjkmbnmvcbdfgrthjghjgghvdfgrstthg"
assert_equal(name.length, 50)
end
end
$ ruby ljust_test.rb
Loaded suite ljust_test
Started
F.
Finished in 0.001179 seconds.
1) Failure:
test_long_short_should_equal_50(NameLengthTest) [ljust_test.rb:15]:
<54> expected but was
<50>.
2 tests, 2 assertions, 1 failures, 0 errors, 0 skips
Test run options: --seed 40377
Did you expect that? ljust
adds white space, but does not slice off extra characters. What do you think would slice off the extra characters? You got it slice.
If the name is less than 50 we need to add white space to the left. If the name is over 50 we need to slice off the extra characters. If the name has 50 characters we leave it alone. Did we just rewrite our tests?
require 'test/unit'
def nameFixing(name)
case
when name.length 50
name = name.slice(0..49)
when name.length < 50
name = name.ljust(50)
else
name
end
end
class NameLengthTest < Test::Unit::TestCase
def test_name_short_should_equal_50
name = nameFixing "boo"
assert_equal(name.length, 50)
end
def test_name_long_should_equal_50
name = nameFixing "dgrtefdgdfshrtyutyuykhjkmbnmvcbdfgrthjghjgghvdfgrstthg"
assert_equal(name.length, 50)
end
def test_name_50_should_equal_50
name = nameFixing "dgrtefdgdfshrtyutyuymbnmvcbdfgrthjghjgghvdfgrstthg"
assert_equal(name.length, 50)
end
end
Do you think this will pass? Try it.
Now that you have a way to make the name the proper length, we need to continue with the other columns. Did you notice a pattern with the definitions? Alphanumeric or numeric and a certain length.
Why don’t we go ahead and tackle the length of the column. You should just be able to pass in a variable to set that. Try it.
require 'test/unit'
def nameFixing(name,len)
case
when name.length len
name = name.slice(0..(len-1))
when name.length < len
name = name.ljust(len)
else
name
end
end
class NameLengthTest < Test::Unit::TestCase
def test_name_short_should_equal_50
name = nameFixing "boo",50
assert_equal(name.length, 50)
end
def test_name_long_should_equal_50
name = nameFixing "dgrtefdgdfshrtyutyuykhjkmbnmvcbdfgrthjghjgghvdfgrstthg",50
assert_equal(name.length, 50)
end
def test_name_50_should_equal_50
name = nameFixing "dgrtefdgdfshrtyutyuymbnmvcbdfgrthjghjgghvdfgrstthg",50
assert_equal(name.length, 50)
end
end
Now you’re passing in the length we need to set it too. Save and rerun the test. Remember Check yourself before you wreck yourself. I need to practice that too.
$ ruby ljust_test.rb
Loaded suite ljust_test
Started
...
Finished in 0.000863 seconds.
3 tests, 3 assertions, 0 failures, 0 errors, 0 skips
Test run options: --seed 30117
Still green. We can set the the length to whatever we want. We’ve only really worked with the alphanumeric side. For numeric we need to add white space to the left side. We haven’t checked to see if we are adding white space to the right side of the string. Why don’t you go ahead and add that to the test. I’ll wait.
def test_name_should_have_white_space_on_the_right
name = nameFixing "should be 18", 18
assert_equal(name, "should be 18 ")
end
Are your tests passing? Cool. Let’s move on to numeric columns. Pretty much the same idea but white space on the left. Here’s my quick and dirty code
require 'test/unit'
def nameFixing(name,len)
case
when name.length len
name = name.slice(0..(len-1))
when name.length < len
name = name.ljust(len)
else
name
end
end
def numbFixing(numb,len)
case
when numb.length len
numb = numb.slice(0..(len-1))
when numb.length < len
numb = numb.rjust(len)
else
numb
end
end
class NameLengthTest < Test::Unit::TestCase
def test_name_short_should_equal_50
name = nameFixing "boo",50
assert_equal(name.length, 50)
end
def test_name_long_should_equal_50
name = nameFixing "dgrtefdgdfshrtyutyuykhjkmbnmvcbdfgrthjghjgghvdfgrstthg",50
assert_equal(name.length, 50)
end
def test_name_50_should_equal_50
name = nameFixing "dgrtefdgdfshrtyutyuymbnmvcbdfgrthjghjgghvdfgrstthg",50
assert_equal(name.length, 50)
end
def test_name_should_have_white_space_on_the_right
name = nameFixing "should be 18",18
assert_equal(name, "should be 18 ")
end
def test_numb_should_have_white_space_on_the_left
numb = numbFixing "123",8
assert_equal(numb, " 123")
end
end
Go ahead, try it. Is it green? Excellent but we’re not very DRY.
Time to Refactor.
We con combine the methods and when we need to add white space we could put an if in there for alphanumeric or numeric. Let’s try that.
require 'test/unit'
def allThingsFixing(data,len,type)
case
when data.length len
data = data.slice(0..(len-1))
when data.length < len
if type == "A"
data = data.ljust(len)
else
data = data.rjust(len)
end
else
data
end
end
class NameLengthTest < Test::Unit::TestCase
def test_name_short_should_equal_50
data = allThingsFixing "boo",50,"A"
assert_equal(data.length, 50)
end
def test_name_long_should_equal_50
data = allThingsFixing "dgrtefdgdfshrtyutyuykhjkmbnmvcbdfgrthjghjgghvdfgrstthg",50,"A"
assert_equal(data.length, 50)
end
def test_name_50_should_equal_50
data = allThingsFixing "dgrtefdgdfshrtyutyuymbnmvcbdfgrthjghjgghvdfgrstthg",50,"A"
assert_equal(data.length, 50)
end
def test_name_should_have_white_space_on_the_right
data = allThingsFixing "should be 18",18,"A"
assert_equal(data, "should be 18 ")
end
def test_numb_should_have_white_space_on_the_left
data = allThingsFixing "123",8,"X"
assert_equal(data, " 123")
end
end
Did you rerun the test? Did they all pass? Brilliant.
How to Handle the Date.
According to the definition, date needs to be yyyymmdd
and luckily we are receiving it mmddyyyy
. We just need to cut it up and rearrange it.
Will you be able to pass that data blindly into our method? We do know that the name of the column is “accountOpened.” To keep things moving we could pass a column name into the method. Time to write a test.
def test_date_should_format_properly
data = allThingsFixing "08152003",8,"9","accountOpened"
assert_equal(data, "20030815")
end
Did you remember to add the new variable you’re passing into the method?
Here’s what I did with the method
def allThingsFixing(data,len,type,column)
case
when data.length len
data = data.slice(0..(len-1))
when data.length < len
if type == "A"
data = data.ljust(len)
else
data = data.rjust(len)
end
else
data
end
end
You can go ahead and run the test. It should fail.
$ ruby ljust_test.rb
Loaded suite ljust_test
Started
F.....
Finished in 0.001405 seconds.
1) Failure:
test_date_should_format_properly(NameLengthTest) [ljust_test.rb:52]:
<"08152003"expected but was
<"20030815">.
6 tests, 6 assertions, 1 failures, 0 errors, 0 skips
Test run options: --seed 25697
Now you just need to reformat the date. It looks like we just need to move the year from the back to the front. Now where should we put this? Since there are eight digits in the column and it calls for eight digits in the output, we’ll put it in the section of its where data.len == len
def allThingsFixing(data,len,type,column)
case
when data.length len
data = data.slice(0..(len-1))
when data.length < len
if type == "A"
data = data.ljust(len)
else
data = data.rjust(len)
end
else
if column == "accountOpened"
data = data.slice(4..7)+data.slice(0..3)
else
data
end
end
end
There seem to be a lot of if statements. That can be re-factored later. Does the test pass?
$ ruby ljust_test.rb
Loaded suite ljust_test
Started
......
Finished in 0.001030 seconds.
6 tests, 6 assertions, 0 failures, 0 errors, 0 skips
Test run options: --seed 48958
Transmogrification Complete!
How would you get the data into the Transmogrifier? Stay tuned!
John is a Dallas-based front-end/back-end web developer with 15+ years experience. His professional growth has come from big corporate day jobs and weekend freelance. His is enjoys working the Ruby the most these days and has even added pain to the process by developing Rails for Windows! He’s had many years of enjoyment with Cold Fusion and has strong background in web standards.