Error When Importing CSV File To MYSQL: Errno 1136 - ER_WRONG_VALUE_COUNT_ON_ROW

02 September 2019 - 1 answer

I am new to Node and Ajax so please bear with me. I am attempting to import a csv file to a MySQL table. I have managed to cobble together a few tutorials and I think I may have messed up somewhere along the line. Your assistance would be greatly appreciated.

I have managed to read the csv file and console.log an output of the contents BUT as soon as I try and insert the records into my table I get the following errors:

Error: ER_WRONG_VALUE_COUNT_ON_ROW: Column count doesn't match value count at row 1
TypeError: req.next is not a function
[ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client

I saw on another post during my search for a solution that for bulk inserts the rows need to be delimited with parenthesis but I cannot figure out how to do that. I have managed to do everything else on this project and it is all working so I know all my settings are correct until I get to this point.

This is my handlebars template:

<head>
  {{>headLinks}}
</head>

<body>
  <div class="main-content">
    {{>profile_header}}

    <div>{{{message}}}</div>
    <input type="file" name="myfile" id="myfile">
  </div>
  {{>footer}}
  <script>
    $('#myfile').change(function(e) {
      var file = e.target.files[0];
      var formData = new FormData();
      formData.append('myfile', file);

      $.ajax({
        url: './csv',
        type: 'post',
        data: formData,
        processData: false,
        contentType: false,
        success: function() {
          window.location = './dashboard'
        },
        crossDomain: true
      });
    });
  </script>
</body>

</html>

This is a part of my app.js file:

const fs = require('fs');
const mysql = require('mysql');
const multer = require('multer');
const csv = require('csv-parse');
const upload = multer({
  dest: 'uploads/'
});
const express = require('express');
const app = express();
const csv_import = require('./controllers/import');

app.post('/:eid/csv', upload.single('myfile'), (req, res) => {
  csv_import.handleImport(req, res, csv, conn, name, fs)
});

This is my import.js file

const handleImport = (req, res, csv, conn, name, fs) => {
  const file = req.file;
  const eventId = req.params.eid ;
  const queryColumns = "SELECT * FROM events WHERE eid = ?"
  conn.query(queryColumns, [eventId], (err, cols, fields) => {
    console.log(cols[0].event_name + " - " + eventId);
    if (err) {
      console.log(err)
      req.flash('alert', '<div class="alert alert-danger" role="alert">Unable to access this event, please try again.</div>');
      res.redirect('/profile');
    } else {
      const table = cols[0].table_name;
      const columns = cols[0].columns;
      const col_array = JSON.parse(columns.split("'").join('"'));
      const custom1 = col_array[8];
      const custom2 = col_array[9];
      const custom3 = col_array[10];
      fs.createReadStream(file.path).pipe(csv()).on('data', (data) => {
        console.log(data.length);
        const values = [];
        values.push(JSON.stringify(data));
        const addQuery = "INSERT INTO " + table + " (TotalGuests , AlertMessage , FirstName , LastName , Company , Email , Phone , " + custom1 + ", " + custom2 + ", " + custom3 + ") VALUES( ?)"
        conn.query(addQuery, [values], (err, results, fields) => {
          if (err || results.length === 0) {
            console.log(err);
            res.render('event_add', {
              title: 'Add Event',
              alert: '<div class="alert alert-danger" role="alert">Unable to create event. Please try again later or <a target="_blank" rel="nofollow noreferrer" href="/contact">contact us</a>for assistance</div>',
              name
            })
            return
          }
          req.flash('alert', '<div class="alert alert-success" role="alert">Import completed</div>')
        })
      });

    }


  });
}

module.exports = {
  handleImport
};

Outputs:

When I console.log just the data from the csv file (1 row):

[ 'Required', 'Optional', 'Required', 'Required', 'Optional', 'Optional', 'Optional', 'Optional', 'Optional', 'Optional' ]

The final output when inserting for the above row:

'INSERT INTO Testtb9325 (TotalGuests , AlertMessage , FirstName , LastName , Company , Email , Phone , Custom1, Custom2, Custom3) VALUES( \'[\\"Required\\",\\"Optional\\",\\"Required\\",\\"Required\\",\\"Optional\\",\\"Optional\\",\\"Optional\\",\\"Optional\\",\\"Optional\\",\\"Optional\\"]\')' }

Answer

I am not sure how or why but the below code works. In case anyone else has the same problem.

    const file = req.file.path
        let stream = fs.createReadStream(file);
        let myData = [];
        let csvStream = csv
            .parse()

        .on("data", (data) => {
            myData.push(data);
        })

        .on("end", () => {
            myData.shift();

            let query = "INSERT INTO " + table + " (TotalGuests,AlertMessage,FirstName,LastName,Company,Email,Phone,Custom1,Custom2,Custom3) VALUES ?";
            conn.query(query, [myData], (error, response) => {

                console.log(error || response);
                const mess = response.message;
                const records = mess.replace("&", "");
                    req.flash('alert', '<div class="alert alert-success" role="alert">Loaded - ' + records + '</div>');
                    res.redirect('./checklist');
                }

            })
        });

        stream.pipe(csvStream);

source: stackoverflow.com

← Previous Post

Error When Importing CSV File To MYSQL: Errno 1136 - ER_WRONG_VALUE_COUNT_ON_ROW

02 September 2019 - 1 answer

Answer

Related Questions