从React前端导入CSV文件到Django/Python后端不稳定

Question

我正在尝试从一个通过ReactJs前端上传的csv文件中获取数据，然后把这些数据导入到Django/Python后端的sqlite数据库中。

我想用同样的代码来处理几个不同的导入情况，目前至少有两个情况是可以正常工作的，但当我尝试复制这段代码时，它却不管用了。

看起来从Excel或其他系统生成的csv文件是可以正常工作的，但我用的是Mac，如果我尝试用VS Code编辑这个csv文件，或者用Numbers（但导出为csv格式），那么这个文件就不能用了。不过，当我在VS Code中查看这个文件时，我发现正常工作的文件和不正常的文件之间没有任何区别，所有文件都是utf编码的。

这是csv文件的问题吗？有没有什么代码可以帮我诊断一下这个问题……或者也许修复它？

前端：

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

后端

模型

from django.db import models

class EventOrder(models.Model):
    event=models.CharField(max_length=40)
    event_order=models.IntegerField()

视图

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"})

序列化器

class WriteEventOrderSerializer(serializers.ModelSerializer):

    class Meta:
        model = EventOrder
        fields = ('event', 'event_order',)

一个不工作的示例csv

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

错误追踪信息

/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

通过解码为UTF-8修复 更新后的视图

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)