format problem

[Li Chen]

Hi all,

I have an 2D array and I write it a file using the following script. But
when I open the written file I find the format is not what I want: the
second and third row are not justified as the first row. I wonder how to
correct it.


Thanks,

Li

############
c=[ ['a','b','c'],['c1',2,1],['d1',3,4] ]
c.collect!{|row| row<<"\n"}

File.open('test.txt','w') do |a_file|
c.each do |row|
row.each {|e| ("#{e}"=~/[a-zA-Z]|\n/) ?
(a_file.printf("%s\t",e)) : ( a_file.printf("%.2f\t",e)) }
end
end

########output#############


a b c
c1 2.00 1.00
d1 3.00 4.00


####expected as follow########
a b c
c1 2.00 1.00
d1 3.00 4.00

 

[David Masover]

Think about what's happening when you add a newline to that row. How will that
newline be printed? Specifically, which printf statement are you using?

 

[Li Chen]

c=[ ['a','b','c'],['c1',2,1],['d1',3,4] ]
c.collect!{|row| row<<"\n"}

File.open('test.txt','w') do |a_file|
c.each do |row|
row.each {|e| ("#{e}"=~/[a-zA-Z]|\n/) ?
(a_file.printf("%s\t",e)) : ( a_file.printf("%.2f\t",e)) }
end
end

You might try checking if the element is a number, otherwise treat it as
a string:

c.each do |row|
row.each do |elem|
fmt = Numeric === elem ? "%.2f\t" : "%s\t"
a_file.printf(fmt, elem)
end
a_file.puts
end

Hi Glen,

I think I do try to check if the element is a number by this code:
("#{e}"=~/[a-zA-Z]|\n/) ?
(a_file.printf("%s\t",e)) : ( a_file.printf("%.2f\t",e))

It looks different from yours:
fmt = Numeric === elem ? "%.2f\t" : "%s\t"

So what is the real different here? Why do you use a code line
Numeric===elem ?

Thanks,

Li

 

[David Masover]

I think I do try to check if the element is a number by this code:
("#{e}"=~/[a-zA-Z]|\n/) ?
(a_file.printf("%s\t",e)) : ( a_file.printf("%.2f\t",e))

That's not your issue.

I did give you a hint: It's about the newlines. Now I should ask: Are you a
newbie? If so, there may be some value in giving you a "hint" rather than just
telling you what's wrong.

Anyway, here's a hint: This line here:

c.collect!{|row| row<<"\n"}

What does c look like after that? Each row has a \n on it, right?

If it helps, add this line immediately after that collect:

p c

Now, when you run that loop, each element that includes a letter (or \n) will
run through this:

a_file.printf("%s\t", e)

What will that produce? For example,if you run 'a' through that, you'll get
"a\t", right?

So if you run "\n" through that, what do you get?

Now, if you just want an answer, here's how I'd do it:

c=[ ['a','b','c'],['c1',2,1],['d1',3,4] ]

File.open('test.txt', 'w') do |a_file|
c.each do |row|
puts row.map{|e| e.kind_of?(Numeric) ? sprintf("%.2f", e) : e}.join("\t")
end
end

Does that make sense?

 

[Li Chen]

Does that make sense?

Hi David,

Thank you very much and now I get it.

Li

 

[Li Chen]

Does that make sense?

Hi David,

Thank you for the detailed explanation and I get it. But I have another
question:

When I open the written file I get my expected format correctly. But it
is odd that after I open it with Excel or import it into Excel the data
in one column are not formatted. Is it caused by Excel?


Thanks,

Li

## written file is open with notepad

a b c

c1 2.00 1.00

d1 3.00 4.00

# file is open with Excel

a b c

c1 2 1

d1 3 4

 

[David Masover]

First, I should apologize for anyone following along -- that should be
a_file.puts, not just puts.

When I open the written file I get my expected format correctly. But it
is odd that after I open it with Excel or import it into Excel the data
in one column are not formatted. Is it caused by Excel?

Let's see...

# file is open with Excel

a b c

c1 2 1

d1 3 4

Most likely. My understanding is that Excel doesn't make a distinction between
integers and floats. For example, try creating this table:

1
=A1/2

That second shell should be 0.5, right?

A quick experiment with OpenOffice shows the exact same behavior. I don't really
know of a good way around it. However, a few things to consider:

First, does it matter? It's very quick to format a range of cells in Excel.

Second, do you really want to be doing this by hand? There's a CSV generator
in the Ruby standard library that's at least as easy to use as what you came
up with, probably easier. Watch this:

c=[ ['a','b','c'],['c1',2,1],['d1',3,4] ]

require 'csv'
CSV.open 'test.txt', 'w' do |a_csv|
c.each do |row|
a_csv << row
end
end

This doesn't fix the problem, but it avoids potential future problems -- for
example, if any of your strings had tabs, quotes, commas, etc, that library
will take care of it.

But the fact that you're using exactly two decimal places for everything, and
you're in Excel, tells me this is probably currency of some kind, right? One
possible workaround I noticed is this:

c=[ ['a','b','c'],['c1',2,1],['d1',3,4] ]

require 'csv'
CSV.open 'test.txt', 'w' do |csv|
c.each do |row|
csv << row.map{|x| x.kind_of?(Numeric) ? "$#{x}" : x}
end
end

That adds a dollarsign to all the numbers. Without even adding decimal places,
OpenOffice suddenly sees them as $1.00, $2.00, etc.

Hope that helps.

If Excel behaves differently, I'm not really sure what to tell you...