-
Notifications
You must be signed in to change notification settings - Fork 0
/
convertCsv.groovy
78 lines (66 loc) · 2.23 KB
/
convertCsv.groovy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
@Grab(group='net.sf.opencsv', module='opencsv', version='2.0')
import au.com.bytecode.opencsv.CSVReader
import au.com.bytecode.opencsv.CSVWriter
// println '<pre>'
def date = new java.text.SimpleDateFormat('MM/dd/yy')
System.in.withReader {
System.out.withWriter { write ->
def (header, records) = new CSVReader(it).readAll().findAll { it.findAll().size() }.with { [it.head(),it.tail()] }
def addr = null
def out = new CSVWriter(write)
def headers = ["Client ID","Member ID","Member Name","Address1","Address2","City","State","Zip","Phone","Email","Fund ID","Fund Name","Amount","Process Date","Settlement Date","Return/Fail Date","Return/Fail Reason","ACH/CC","Payment Type","CCType","Discount Amount","Transaction Fee","Deposit Date","CheckNo","Notes"]
def set = { line, name, value ->
def index = headers.indexOf(name)
if(index >= 0) {
line[index] = value
}
}
def ids = -1
out.writeNext(headers as String[])
records.groupBy {
if(it[0]) {
addr = it[0]
}
addr
}.each { address, recs ->
// first line is the name
def name = address.split('\n').head().split(' ').reverse().with {
[head(),tail().reverse().join(' ')].join(', ')
}
def line = headers.collect {''}
set(line,'Member Name',name)
address.split('\n').tail().reverse().with {
if(!it) return
// parse the last line
head().replaceAll(/^(.*?), (.*?) ([\d-]+)$/) { l, city, state, zip ->
set(line,'City',city)
set(line,'State',state)
set(line,'Zip',zip)
}
// remaining lines are address
tail().reverse().eachWithIndex { addressLine, i ->
set(line,"Address${i+1}".toString(),addressLine)
}
}
// fake an id
set(line,'Member ID',ids--)
recs.each { r ->
def lineCopy = line.clone()
set(lineCopy,'Process Date',date.parse(r[1]).format('yyyy-MM-dd'))
set(lineCopy,'Amount',r[2].replaceAll(',',''))
if(r.size() == 4) {
set(lineCopy,'CheckNo',r[3])
}
if(r.size() == 5) {
// retreat doesn't count
if(r[4] =~ /(?i)retreat/) return
set(lineCopy,'Notes','Recorded Fund: '+r[4])
set(lineCopy,'Fund ID',r[4] =~ /(?i)action/ ? '0003' : r[4] =~ /(?i)com/ ? '0002' : '0001')
} else {
set(lineCopy,'Fund ID','0001');
}
out.writeNext(lineCopy as String[])
}
}
}
}