I have data in a txt file like this:-

A1085|CR01B|      |13261F001000|0000000728|      |0000000000000|      |99000|01| 

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);
    }
}

CodePudding user response：

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

CodePudding user response：

 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)