I am trying to get data from a csv file that is uploaded via a ReactJs front end and then import it into a sqlite database in a Django/Python backend.
I am trying to use the same code for several different imports and I have at least two cases that work but when I try to replicate the code, it is not working.
It does seem that a csv generated from Excel or from another system does work but I have a mac and if I try to edit the csv either using my VS Code editor or using Numbers (but exporting as a csv) the file then doesn't work. If I inspect the file in VS code though, I can't see any difference between the ones that work and the ones that do not. All are in utf.
Is this a problem with the csv? And is there code I can add to diagnose that... and maybe rectify?
FRONTEND:
import axios from 'axios'
import { formatTimeDate } from '../../lib/helpers'
class CSVDataLoader extends Component {
constructor() {
super()
this.state = {
loading: false,
updated: ''
}
this.handleSubmitData = this.handleSubmitData.bind(this)
this.handleFile = this.handleFile.bind(this)
}
handleFile (e) {
e.preventDefault()
const fileToUpload = e.target.files[0]
this.setState({
fileToUpload: fileToUpload
})
}
async handleSubmitData(e) {
e.preventDefault()
this.cancelTokenSource = axios.CancelToken.source()
this.setState({ loading: true })
const formData = new FormData()
formData.append('file', this.state.fileToUpload)
try {
const retrievedData = await axios.post(this.props.url, formData, {
headers: {
'Content-Type': 'multipart/form-data'
},
cancelToken: this.cancelTokenSource.token
})
console.log(retrievedData.data)
this.setState({ updated: Date.now(), loading: false })
} catch (err) {
if (axios.isCancel(err)) {
// ignore
} else {
// propegate
throw err
}
} finally {
this.cancelTokenSource = null
}
}
componentWillUnmount() {
this.cancelTokenSource && this.cancelTokenSource.cancel()
}
render() {
const { loading } = this.state
return (
<form onSubmit={this.handleSubmitData}>
<div className="file has-name is-right">
<label className="file-label">
<input
className="file-input"
type="file"
name="file"
multiple={false}
accept=".xls,.xlsx,.csv,.txt"
onChange={this.handleFile}
/>
<span className="file-cta">
<span className="file-icon">
<i className="fas fa-upload"></i>
</span>
<span className="file-label">
Choose a file…
</span>
</span>
<span className="file-name">
Select the csv file to import ...
</span>
</label>
</div>
<button className={`${this.props.class} button is-primary`}>
{loading && <span className="spinner"><i
className="fas fa-spinner fa-spin"
/>Loading ...</span>
}
{!loading && <span>{this.props.buttonText}</span>}
</button>
<p><small>{!this.state.updated ? '' : `Updated: ${formatTimeDate(this.state.updated)}`}</small></p>
</form>
)
}
}
export default CSVDataLoader
BACKEND
MODEL
from django.db import models
class EventOrder(models.Model):
event=models.CharField(max_length=40)
event_order=models.IntegerField()
VIEW
import os
import csv, sys
import tempfile
from rest_framework.views import APIView
from rest_framework.response import Response
from rest_framework.parsers import MultiPartParser, FormParser, ParseError
from ..serializers import WriteEventOrderSerializer
from ..models import EventOrder, Crew
class EventOrderImport(APIView):
# This function ATTEMPTS to import the csv from frontend
# Start by deleting all existing event cats
parser_classes = (FormParser, MultiPartParser)
def post(self, request):
EventOrder.objects.all().delete()
# Convert the InMemoryUploadedFile to a NamedTemporaryFile
for csv_upload in request.FILES.values():
file_temp = tempfile.NamedTemporaryFile()
file_temp.write(csv_upload.read())
print(file_temp.name) # This is the path.
with open(file_temp.name, newline='') as f:
reader = csv.reader(f)
next(reader) # skips the first row
for row in reader:
if row:
data = {
'event': row[0],
'event_order': row[1]
}
serializer = WriteEventOrderSerializer(data=data)
if serializer.is_valid():
serializer.save()
event_orders = EventOrder.objects.all()
serializer = WriteEventOrderSerializer(event_orders, many=True)
file_temp.close()
return Response(serializer.data)
return Response({"Success!": "CSV imported OK"})
SERIALIZER
class WriteEventOrderSerializer(serializers.ModelSerializer):
class Meta:
model = EventOrder
fields = ('event', 'event_order',)
SAMPLE CSV THAT DOES NOT WORK
Event,Event_Order
W 2x Championship,1
W 2x Senior,2
W Lwt 2x ,3
W J18 2x ,4
W 2x Intermediate,5
W 2x Club,6
W J16 2x ,7
W MasB 2x ,8
W MasC 2x ,9
W MasD 2x Championship,10
W MasD 2x Club,11
W MasE 2x ,12
W MasE 2x Championship,13
W MasE 2x Club,14
W MasF 2x ,15
W MasG 2x ,16
W 2- Championship,17
W 2- Senior,18
W J18 2- ,19
W 2- Club,20
W MasB 2- ,21
W MasC/D 2- ,22
W MasD 2- ,23
W MasE/F 2- ,24
Mx 2x ,25
Mx MasB/C 2x ,26
Mx MasD 2x ,27
Mx MasE 2x ,28
Mx MasF/G/H 2x ,29
Op 2x Championship,30
Op 2x Senior,31
Op Lwt 2x ,32
Op J18 2x ,33
Op 2x Club,34
Op J16 2x ,35
Op MasB 2x ,36
Op MasC 2x Championship,37
Op MasC 2x Club,38
Op MasD 2x ,39
Op MasE 2x Championship,40
Op MasE 2x Club,41
Op MasF 2x ,42
Op MasG 2x ,43
Op MasH/I 2x ,44
Op 2- Championship,45
Op 2- Senior,46
Op J18 2- ,47
Op 2- Club,48
Op MasB/C 2- ,49
Op MasD 2- ,50
Op MasE 2- ,51
Op MasF 2- ,52
Op MasG/H 2- ,53
ERROR TRACEBACK
/var/folders/3l/z2qry1p532n1kjhtwb5g6_fm0000gn/T/tmpo72y4kfn
Internal Server Error: /api/event-order-import/
Traceback (most recent call last):
File "/Users/sianalcock/.local/share/virtualenvs/pairsheadoftheriver-vxdBwZ1a/lib/python3.10/site-packages/django/core/handlers/exception.py", line 47, in inner
response = get_response(request)
File "/Users/sianalcock/.local/share/virtualenvs/pairsheadoftheriver-vxdBwZ1a/lib/python3.10/site-packages/django/core/handlers/base.py", line 181, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "/Users/sianalcock/.local/share/virtualenvs/pairsheadoftheriver-vxdBwZ1a/lib/python3.10/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
return view_func(*args, **kwargs)
File "/Users/sianalcock/.local/share/virtualenvs/pairsheadoftheriver-vxdBwZ1a/lib/python3.10/site-packages/django/views/generic/base.py", line 70, in view
return self.dispatch(request, *args, **kwargs)
File "/Users/sianalcock/.local/share/virtualenvs/pairsheadoftheriver-vxdBwZ1a/lib/python3.10/site-packages/rest_framework/views.py", line 509, in dispatch
response = self.handle_exception(exc)
File "/Users/sianalcock/.local/share/virtualenvs/pairsheadoftheriver-vxdBwZ1a/lib/python3.10/site-packages/rest_framework/views.py", line 469, in handle_exception
self.raise_uncaught_exception(exc)
File "/Users/sianalcock/.local/share/virtualenvs/pairsheadoftheriver-vxdBwZ1a/lib/python3.10/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
raise exc
File "/Users/sianalcock/.local/share/virtualenvs/pairsheadoftheriver-vxdBwZ1a/lib/python3.10/site-packages/rest_framework/views.py", line 506, in dispatch
response = handler(request, *args, **kwargs)
File "/Users/sianalcock/Development/pairsheadoftheriver/results/views/event_order.py", line 70, in post
next(reader) # skips the first row
StopIteration
[29/Mar/2024 15:39:12] "POST /api/event-order-import/ HTTP/1.1" 500 103983
FIXED BY DECODING TO UTF-8 UPDATED VIEW
class EventOrderImport(APIView):
# This function ATTEMPTS to import the csv from frontend
# Start by deleting all existing event cats
parser_classes = (FormParser, MultiPartParser)
def post(self, request):
EventOrder.objects.all().delete()
def decode_utf8(input_iterator):
for l in input_iterator:
yield l.decode('utf-8')
reader = csv.reader(decode_utf8(request.FILES['file']))
next(reader) # skips the first row
for row in reader:
print(row)
if row:
data = {
'event': row[0],
'event_order': row[1]
}
serializer = WriteEventOrderSerializer(data=data)
if serializer.is_valid():
serializer.save()
event_orders = EventOrder.objects.all()
serializer = WriteEventOrderSerializer(event_orders, many=True)
return Response(serializer.data)