Skip to content

Instantly share code, notes, and snippets.

@KPChakravarthy

KPChakravarthy/excelFileRead.js

Last active August 23, 2019 07:21
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save KPChakravarthy/d3e0aec197f465ab2908feadbfe9c607 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save KPChakravarthy/d3e0aec197f465ab2908feadbfe9c607 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. KPChakravarthy revised this gist Aug 23, 2019. 1 changed file with 3 additions and 4 deletions.
    7 changes: 3 additions & 4 deletions upsertData.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,7 @@
    exports.uploadPpeData = async (req, res, next) => {
    let headerKeysPPE = [{ 'Current MPN': 'current_mpn' }, { 'Current Manufacturer': 'current_manufacturer' }, { 'Current paid price': 'current_price' }, { 'Qty per BOM': 'qty_per_bom' }, { 'Alterante MPN': 'alternate_mpn' }, { 'Alterante MFR': 'alternate_manufacturer' }, { 'Alternate FFF/F': 'ppe_type' }];
    let fileURL1 = process.cwd() + '/BOM_1_ET_v1_-_PPE_v1.xlsx';
    let keys1 = '("_id", "current_mpn", "current_manufacturer","current_price", "qty_per_bom", "alternate_mpn", "alternate_manufacturer", "ppe_type", "createdAt", "updatedAt")';
    let ppeArr = await getManualUploadValues(fileURL1, headerKeysPPE);
    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
  2. KPChakravarthy created this gist Aug 23, 2019.
    53 changes: 53 additions & 0 deletions excelFileRead.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,53 @@
    // 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);
    });
    20 changes: 20 additions & 0 deletions upsertData.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    exports.uploadPpeData = async (req, res, next) => {
    let headerKeysPPE = [{ 'Current MPN': 'current_mpn' }, { 'Current Manufacturer': 'current_manufacturer' }, { 'Current paid price': 'current_price' }, { 'Qty per BOM': 'qty_per_bom' }, { 'Alterante MPN': 'alternate_mpn' }, { 'Alterante MFR': 'alternate_manufacturer' }, { 'Alternate FFF/F': 'ppe_type' }];
    let fileURL1 = process.cwd() + '/BOM_1_ET_v1_-_PPE_v1.xlsx';
    let keys1 = '("_id", "current_mpn", "current_manufacturer","current_price", "qty_per_bom", "alternate_mpn", "alternate_manufacturer", "ppe_type", "createdAt", "updatedAt")';
    let ppeArr = await getManualUploadValues(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 }));
    }