save huge dataset from excel in MySQL, does not save all rows

330
February 15, 2017, at 10:05 PM

I have more or less 20000 rows in my excel file (exported from another Database based Tool), which i would like to save in my Database.

if(typeof require !== 'undefined') XLSX = require('xlsx');
var xlsx = "tmp8704.xlsx";
var url = "extFiles/"+xlsx;
var oReq = new XMLHttpRequest();
oReq.open("GET", url, true);
oReq.responseType = "arraybuffer";
var dataXLS;
oReq.onload = function(e) {
var arraybuffer = oReq.response;
/* convert data to binary string */
var data = new Uint8Array(arraybuffer);
var arr = new Array();
for(var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
var bstr = arr.join("");
/* Call XLSX */
var workbook = XLSX.read(bstr, {type:"binary"});
var first_sheet_name = workbook.SheetNames[0];
/* Get worksheet */
var worksheet = workbook.Sheets[first_sheet_name];
dataXLS = XLSX.utils.sheet_to_json(worksheet, {header:1});
var mysql = require("mysql");
var connection = mysql.createConnection({
  host: 'xxx.xxx.xxx.xxx',
  user: 'muser',
  password: 'massword',
  database: 'matabase'
});
connection.connect((err) => {
if(err) {
  return console.log(err.stack);
}
console.log("Connection succesfully established");
});
for(var x in dataXLS){
  if(parseInt(x)+2 != dataXLS.length) {
      var myNumber = dataXLS[parseInt(x)+2][6];
      console.log("Stelle " + x + " :" + dataXLS[parseInt(x)+2][0] + " " + dataXLS[parseInt(x)+2][1] + " " + parseFloat(myNumber));
      $queryString = "INSERT INTO `articles` (articleno,text,price) VALUES ("+parseInt(dataXLS[parseInt(x)+2][0])+",'"+dataXLS[parseInt(x)+2][1]+"',"+parseFloat(myNumber)+");";

      connection.query($queryString, (err, rows, fields) => {
        if(err) {
          return console.log("An error ocurred with the query", err);
        }
      });
  } else {
    console.log(parseInt(x)+2);
    console.log(dataXLS.length); //19610
    break;
  }
}
connection.end(() => {
  console.log("Connection successfully closed");
});
}
oReq.send();

First question: It is saving data in my table, but only time after time, and not all of it. Why?

Second question: Is there a more efficient way to do that?

Answer 1

When I had to export large Excel sheets into MySQL, I used this website. You will have to export your excel file as a csv and tweak the settings on the site somewhat, but I found that it was fast and reliable.

READ ALSO
search error: [index_not_found_exception] no such index in elasticsearch

search error: [index_not_found_exception] no such index in elasticsearch

I have index not found exception when the first time I run my code for index creation,But second time it working good

404
How to access external function variables from inner functions in JavaScript?

How to access external function variables from inner functions in JavaScript?

Am just getting the ropes of JS Fundamentals, bear with me

305
Scripts multiplies in controllers

Scripts multiplies in controllers

I'am using nodejs with sails

354