I have a problem when exploding text from one column in ms excel and insert it into 2 column in Django models. this is my views.py
class UploadFileForm(forms.Form):
file = forms.FileField()
def import_data(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
request.FILES['file'].save_to_database(
model=Transaksi,
mapdict=['tanggal', 'kode','unit','keterangan', 'nominal'],
)
return HttpResponse("OK", status=200)
else:
return HttpResponseBadRequest('Gagal Upload')
else:
form = UploadFileForm()
return render(request, 'kastransaksi/transaksi_formupload.html', {'form': form})
it's my models.py
class Transaksi(models.Model):
tanggal = models.DateField(null=True, blank=True)
kode = models.CharField(max_length=2, null=True, blank=True)
unit = models.CharField(max_length=2, null=True, blank=True)
keterangan = models.CharField(max_length=100, null=True, blank=True)
nominal = models.IntegerField(null=True, blank=True)
and my excel file consist just three columns
tangal keterangan nominal
19/01/2016 944448800010303282-SPP Raihanah Barmim 205000
19/02/2016 944448800010303282-SPP Raihanah Barmim 205000
I want to split text from 'keterangan' column into my Django model field there are 'kode' and 'unit'. How to do it?
save_to_database is a convenient method to import an excel sheet into a database model only. If the spread sheet needs further data munging, you can always obtain pyexcel sheet and then continue to do further formatting, then call save_to_django_model.
sheet = request.FILES['file'].get_sheet()
...process sheet data..
sheet.save_to_django_model(
model=Transaksi,
mapdict=['tanggal', 'kode','unit','keterangan', 'nominal'],
)
return HttpResponse("OK", status=200)
Here is the example data wrangling code:
>>> import pyexcel as p
>>> s=p.get_sheet(file_name='42381732.tsv')
>>> s
42381732.tsv:
+------------+----------------------------------------+---------+
| tangal | keterangan | nominal |
+------------+----------------------------------------+---------+
| 19/01/2016 | 944448800010303282-SPP Raihanah Barmim | 205000 |
+------------+----------------------------------------+---------+
| 19/02/2016 | 944448800010303282-SPP Raihanah Barmim | 205000 |
+------------+----------------------------------------+---------+
>>> rows = [row.split() for row in s.column[1][1:]]
>>> rows
[[u'944448800010303282-SPP', u'Raihanah', u'Barmim'], [u'944448800010303282-SPP', u'Raihanah', u'Barmim']]
>>> rows= [[row[0], ' '.join(row[1:])] for row in rows]
>>> rows
[[u'944448800010303282-SPP', u'Raihanah Barmim'], [u'944448800010303282-SPP', u'Raihanah Barmim']]
>>> rows = [['kode', 'unit']] + rows
>>> rows
[['kode', 'unit'], [u'944448800010303282-SPP', u'Raihanah Barmim'], [u'944448800010303282-SPP', u'Raihanah Barmim']]
>>> s.extend_columns_with_rows(rows)
>>> s
42381732.tsv.tsv:
+------------+----------------------------------------+---------+------------------------+-----------------+
| tangal | keterangan | nominal | kode | unit |
+------------+----------------------------------------+---------+------------------------+-----------------+
| 19/01/2016 | 944448800010303282-SPP Raihanah Barmim | 205000 | 944448800010303282-SPP | Raihanah Barmim |
+------------+----------------------------------------+---------+------------------------+-----------------+
| 19/02/2016 | 944448800010303282-SPP Raihanah Barmim | 205000 | 944448800010303282-SPP | Raihanah Barmim |
+------------+----------------------------------------+---------+------------------------+-----------------+