使用Node.js和XLSX将Excel数据上传到MySQL数据库时排除日期时间格式错误

dxpyg8gm  于 2023-06-04  发布在  Mysql
关注(0)|答案(1)|浏览(182)

我有后端代码，处理数据插入到MySQL数据库从Excel文件，将上传到客户端。代码如下：

const router = require("express").Router();
const fs = require("fs");
const db = require("../config/db");
const multer = require("multer");
const xlsx = require("xlsx");

const upload = multer({ dest: "uploads/" });

// Define route to handle file upload
router.post("/appointments/upload", upload.single("file"), (req, res) => {
  const filePath = req.file.path;
  const workbook = xlsx.readFile(filePath);
  const sheets = workbook.SheetNames;

  let sheetsProcessed = 0; // Variable to keep track of the number of processed sheets

  // Execute the database query to insert data
  sheets.forEach((sheet) => {
    const sheetData = xlsx.utils.sheet_to_json(workbook.Sheets[sheet]);
    const columns = Object.keys(sheetData[0]);
    const values = sheetData.map((row) => columns.map((column) => row[column]));
    const query = `INSERT INTO ${sheet} (${columns.join(", ")}) VALUES ?`;

    db.query(query, [values], (err) => {
      if (err) {
        console.error("Error inserting rows:", err);
        res.status(500).send("Error inserting rows.");
      } else {
        sheetsProcessed++; // Increment the counter for processed sheets

        if (sheetsProcessed === sheets.length) {
          // All sheets have been processed, remove the temporary file and send the response
          fs.unlinkSync(filePath);
          res.send("Data saved successfully!");
        }
      }
    });
  });
});

module.exports = router;

下面是我在Excel中插入的一个示例和数据：

当我尝试上传文件时，它似乎告诉文件内的日期值格式不正确。详情如下：

Error inserting rows: Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '36809' for column 'tgl_kunjungan' at row 1
    at Query.Sequence._packetToError (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    --------------------
    at Protocol._enqueue (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/Connection.js:198:25)
    at /Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/routes/uploadAppointments.js:24:8
    at Array.forEach (<anonymous>)
    at /Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/routes/uploadAppointments.js:18:10
    at Layer.handle [as handle_request] (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/express/lib/router/layer.js:95:5)
    at next (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/express/lib/router/route.js:144:13)
    at done (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/multer/lib/make-middleware.js:45:7)
    at indicateDone (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/multer/lib/make-middleware.js:49:68)
    at /Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/multer/lib/make-middleware.js:155:11
    at WriteStream.<anonymous> (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/multer/storage/disk.js:43:9)
    at WriteStream.emit (node:events:525:35)
    at finish (node:internal/streams/writable:756:10)
    at finishMaybe (node:internal/streams/writable:741:9)
    at afterWrite (node:internal/streams/writable:506:3)
    at onwrite (node:internal/streams/writable:479:7) {
  code: 'ER_TRUNCATED_WRONG_VALUE',
  errno: 1292,
  sqlMessage: "Incorrect datetime value: '36809' for column 'tgl_kunjungan' at row 1",
  sqlState: '22007',
  index: 0,
  sql: "INSERT INTO kunjungan (id_kunjungan, kode_kunjungan, id_pasien, tgl_kunjungan, gigi, diagnosa, terapi, keterangan, daftar) VALUES (1000, 'NOMORKUNJUNGAN', 901, 36809, 'OKELAH', 'benjol', 'tolak angin', 'ga efektif', '')"
}
Error inserting rows: Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (`dental_art`.`pembayaran`, CONSTRAINT `fk_id_kunjungan_pembayaran` FOREIGN KEY (`id_kunjungan`) REFERENCES `kunjungan` (`id_kunjungan`) ON DELETE CASCADE ON UPDATE CASCADE)
    at Query.Sequence._packetToError (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    --------------------
    at Protocol._enqueue (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/Connection.js:198:25)
    at /Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/routes/uploadAppointments.js:24:8
    at Array.forEach (<anonymous>)
    at /Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/routes/uploadAppointments.js:18:10
    at Layer.handle [as handle_request] (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/express/lib/router/layer.js:95:5)
    at next (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/express/lib/router/route.js:144:13)
    at done (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/multer/lib/make-middleware.js:45:7)
    at indicateDone (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/multer/lib/make-middleware.js:49:68)
    at /Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/multer/lib/make-middleware.js:155:11
    at WriteStream.<anonymous> (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/multer/storage/disk.js:43:9)
    at WriteStream.emit (node:events:525:35)
    at finish (node:internal/streams/writable:756:10)
    at finishMaybe (node:internal/streams/writable:741:9)
    at afterWrite (node:internal/streams/writable:506:3)
    at onwrite (node:internal/streams/writable:479:7) {
  code: 'ER_NO_REFERENCED_ROW_2',
  errno: 1452,
  sqlMessage: 'Cannot add or update a child row: a foreign key constraint fails (`dental_art`.`pembayaran`, CONSTRAINT `fk_id_kunjungan_pembayaran` FOREIGN KEY (`id_kunjungan`) REFERENCES `kunjungan` (`id_kunjungan`) ON DELETE CASCADE ON UPDATE CASCADE)',
  sqlState: '23000',
  index: 0,
  sql: 'INSERT INTO pembayaran (id_pembayaran, id_kunjungan, jumlah_bayar, jumlah_tagihan) VALUES (26, 1000, 50000, 100000)'
}
/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^

Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client
    at new NodeError (node:internal/errors:387:5)
    at ServerResponse.setHeader (node:_http_outgoing:644:11)
    at ServerResponse.header (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/express/lib/response.js:794:10)
    at ServerResponse.send (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/express/lib/response.js:174:12)
    at Query.<anonymous> (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/routes/uploadAppointments.js:27:25)
    at Query.<anonymous> (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/Connection.js:526:10)
    at Query._callback (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/Connection.js:488:16)
    at Query.Sequence.end (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
    at Query.ErrorPacket (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/sequences/Query.js:92:8)
    at Protocol._parsePacket (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/Users/gianwnp/Documents/Self-Learning/Web Development/alt-dental-art-ptk/server/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:513:28) {
  code: 'ERR_HTTP_HEADERS_SENT'
}
[nodemon] app crashed - waiting for file changes before starting...

我尝试将Excel文件中的格式更改为所需的格式：年-月-日分：时：秒
但它仍然产生相同的错误。
谁能告诉我我做错了什么，或者我没有尝试过什么方法来修复它？

mysql

1条答案

按热度按时间
gojuced7

gojuced71#

您可能需要更改解析文件的方式，而不是文件。
尝试添加{cellDates: true}解析选项：

const sheetData = xlsx.utils.sheet_to_json(workbook.Sheets[sheet], {cellDates: true});

参见文档：
默认情况下，Excel将日期存储为数字，并使用指定日期处理的格式代码。例如，日期19-Feb-17存储为数字42785，数字格式为d-mmm-yy
所有解析器的默认行为都是生成数字单元格。将cellDates设置为true将强制生成器存储日期。
https://www.npmjs.com/package/xlsx#dates

赞(0）分享  回复(0）举报  2023-06-04
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com