Context:
I have large CSV file (around 100K), which I am parsing chunkwise using papaparse. Upon encountering a chunk, I am inserting chunk into sqlite database table.
I am using react and redux-thunk, function uploadData is action dispatcher.
Problem: Not all the rows are inserted in sqlite table. Everytime I run method, different number of rows inserted.
What I have tried:
I have tried adding BEGIN TRANSACTION and COMMIT TRANSACTION before inserting chunk. But didn't work.
Below is the function snippet.
export function uploadData(file) {
return (dispatch) => {
var db = new sqlite3.Database(decodeURI(Constants.DB_PATH));
dispatch(bfUploadStatus({ loading: true, status: false }));
return db.serialize(function() {
db.run(`PRAGMA KEY = 'secret'`);
db.run(`PRAGMA CIPHER = 'aes-128-cbc'`);
db.run(`DROP TABLE IF EXISTS bf`);
db.run(`CREATE TABLE bf (
A TEXT, B TEXT, C TEXT, D TEXT, E TEXT, F TEXT, G TEXT, H TEXT, I TEXT, J TEXT, K TEXT, L TEXT, M TEXT, N TEXT, O TEXT, P TEXT, Q TEXT, R TEXT, S TEXT, T TEXT, U TEXT, V TEXT, W TEXT, X TEXT, Y TEXT, Z TEXT,
AA TEXT, AB TEXT, AC TEXT, AD TEXT, AE TEXT, AF TEXT, AG TEXT, AH TEXT, AI TEXT, AJ TEXT, AK TEXT, AL TEXT, AM TEXT, AN TEXT, AO TEXT, AP TEXT, AQ TEXT, AR TEXT, ASA TEXT, AT TEXT, AU TEXT, AV TEXT, AW TEXT, AX TEXT, AY TEXT, AZ TEXT,
BA TEXT, BB TEXT, BC TEXT, BD TEXT, BE TEXT, BF TEXT, BG TEXT, BH TEXT, BI TEXT, BJ TEXT, BK TEXT, BL TEXT, BM TEXT, BN TEXT, BO TEXT, BP TEXT, BQ TEXT, BR TEXT, BS TEXT, BT TEXT, BU TEXT, BV TEXT, BW TEXT, BX TEXT, BY TEXT, BZ TEXT,
CA TEXT, CB TEXT, CC TEXT, CD TEXT, CE TEXT, CF TEXT, CG TEXT, CH TEXT, CI TEXT, CJ TEXT, CK TEXT, CL TEXT, CM TEXT, CN TEXT, CO TEXT, CP TEXT, CQ TEXT, CR TEXT, CS TEXT, CT TEXT, CU TEXT, CV TEXT, CW TEXT, CX TEXT, CY TEXT, CZ TEXT,
DA TEXT, DB TEXT, DC TEXT, DD TEXT, DE TEXT, DF TEXT, DG TEXT, DH TEXT, DI TEXT, DJ TEXT)`);
const stmt = db.prepare('INSERT INTO bf VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
Papa.parse(file, {
chunk: (results) => {
console.log(results);
db.run(`BEGIN TRANSACTION`);
forEach(results.data, (result) => {
stmt.run(result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7], result[8], result[9], result[10], result[11], result[12], result[13], result[14], result[15], result[16], result[17], result[18], result[19], result[20], result[21], result[22], result[23], result[24], result[25], result[26], result[27], result[28], result[29], result[30], result[31], result[32], result[33], result[34], result[35], result[36], result[37], result[38], result[39], result[40], result[41], result[42], result[43], result[44], result[45], result[46], result[47], result[48], result[49], result[50], result[51], result[52], result[53], result[54], result[55], result[56], result[57], result[58], result[59], result[60], result[61], result[62], result[63], result[64], result[65], result[66], result[67], result[68], result[69], result[70], result[71], result[72], result[73], result[74], result[75], result[76], result[77], result[78], result[79], result[80], result[81], result[82], result[83], result[84], result[85], result[86], result[87], result[88], result[89], result[90], result[91], result[92], result[93], result[94], result[95], result[96], result[97], result[98], result[99], result[100], result[101], result[102], result[103], result[104], result[105], result[106], result[107], result[108], result[109], result[110], result[111], result[112], result[113]);
});
db.run(`COMMIT TRANSACTION`);
},
error: (err) => {
dispatch(bfUploadStatus({ loading: false, status: false }));
},
complete: (results) => {
stmt.finalize();
db.close();
return dispatch(bfUploadStatus({ loading: false, status: true }));
}
})
});
}
}
