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 .