Create table dynamically in MySQl from headers of csv while uploading csv file

Viewed 47
const app = express();
const multer = require("multer");
const db = require("./dbConnection");
var csv = require("csvtojson");

//create csvdata table

app.get("/csvtable", (req, res) => {
  let sql = "CREATE TABLE csvdata(Name char(50),Age int, Address varchar(255))";
  db.query(sql, (err, result) => {
    if (err) throw err;
    console.log(result);
    res.send("csvData Table created");
  });
});

//File Upload Middleware

const fileStorage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, "./public/uploads");
  },
  //filename is use to determine what the file should be named inside folder
  filename: (req, file, cb) => {
    cb(null, Date.now() + "--" + file.originalname);
  },
});

const upload = multer({ storage: fileStorage }).single("docs");

app.post("/upload", upload, (req, res) => {
  //console.log(req.file);

  //convert csvfile to jsonArray
  csv()
    .fromFile(req.file.path)
    .then((source) => {
      // Fetching the data from each row and inserting to the table "csvdata"

      for (var i = 0; i < source.length; i++) {
        var Name = source[i]["Name"],
          Age = source[i]["Age"],
          Address = source[i]["Address"];

        var sql = `INSERT INTO csvdata values (?,?,?)`;

        var items = [Name, Age, Address];

        //Inserting data of current row into database

        db.query(sql, items, (err, result, fields) => {
          if (err) {
            console.log("Unable to insert item at row", i + 1);
            return console.log(err);
          }
        });
      }

      console.log("All items stored in db successfully");
    });
  res.send("CSV File Data saved in DB");
});

//API  for getting all data from user table
app.get("/getcsvdata", (req, res) => {
  let sql = "SELECT * FROM csvdata";
  db.query(sql, (err, result) => {
    if (err) throw err;
    res.send(result);
  });
});

module.exports = app;

Rightnow if you can see from above code first i created a table Name,age,Address initially because the csv file that i will upload, have name,age,address headers present. And based upon those condition i write logic to insert csv data into this table inside MySQL Db.

Now my question let suppose next time i want to uplaod a csv file where name,email,firstname,lastname, Address,fathersname......... etc are the column-headers inside csv file. Then again i have to create a table with these column first and then i can insert csv data into this table.

So what i am asking is there any way that each time when i upload a new csv file with different number of headers inside , table inside MySQL should be created automatically based upon csv header-column and data from csv file should be inserted into that table rather than manually creating table every time for different csv file .

0 Answers
Related