-
Notifications
You must be signed in to change notification settings - Fork 0
/
conversor.py
130 lines (101 loc) · 4.12 KB
/
conversor.py
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
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
128
129
130
#!/usr/bin/env python
import pandas as pd
import xlrd
import sys
import os
import warnings
warnings.simplefilter(action='ignore', category=FutureWarning)
pd.set_option('mode.chained_assignment', None)
def convert_number(number):
result = str(number)
# result = result.replace('.', '')
# result = result.replace(',', '.')
return result
def convert_date(date):
chan = date.split('/')
result = chan[2] + '-' + chan[1] + '-' + chan[0]
return result
def join_texts(t1, t2):
lista = []
t1 = str(t1)
t2 = str(t2)
if 'nan' != t1:
lista.append(t1)
if 'nan' != t2:
lista.append(t2)
result = ' - '.join(lista)
result = result.replace(',', ' ')
return result
def get_name(date):
mes = ['na', 'Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio',
'julio', 'Agosto', 'Septiebre', 'Octubre', 'Noviembre', 'Diciembre']
chan = date.split('-')
return mes[int(chan[1])] + ' ' + chan[0]
def final_date(date):
chan = date.split('-')
mes = str(int(chan[1]) + 1).zfill(2)
return chan[0] + '-' + mes + '-01'
def main():
if len(sys.argv) > 1:
input_file = sys.argv[1]
if len(sys.argv) > 2:
output_file = sys.argv[2]
else:
output_file = os.path.splitext(input_file)[0] + '.csv'
try:
xl = pd.ExcelFile(input_file)
except IOError:
help_string = 'El archivo "' + input_file + '" no se ha podido abrir'
print(help_string)
sys.exit()
except xlrd.XLRDError:
help_string = 'El archivo "' + input_file + '" no parece una hoja de Excell'
print(help_string)
sys.exit()
lista_hojas = xl.sheet_names
df = xl.parse(lista_hojas[0])
headers = df.iloc[14]
ndf = df.iloc[15:]
if headers[4] == "CUENTA":
ndf.columns = ["nada", "nada", "fecha", "nada",
"nada", "nada", "concepto", "nada",
"descripcion", "importe", "saldo",
"nada", "nada", "nada"]
else:
ndf.columns = ["nada", "nada", "fecha",
"nada", "nada", "concepto", "nada",
"descripcion", "importe", "saldo",
"nada", "nada", "nada"]
"""
ndf.columns = ["nada", "fecha", "nada", "nada", "concepto",
"descripcion", "importe", "saldo", "nada", "nada",
"nada"]
ndf.columns = ["nada", "nada", "fecha", "nada", "nada", "concepto", "nada", "descripcion", "importe", "saldo", "nada", "nada", "nada"]
"""
saldo_primero = float(convert_number(ndf['saldo'].iloc[-1]))
mov_ini = float(convert_number(ndf['importe'].iloc[-1]))
saldo_inicial = saldo_primero - mov_ini
ndf.loc[:, 'importe'] = ndf.loc[:, 'importe'].apply(convert_number)
ndf.loc[:, 'saldo'] = ndf.loc[:, 'saldo'].apply(convert_number)
ndf.loc[:, 'fecha'] = ndf.loc[:, 'fecha'].map(convert_date)
ndf['descripcion'] = ndf['descripcion'].combine(ndf['concepto'],
join_texts)
ndf.loc[:, 'vacio'] = ''
final = pd.DataFrame(ndf[['vacio', 'vacio', 'vacio', 'vacio', 'fecha',
'descripcion', 'importe']])
final.columns = ['date', 'name', 'balance_start', 'balance_end_real',
'line_ids/date', 'line_ids/name', 'line_ids/amount']
fecha_inicial = final['line_ids/date'].iloc[-1]
final['date'].iloc[0] = final_date(fecha_inicial)
final['balance_end_real'].iloc[0] = ndf['saldo'].iloc[0]
final['balance_start'].iloc[0] = saldo_inicial
final['name'].iloc[0] = get_name(fecha_inicial)
try:
final.to_csv(output_file, sep=',', encoding='utf-8', index=False)
except IOError:
help_string = 'No se ha podido guardar el archivo ' + output_file
print(help_string)
else:
help_string = 'Uso: ' + sys.argv[0] + ' input.xls [output.csv]'
print(help_string)
main()