How can I dynamically choose the columns to import in mysql?
this is the raw excel file.
I only want to get the firstname
, lastname
, and department
The data are being imported in mysql successfully, but I get this error
router.post("/", upload.single("excel"), async (req, res) => {
try {
if (!req.file) {
return res.status(400).json({ error: "No File Uploaded" });
}
const workbook = XLSX.read(req.file.buffer, { type: "buffer" });
const sheetName = workbook.SheetNames[0];
const data = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
const columnsArray = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], {
header: 1,
})[0];
const validation = ["firstname", "lastname", "department"];
const successData = [];
const failedData = [];
for (let i = 0; i < data.length; i++) {
const { firstname, lastname, department } = data[i];
const sql =
"INSERT INTO users (firstname,lastname,department) VALUES(?, ?, ?)";
try {
const [rows, fields] = await connection.execute(sql, [firstname, lastname, department]);
if (rows.affectedRows) {
successData.push(data[i]);
} else {
failedData.push(data[i]);
}
} catch (error) {
console.error("Error executing SQL query:", error);
failedData.push(data[i]);
}
}
return res.json({ msg: "Ok", data: { successData, failedData } });
} catch (error) {
console.error("Error processing file:", error);
return res.status(500).json({ error: "Internal Server Error" });
}
});
Error executing SQL query: TypeError: Bind parameters must not contain undefined. To pass SQL NULL specify JS null
I think some of data are undefined in your excel file. While inserting it sets undefined in query that's why it gives an error. You should set it as NULL if value is undefined.
For example...
const [rows, fields] = await connection.execute(sql, [firstname || null, lastname || null, department || null]);