Last active
August 23, 2019 07:21
-
-
Save KPChakravarthy/d3e0aec197f465ab2908feadbfe9c607 to your computer and use it in GitHub Desktop.
NodeJS Postgresql Bulk upsert.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// import XlsxStreamReader - it is an npm package ==== https://www.npmjs.com/package/xlsx-stream-reader | |
function readXlsx(file, headers) { | |
return new Promise((resolve, reject) => { | |
let resp_row = []; | |
var workBookReader = new XlsxStreamReader(); | |
workBookReader.on('error', function (error) { | |
throw error; | |
}); | |
workBookReader.on('sharedStrings', function () { | |
// console.log(workBookReader.workBookSharedStrings); | |
}); | |
workBookReader.on('styles', function () { | |
// console.log(workBookReader.workBookStyles); | |
}); | |
workBookReader.on('worksheet', function (workSheetReader) { | |
if (workSheetReader.id > 1) { | |
workSheetReader.skip(); | |
return; | |
} | |
let headerKeys = []; | |
workSheetReader.on('row', row => { | |
// console.log(headers); | |
if (row.attributes.r == 1) { | |
row.values.shift(); | |
headerKeys = row.values; | |
} else { | |
let item = {}; | |
// File parse operations here - works on each row basis | |
item['_id'] = `'${uuid()}'`; | |
for (let i = 0; i < headers.length; i++) { | |
if (typeof (row.values[i]) === 'string') { | |
item[headers[i][headerKeys[i]]] = `'${row.values[i]}'`; | |
} else { | |
item[headers[i][headerKeys[i]]] = row.values[i] ? row.values[i] : 'NULL'; | |
} | |
} | |
item['createdAt'] = `'${new Date().toUTCString()}'`; | |
item['updatedAt'] = `'${new Date().toUTCString()}'`; | |
resp_row.push(`(${Object.values(item).join(', ')})`); | |
} | |
}); | |
workSheetReader.on('end', function () { | |
// console.log(workSheetReader.rowCount); | |
}); | |
workSheetReader.process(); | |
}); | |
workBookReader.on('end', function () { | |
resolve(resp_row); | |
}); | |
fs.createReadStream(file).pipe(workBookReader); | |
}); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
exports.uploadPpeData = async (req, res, next) => { | |
let fileURL1 = process.cwd() + '/fileName.xlsx'; | |
let keys1 = '("_id", "key_1", "key_2", "key_3", "createdAt", "updatedAt")'; | |
let ppeArr = await excelFileRead(fileURL1, headerKeysPPE); | |
let queryStr = `CREATE OR REPLACE FUNCTION public.ppeUp(OUT response public."CommonAttributes", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_83947583745$ | |
BEGIN INSERT INTO public."CommonAttributes" as CommonAttribute ("_id", "key_1", "key_2", "key_3", "createdAt", "updatedAt") VALUES ${ppeArr.join(', ')} | |
ON CONFLICT ("key_1") DO UPDATE SET | |
"key_2"=excluded.key_2, | |
"key_3"=excluded.key_3, | |
"key_4"=excluded.key_4, | |
"key_5"=excluded.key_5, | |
"key_6"=excluded.key_6; | |
END $func_83947583745$ LANGUAGE plpgsql; | |
select public.ppeUp();` | |
db.sequelize.query(queryStr).then(resp => { | |
debugger | |
res.status(200).json({ msg: 'Successfully added', data: resp }); | |
}).catch(err => res.status(400).json({ msg: 'failed at ppe', err })); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment