Inserting CSV into Postgresql with Nodejs

Asked

Viewed 55 times

2

I want to insert a CSV into a postgresql table. CSV contains rows with multiple column numbers.

Ex:

     1,2,3
     1,2,3,4,5
     1,2,3,4,5,6
     1,2,3,4

When performing the insertion, the following error occurs:

bind message supplies 7 parameters, but prepared statement "" requires 11.

In this case my CSV has up to 11 columns, but the insertion of the first row only has 7 columns. Can you help me?

My code:

const query = "INSERT INTO category(activitiesheart,activitiessteps,opa,oxe,umt,erf,mar,zap,asd,ser,ver)
 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)";

pool.connect((err, client, done) => {
  if (err) throw err;

  try {
    csvData.forEach((row) => {
      client.query(query, row, (err, res) => {
        if (err) {
          console.log(err.stack);
        } else {
          console.log("inserted " + res.rowCount + " row:", row);
        }
      });
    });
  } finally {
    done();
  }
});
node.js postgresql csv sql-insert
  • 1

    Instead of missing values what do you want to do? fill with null?

  • this, is an option too :)

  • if possible, the number of VALUE values is dynamic...

  • Another thing is how do you know for sure that the element in position "6" belongs to position 6? How do you guarantee that it is not the element in position 11 for example?

  • The code goes through each line of the CSV. In the first line for example, it wants to insert 7 elements, but the Insert is waiting 11 elements. At the moment the CSV has 11 elements, the insertion occurs perfectly. So there’s the problem, the maximum of elements are 11, but there are lines with fewer elements.

  • If it contains 7 elements, the rest inserted can be null, 0, anything.

  • i understood the problem. I can see by sql the position 6 of the array is erf as I know that the value of heading 6 corresponds to the element erf and not to the element ver? There is some order assurance in the array elements?

  • The order is 6 = Erf, as you said. There is no guarantee, because if the value does not exist, enter null.

Show 3 more comments

1 answer

1


One way to solve is to make a loop to fill the missing values, if it is possible to insert null values into your database then it is possible to make a function to "complete" the values of the array with nulls. After normalizing the size of the array it is possible to insert without problems.

Follow an example:

//...
csvData.forEach((row) => {
    while (row.length < 11) {
        row.push(null)  //ou row.push('')
    }
    client.query(query, row, (err, res) => {
// resto do código ...

It would be the same idea if the values to be filled are integer values.

OBS.: This way the code was implemented we are assuming that the data read is positional and that missing values can be completed with null in the order in which they appear and the missing ones are always the last elements (there is no possibility that missing values are intermediate values).

  • I appreciate the help, I believe I’ve managed to resolve. Thank you!!

Browser other questions tagged node.js postgresql csv sql-insert

You are not signed in. Login or sign up in order to post.