Class: Sycsvpro::Table

Inherits:
Object
  • Object
show all
Includes:
Dsl
Defined in:
lib/sycsvpro/table.rb

Overview

Extracts values from a csv file and enables to associate values to key values. Columns can be created dynamically based on the content of columns. Example: File 1 (infile)

Date       | Order-Type | Revenue
01.01.2013 | AZ         | 22.50
13.04.2014 | BZ         | 33.40
16.12.2014 | CZ         | 12.80

File 2 (outfile)

Year | AZ    | BZ    | CZ    | Total
2013 | 22.50 |       |       | 22.50
2014 |       | 33.40 | 12.80 | 46.20

Constant Summary

Constants included from Dsl

Dsl::COMMA_SPLITTER_REGEX

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Dsl

#clean_up, #params, #split_by_comma_regex, #str2utf8, #unstring, #write_to

Constructor Details

#initialize(options = {}) ⇒ Table

Creates a new Table. Options expects :infile, :outfile, :rows and :columns. Optionally a header can be provided. The header can be supplemented with additional column names that are generated due to a arithmetic operation that creates new columns :call-seq:

Sycsvpro::Table.new(infile:  "in.csv",
                    outfile: "out.csv",
                    df:      "%d.%m.%Y",
                    rows:    "1,2,BEGINn3>20END",
                    header:  "Year,c6,c1",
                    key:     "c0=~/\\.(\\d{4})/,c6",
                    cols:    "Value:+n1,c2+c3:+n1",
                    nf:      "DE",
                    pr:      "2",
                    sum:     "TOP:Value,c2+c3",
                    sort:    "2").execute
infile

csv file to operate on

outfile

csv file with the result

df

date format

nf

number format of number values. “DE” e.g. is 1.000,00 where as US is 1,000.00

pr

precision of number values.

rows

rows to consider for operation. Rows that don’t match the pattern will be skipped for operation

header

Header of the csv file

key

Values located at value 0 and subsequent columns

cols

Values added to columns base on a operation or assignment

sum

sum row at specified position top or eof

sort

indicates that the columns have to sorted from index on



69
70
71
72
73
74
75
76
77
78
79
80
81
82
 
# File 'lib/sycsvpro/table.rb', line 69

def initialize(options = {})
  @infile      = options[:infile]
  @outfile     = options[:outfile]
  @date_format = options[:df] || "%Y-%m-%d"
  @row_filter  = RowFilter.new(options[:rows], df: options[:df])
  @header      = Header.new(options[:header], sort: options[:sort])
  @keys        = split_by_comma_regex(options[:key])
  @cols        = split_by_comma_regex(options[:cols])
  @number_format = options[:nf] || 'EN'
  @precision     = options[:pr].to_i if options[:pr]
  prepare_sum_row options[:sum]
  @sort          = options[:sort]
  @rows        = {}
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(id, *args, &block) ⇒ Object

Retrieves the values from a row as the result of a arithmetic operation with #eval. It reconizes

c

string value

n

number value

d

date value



89
90
91
92
93
94
 
# File 'lib/sycsvpro/table.rb', line 89

def method_missing(id, *args, &block)
  return @columns[$1.to_i]            if id =~ /c(\d+)/
  return to_number(@columns[$1.to_i]) if id =~ /n(\d+)/
  return to_date(@columns[$1.to_i])   if id =~ /d(\d+)/
  super
end

Instance Attribute Details

#date_format ⇒ Object (readonly)

date format for date operations



33
34
35
 
# File 'lib/sycsvpro/table.rb', line 33

def date_format
  @date_format
end

#header ⇒ Object (readonly)

header of the outfile



35
36
37
 
# File 'lib/sycsvpro/table.rb', line 35

def header
  @header
end

#infile ⇒ Object (readonly)

infile contains the data that is operated on



27
28
29
 
# File 'lib/sycsvpro/table.rb', line 27

def infile
  @infile
end

#outfile ⇒ Object (readonly)

outfile is the file where the result is written to



29
30
31
 
# File 'lib/sycsvpro/table.rb', line 29

def outfile
  @outfile
end

#row_filter ⇒ Object (readonly)

filter that is used for rows



31
32
33
 
# File 'lib/sycsvpro/table.rb', line 31

def row_filter
  @row_filter
end

#rows ⇒ Object (readonly)

rows of the created table



37
38
39
 
# File 'lib/sycsvpro/table.rb', line 37

def rows
  @rows
end

Instance Method Details

#create_key ⇒ Object

Creates a key from the provided key pattern



147
148
149
150
151
 
# File 'lib/sycsvpro/table.rb', line 147

def create_key
  key = []
  @keys.each { |k| key << evaluate(k, "") }
  key
end

#create_row(key, line) ⇒ Object

Creates a table row based on the column pattern Examples of column patterns

  • Value:+n1 Adds content of column 1 to Value column

  • Value:+n1,c2+c3:+n1 Creates a dynamic column and adds column 1 value

  • c0=~/\.(\d4)/:+n1 Creates dynamic column from regex and adds

    column 1 value



159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/sycsvpro/table.rb', line 159

def create_row(key, line)
  row = rows[key] || rows[key] = { key: key, cols: Hash.new(0) }  
  @cols.each do |col|
    column, formula = col.split(':')
    column = evaluate(column) if column =~ /^\(?c\d+[=~+.]/
    previous_value = row[:cols][column]
    if value = eval("#{row[:cols][column]}#{formula}")
      row[:cols][column] = @precision ? value.round(@precision) : value
      add_to_sum_row(row[:cols][column] - previous_value, column)
    end
  end
end

#create_table_data ⇒ Object

Create the table



103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
 
# File 'lib/sycsvpro/table.rb', line 103

def create_table_data
  processed_header = false

  File.open(infile).each_with_index do |line, index|
    line = line.chomp

    next if line.empty?
    
    line = unstring(line).chomp

    header.process line, processed_header

    unless processed_header
      processed_header = true
      next
    end

    next if row_filter.process(line, row: index).nil?
    
    @columns = line.split(';')

    create_row(create_key, line)
  end

end

#execute ⇒ Object

Executes the table and writes the result to the outfile



97
98
99
100
 
# File 'lib/sycsvpro/table.rb', line 97

def execute
  create_table_data
  write_to_file
end

#write_to_file ⇒ Object

Write table to outfile



130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 
# File 'lib/sycsvpro/table.rb', line 130

def write_to_file
  File.open(outfile, 'w') do |out|
    out.puts header.to_s
    out.puts create_sum_row if @sum_row_pos == 'TOP'
    rows.each do |key, row|
      line = [] << row[:key]
      header.clear_header_cols.each_with_index do |col, index|
        next if index < row[:key].size
        line << row[:cols][col]
      end
      out.puts line.flatten.join(';')
    end
    out.puts create_sum_row if @sum_row_pos == 'EOF'
  end
end