const fs = require('fs'); const { promisify } = require('util'); const parseString = promisify(require('xml2js').parseString); const { Pool } = require('pg'); const XLSX = require('xlsx'); const { performance } = require('perf_hooks'); function evaluateCondition(conditions, value) { for (const condition of conditions) { if (condition._ && condition._ === value) { return condition.__text; } } return value; } function evaluateFormula(formula, value) { if (typeof formula === 'string') { if (formula.startsWith('{#if')) { const conditions = formula.Condition; return evaluateCondition(conditions, value); } else { return eval(formula.replace(/value/g, `'${value}'`)); } } else { return value; } } async function insertDataFromExcelToPostgreSQL() { try { console.log('Inserting data into PostgreSQL...'); const excelFilePath = process.argv[2]; const xmlFilePath = process.argv[3]; if (!excelFilePath || !xmlFilePath) { console.error('Please provide both Excel file path and XML file path as command-line arguments.'); return; } // Step 1: Parse the XML file const xmlString = fs.readFileSync(xmlFilePath, 'utf8'); const xmlData = await parseString(xmlString); const rootElement = xmlData.Mapping.RootElement[0]; const mappingFields = xmlData.Mapping.MappingFields[0].Field; // Step 2: Read the Excel file const workbook = XLSX.readFile(excelFilePath); const worksheet = workbook.Sheets[workbook.SheetNames[0]]; const excelData = XLSX.utils.sheet_to_json(worksheet); // Step 3: Connect to the PostgreSQL database const pool = new Pool({ user: 'root', host: 'localhost', database: 'myform', password: 'password', port: '5432', }); const client = await pool.connect(); // Step 4: Insert the data into the table const columnNames = []; const valuePlaceholders = []; const values = []; let skippedRecords = 0; let importedRecords = excelData.length; for (const field of mappingFields) { const excelColumnName = field.ExcelColumnName[0]; const tableFieldName = field.TableFieldName[0]; const formula = field.Formula[0]; const validation = field.Validation[0]; columnNames.push(tableFieldName); valuePlaceholders.push(`$${columnNames.length}`); // Extract the corresponding Excel column values for each table field let fieldValues = excelData.map(row => row[excelColumnName]); if (formula) { fieldValues = fieldValues.map(value => evaluateFormula(formula, value)); } const processedValues = fieldValues.map(value => `${value}`); values.push(processedValues); // Check if the field values match the validation rule if (validation) { for (let i = 0; i < fieldValues.length; i++) { const value = fieldValues[i]; const validationResult = eval(validation.replace(/value/g, `'${value}'`)); if (!validationResult) { skippedRecords++; console.log(`Skipped record at index ${i}: ${value}`); fieldValues[i] = null; importedRecords--; // Decrement importedRecords when a record is skipped } } } } const insertQuery = `INSERT INTO ${rootElement} (${columnNames.join(',')}) VALUES (${valuePlaceholders.join(',')})`; // Calculate the maximum number of rows for any field const maxRows = values.reduce((max, fieldValues) => Math.max(max, fieldValues.length), 0); const startTime = performance.now(); for (let i = 0; i < maxRows; i++) { const rowValues = values.map(fieldValues => fieldValues[i]); // Skip the record if any value is null if (rowValues.some(value => value === null)) { skippedRecords++; importedRecords--; // Decrement importedRecords when a record is skipped continue; } // Insert the row into the database try { await client.query(insertQuery, rowValues); const progress = ((i + 1) / maxRows) * 100; console.log(`Progress: ${progress.toFixed(2)}%`); } catch (error) { console.log(`Error inserting record at index ${i}: ${rowValues}`); console.error(error); importedRecords--; // Decrement importedRecords when a record fails to be inserted } } const endTime = performance.now(); const executionTime = (endTime - startTime) / 1000; // Close the database connection client.release(); pool.end(); console.log(`Data processed successfully.`); console.log(`Total records: ${excelData.length}`); console.log(`Skipped records: ${skippedRecords}`); console.log(`Imported records: ${importedRecords}`); console.log(`Execution time: ${executionTime.toFixed(2)} seconds`); } catch (error) { console.error('Error:', error); } } // Check if command-line arguments are provided correctly if (process.argv.length < 4) { console.error('Please provide both Excel file path and XML file path as command-line arguments.'); } else { // Call the function to start the process insertDataFromExcelToPostgreSQL(); }