Multiline (block) CSV file processing

Ruby
1

People,

I am looking for suggestions for Ruby utilities (and gems?) for a
flexible, easy method of processing multi-line blocks of CSV text eg in
a CSV file, lines 1-5 are the first block and lines 6-10 are the second
block etc. Then for each block I want to:

  • print the first field of line 1
  • second field of line 2
  • fifth field of line 3
  • tenth field of line 4
  • twelfth field of line 5

as fields of a new line.

Of course I could do this myself from basics but I thought there might
be existing tools that would allow me to do things like easily for
different block sizes, different fields on each line of the block etc

Thanks,

Phil.

Philip R.

Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275)
GPO Box 3411
Sydney NSW 2001
Australia
Fax: +61:(0)2-8221-9599
E-mail: [email protected]

2

On Jan 10, 2008, at 7:47 AM, Phil R. wrote:

  • tenth field of line 4
  • twelfth field of line 5

as fields of a new line.

Well, if I fully understand the request, I would use code like the
following with the fastercsv gem:

#!/usr/bin/env ruby -wKU

require “rubygems”
require “faster_csv”

input = FCSV.open(ARGV.shift)
output = FCSV.open(“filtered.csv”, “w”)

catch(:out_of_lines) do
loop do
lines = Array.new(5) { input.shift or throw :out_of_lines }
output << lines.zip([0, 1, 4, 9, 11]).map { |line, i| line[i] }
end
end

END

Hope that helps.

James Edward G. II

3

James,

On Fri, 2008-01-11 at 00:12 +0900, James G. wrote:

  • second field of line 2

end
end

END

Hope that helps.

Thanks but not quite - say my input file is:

1 2 3 4 5 6 7 8 9 a b c
11 12 13 14 15 16 17 18 19 d e f
21 22 23 24 25 26 27 28 29 g h i
31 32 33 34 35 36 37 38 39 j k l
41 42 43 44 45 46 47 48 49 m n o

The output should be:

1 12 25 j o

Regards,

Phil.

Philip R.

Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275)
GPO Box 3411
Sydney NSW 2001
Australia
Fax: +61:(0)2-8221-9599
E-mail: [email protected]

4

On Jan 10, 11:18 am, Phil R. [email protected] wrote:

1 12 25 j o
File.open(“data.txt”, “r”) do |file|
i = 0
file.each_line do |line|
print line.chomp.split[[1, 2, 5, 10, 12][i]-1] + ’ ’
puts ‘’ if (i = (i + 1) % 5) == 0
end
end

Or, the following might be more fun:

def each_block file
while !file.eof
result = []
5.times { result << file.readline.chomp }
yield result
end
rescue
end

File.open(“data.txt”, “r”) do |file|
each_block(file) do |block|
[1, 2, 5, 10, 12].zip(block).each do |field, line|
print line.split[field-1] + ’ ’
end
puts ‘’
end
end

Brian A.

5

On Jan 10, 2008, at 10:18 AM, Phil R. wrote:

second
Well, if I fully understand the request, I would use code like the
catch(:out_of_lines) do

1 12 25 j o
Surely, I got you close enough to finish it off, right? :wink:

If you’re saying that your file is whitespace separated, as you show
above set :col_sep for FasterCSV. If your data really doesn’t contain
quoted fields as shown above, you probably don’t a CSV parser at all.
You can read with split() and write with join().

If you run into problems or have more specific questions, ask and I’ll
do my best to help.

James Edward G. II

6

Brian,

On Fri, 2008-01-11 at 04:19 +0900, Brian A. wrote:

fields.each do |field|

end
end

Thanks! - now I just need to work out how that actually works and then
work out how I can modify it to use command line parameters.

Regards,

Phil.

Philip R.

Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275)
GPO Box 3411
Sydney NSW 2001
Australia
Fax: +61:(0)2-8221-9599
E-mail: [email protected]

7

Brian,

On Fri, 2008-01-11 at 06:47 +0900, Phil R. wrote:

11 12 13 14 15 16 17 18 19 d e f
def block_extractor file, fields,

File.open(“data.txt”, “r”) do |file|
block_extractor(file, [1,2,5,10,12]) do |fields|
puts fields.join(’ ')
end
end

Thanks! - now I just need to work out how that actually works and then
work out how I can modify it to use command line parameters.

I apologise for replying to my own post but I have had a look at this
and read up about Procs and Lambdas and I can sorta see what you are
doing but would you be so kind as to elaborate on the code a bit? - I
think other people would find it useful as well . .

Also, to generalise the code further, how would you select two of more
fields from each line?

Thanks,

Phil.

Philip R.

Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275)
GPO Box 3411
Sydney NSW 2001
Australia
Fax: +61:(0)2-8221-9599
E-mail: [email protected]

8

On Jan 10, 5:21 pm, Phil R. [email protected] wrote:

yield result

work out how I can modify it to use command line parameters.

I apologise for replying to my own post but I have had a look at this
and read up about Procs and Lambdas and I can sorta see what you are
doing but would you be so kind as to elaborate on the code a bit? - I
think other people would find it useful as well . .

I’d be glad to. What question do you have?

Also, to generalise the code further, how would you select two of more
fields from each line?

Well, this is very much a toy/example program, so I wouldn’t build on
it too much. There is a cost and a benefit to generalization, so it
might be worthwhile to spend some time thinking about how general you
need the function to be.

A simple way to select two or more fields from each line would be to
change from:

[a, b, …]

to:

[ [a1, a2, …] [b1, b2, …] … ]

or possibly use a hash with the key being a block-relative line
number, and the value being a list of field numbers. Or you may want
an external specification of the fields to extract - kind of like HTML
templating in reverse.

Brian A.

9

On Jan 10, 1:40 pm, Brian A. [email protected] wrote:

The output should be:

1 12 25 j o

This is a little more general.

def block_extractor file, fields,
splitter = lambda {|line| line.split }
while !file.eof
result = []
fields.each do |field|
line = file.readline
result << splitter.call(line.chomp)[field-1] if field
end
yield result
end
end

File.open(“data.txt”, “r”) do |file|
block_extractor(file, [1,2,5,10,12]) do |fields|
puts fields.join(’ ')
end
end

10

Brian,

On Sat, 2008-01-12 at 13:20 +0900, Brian A. wrote:

end

Thanks! - now I just need to work out how that actually works and then
work out how I can modify it to use command line parameters.

I apologise for replying to my own post but I have had a look at this
and read up about Procs and Lambdas and I can sorta see what you are
doing but would you be so kind as to elaborate on the code a bit? - I
think other people would find it useful as well . .

I’d be glad to. What question do you have?

I’ll come back to that after having another look at your code but see
below . .

[a, b, …]

to:

[ [a1, a2, …] [b1, b2, …] … ]

or possibly use a hash with the key being a block-relative line
number, and the value being a list of field numbers. Or you may want
an external specification of the fields to extract - kind of like HTML
templating in reverse.

While I was waiting I thought I would go ahead and produce something
that would do exactly what I wanted and then get some feedback on it. I
wanted to be able to run a program with parameters eg

multi_line_cvs.rb filename.txt #lines_in_block #fields_in_line
arraycell1 arraycell2 arraycell3 . .

like:

./t070.rb infile.txt 5 12 0,0 1,1 2,4 3,9 4,11

So I have produced this:

#!/usr/bin/ruby

filename = ARGV.shift
lib = ARGV.shift.to_i # No. Lines In Block
fil = ARGV.shift.to_i # Max. No. of Fields to read In Line

infile = File::open( filename, ‘r’ )

count = 0
array = Array.new( lib ) { Array.new( fil ) }

infile.each { |line|
for field in 0…( fil-1 )
array[ count ][ field ] = line.split( “\t” )[ field ].chomp
end

count += 1

if count == lib
    output = ''

    ARGV.each { |cell|
        output << array[cell.split( ',' )[0].to_i][cell.split( ','

)[1].to_i]
output << “\t”
}

    output.chop
    puts output

    count = 0
    array = Array.new( lib ) { Array.new( fil ) }
end

}

infile.close

and this actually does just what I want and the output is correct on the
example above ie: “1 12 25 j o”

It obviously needs error handling and there are probably other
suggestions people can make to improve/replace it . .

The original question was whether something that would do this already
existed as a gem or library but it appears not . .

Regards,

Phil.

Philip R.

Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275)
GPO Box 3411
Sydney NSW 2001
Australia
Fax: +61:(0)2-8221-9599
E-mail: [email protected]