React前端上传CSV至Django后端导入异常问题排查与解决

问题背景 #

将React前端上传的CSV文件导入Django后端SQLite数据库时，出现场景兼容性问题：Excel或其他系统生成的CSV可正常导入，但Mac下用VS Code或Numbers编辑导出的CSV无法导入。肉眼检查文件内容无差异且均为UTF编码，报错为StopIteration，最终通过添加UTF-8解码逻辑修复问题。

前端代码 #

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

修复后的视图 #

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)

内容的提问来源于stack exchange，提问作者sunshine2171