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

Question

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('\x00\r\n', ''));
        } 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);
    }
}

Please avoid using words like *lakh* that are not understood globally -- and might cause people to be very confused about what are *lakh rows* — James Z, Jan 05 '23 at 15:37

Juljo Shahini · Answer 1 · 2023-01-05T10:16:29.080

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)

edited Jan 05 '23 at 10:16

answered Jan 05 '23 at 09:57

Juljo Shahini

138
10

Hi yes but actually I am after reading the data from text file I am spliting the row and saving the separately to an array and that's why I am looping it to pass the values one by one – piyushCodes6 Jan 05 '23 at 10:01
@piyushCodes6 I edited my answer, take a look at it and tell me if that can somehow help you – Juljo Shahini Jan 05 '23 at 10:18
is there any way we can connect bro. I am totally new to SQL. 5-10 minutes of your would save me many more upcoming nights. – piyushCodes6 Jan 05 '23 at 10:27
I have sent the request from my side bro. – piyushCodes6 Jan 05 '23 at 10:51
Thank you so so much bro. It is working absolutely fine now. It took only 50 seconds to push the data into RDS PostgreSQL DB – piyushCodes6 Jan 05 '23 at 14:23

score 0 · Answer 2 · answered Jan 05 '23 at 10:06

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

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

2 Answers2