Question 90265 in PostgreSQL Question posted in
The official documentation can be found here.

How to read from text file and insert the data into PostgreSQL using Nodejs?

This text file contains more than 500k rows which I want to read and insert into PostgreSQL table using nodejs. These blanks spaces should also be read and saved like this empty space only.

I wrote the script like this and it is working also the data is getting inserted into table but it is taking very much time like 10 mins for 20 thousands rows only.

const readTextFile = async () => {
    const File = await fs.readFileSync('data.txt', 'utf-8');
    let arr = File.split("|");
    let modified = [];
    let temp = [];
    for (let i = 0; i < arr.length; i++) {
        if (i % 10 === 0) {
            modified.push([...temp]);
            temp = [];
            temp.push(arr[i].replace('x00rn', ''));
        } else {
            temp.push(arr[i]);
        }
    }
    console.log("modified", modified.length);
    for (let i = 0; i < modified.length; i++) {
        await insertValuesToDB(modified[i]);
    }
}

const insertValuesToDB = async (values) => {
    try {
        const text = `INSERT INTO requirement(container, module, mod_devdate, part_no, qty, tapdate, tap_qty, taptime, sup_cd, namc_id) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`;
        const result = await client.query(text, values);
        console.log("result", result);
    } catch (e) {
        console.log("ERROR", e);
    }
}
Tags:

2

Answers


  1. 0 
     for (let i = 0; i < modified.length; i++) {
    await insertValuesToDB(modified[i]);
}

    I think it’s not suggested to loop this as you insert data into database in this function. I suggest doing it all in one single query.
    When I encountered this kind of problem in mysql I solved it like this:

    INSERT INTO EXAMPLE (
name,
surname,
email)
VALUES
(
    '1name',
    '1surname',
    '1email'
),
(
    '2name',
    '2surname',
    '2email'
),
(
    '3name',
    '3surname',
    '3email'
);

    This is how the query was supposed to look in the end.

    let data = [{name: '1name', surname: '1surname', email: '1email'},{name: '2name', surname: '2surname', email: '2email'},{name: '3name', surname: '3surname', email: '3email'}]

let QueryInsert = data.length > 0 ? 'INSERT INTO EXAMPLE (name,surname,email) VALUES ' : '';
data.forEach((el) => {
  QueryInsert = QueryInsert + `(${el.name},${el.surname},${el.email}),`
})

QueryInsert = QueryInsert.substring(0,QueryInsert.length-1);
console.log(QueryInsert)
    Login or Signup to reply.
  2. 0

    If possible – a simple copy would be the fastest solution, I would assume. For example:

    How to import CSV file data into a PostgreSQL table

    Maybe this is not possible, because of the data sanitization that has to be done.

    Another possibility would be, to wrap the inserts in a transaction. Maybe this can be "batched", for lower memory consumption.

    Minimal example:

    await client.query('BEGIN')
for (let i = 0; i < modified.length; i++) {
  await insertValuesToDB(modified[i]);
}
await client.query('COMMIT')

    See: https://node-postgres.com/features/transactions

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search