Skip to main content

Data Migration

This scenario demonstrates building a data migration system using Map/Reduce for high-volume data imports and exports.

Business Requirements​

┌─────────────────────────────────────────────────────────────────────────────┐
│                    DATA MIGRATION REQUIREMENTS                               │
└─────────────────────────────────────────────────────────────────────────────┘

     ✓ Import customers from CSV file
     ✓ Process thousands of records efficiently
     ✓ Handle errors gracefully with logging
     ✓ Support field mapping configuration
     ✓ Generate import summary report
     ✓ Allow for data transformation during import

Import Flow​

┌─────────────────────────────────────────────────────────────────────────────┐
│                    CSV IMPORT FLOW                                           │
└─────────────────────────────────────────────────────────────────────────────┘

     ┌──────────────────┐
     │  Upload CSV File │
     │  (via Suitelet)  │
     └────────┬─────────┘
              │
              ▼
     ┌──────────────────────────────────────────────────────────────────┐
     │  PARSE & VALIDATE                                                 │
     │  ────────────────────────────────────────────────────────────────│
     │  • Read CSV headers                                              │
     │  • Validate required columns                                     │
     │  • Create staging records                                        │
     └──────────────────────────────┬───────────────────────────────────┘
                                    │
                                    ▼
     ┌──────────────────────────────────────────────────────────────────┐
     │  MAP/REDUCE PROCESSING                                            │
     │  ────────────────────────────────────────────────────────────────│
     │  getInputData: Load staging records                              │
     │  map: Validate and transform each row                            │
     │  reduce: Create/update NetSuite records                          │
     │  summarize: Generate report, notify user                         │
     └──────────────────────────────┬───────────────────────────────────┘
                                    │
                                    ▼
     ┌──────────────────────────────────────────────────────────────────┐
     │  RESULTS                                                          │
     │  ────────────────────────────────────────────────────────────────│
     │  • Records created/updated                                       │
     │  • Error log                                                     │
     │  • Summary email                                                 │
     └──────────────────────────────────────────────────────────────────┘

Upload Suitelet​

src/FileCabinet/SuiteScripts/Suitelets/data_import_sl.js

/**
 * @NApiVersion 2.1
 * @NScriptType Suitelet
 */
define(['N/ui/serverWidget', 'N/file', 'N/task', 'N/runtime', 'N/log'],
    (serverWidget, file, task, runtime, log) => {

    const onRequest = (context) => {
        if (context.request.method === 'GET') {
            showUploadForm(context);
        } else {
            processUpload(context);
        }
    };

    const showUploadForm = (context) => {
        const form = serverWidget.createForm({
            title: 'Customer Data Import'
        });

        // File upload field
        form.addField({
            id: 'custpage_file',
            type: serverWidget.FieldType.FILE,
            label: 'CSV File'
        });

        // Import type
        const importType = form.addField({
            id: 'custpage_import_type',
            type: serverWidget.FieldType.SELECT,
            label: 'Import Type'
        });
        importType.addSelectOption({ value: 'customer', text: 'Customers' });
        importType.addSelectOption({ value: 'vendor', text: 'Vendors' });
        importType.addSelectOption({ value: 'item', text: 'Items' });

        // Update existing?
        form.addField({
            id: 'custpage_update',
            type: serverWidget.FieldType.CHECKBOX,
            label: 'Update Existing Records'
        });

        form.addSubmitButton({ label: 'Start Import' });

        // Instructions
        const instructions = form.addField({
            id: 'custpage_instructions',
            type: serverWidget.FieldType.INLINEHTML,
            label: ' '
        });
        instructions.defaultValue = `
            <div style="background: #f5f5f5; padding: 15px; margin: 20px 0; border-radius: 4px;">
                <h3>CSV Format Requirements</h3>
                <p>The CSV file must include a header row with these columns:</p>
                <ul>
                    <li><strong>external_id</strong> - Unique identifier (required)</li>
                    <li><strong>company_name</strong> - Company name (required)</li>
                    <li><strong>email</strong> - Email address</li>
                    <li><strong>phone</strong> - Phone number</li>
                    <li><strong>address1</strong> - Street address</li>
                    <li><strong>city</strong> - City</li>
                    <li><strong>state</strong> - State/Province</li>
                    <li><strong>zip</strong> - Postal code</li>
                    <li><strong>country</strong> - Country code (US, CA, etc.)</li>
                </ul>
            </div>
        `;

        context.response.writePage(form);
    };

    const processUpload = (context) => {
        try {
            const uploadedFile = context.request.files.custpage_file;
            const importType = context.request.parameters.custpage_import_type;
            const updateExisting = context.request.parameters.custpage_update === 'T';

            if (!uploadedFile) {
                throw new Error('No file uploaded');
            }

            // Save file to File Cabinet
            uploadedFile.folder = -15; // SuiteScripts folder
            uploadedFile.name = `import_${new Date().getTime()}.csv`;
            const fileId = uploadedFile.save();

            // Trigger Map/Reduce script
            const mrTask = task.create({
                taskType: task.TaskType.MAP_REDUCE,
                scriptId: 'customscript_data_import_mr',
                deploymentId: 'customdeploy_data_import_mr',
                params: {
                    'custscript_import_file': fileId,
                    'custscript_import_type': importType,
                    'custscript_update_existing': updateExisting,
                    'custscript_notify_user': runtime.getCurrentUser().id
                }
            });

            const taskId = mrTask.submit();

            // Show confirmation
            const form = serverWidget.createForm({
                title: 'Import Started'
            });

            form.addField({
                id: 'custpage_message',
                type: serverWidget.FieldType.INLINEHTML,
                label: ' '
            }).defaultValue = `
                <div style="background: #e8f5e9; padding: 20px; border-radius: 4px;">
                    <h3>Import Job Started</h3>
                    <p>Your import is being processed in the background.</p>
                    <p><strong>Task ID:</strong> ${taskId}</p>
                    <p>You will receive an email when the import is complete.</p>
                    <p><a href="/app/common/scripting/mapreducescriptstatus.nl">View Map/Reduce Status</a></p>
                </div>
            `;

            context.response.writePage(form);

        } catch (e) {
            log.error('Upload Error', e.message);

            const form = serverWidget.createForm({
                title: 'Import Error'
            });

            form.addField({
                id: 'custpage_error',
                type: serverWidget.FieldType.INLINEHTML,
                label: ' '
            }).defaultValue = `
                <div style="background: #ffebee; padding: 20px; border-radius: 4px;">
                    <h3>Error</h3>
                    <p>${e.message}</p>
                    <p><a href="#" onclick="history.back()">Go Back</a></p>
                </div>
            `;

            context.response.writePage(form);
        }
    };

    return { onRequest };
});

Map/Reduce Import Script​

src/FileCabinet/SuiteScripts/MapReduce/data_import_mr.js

/**
 * @NApiVersion 2.1
 * @NScriptType MapReduceScript
 */
define(['N/file', 'N/record', 'N/search', 'N/runtime', 'N/email', 'N/log'],
    (file, record, search, runtime, email, log) => {

    // Field mapping configuration
    const FIELD_MAPPING = {
        customer: {
            'external_id': 'externalid',
            'company_name': 'companyname',
            'email': 'email',
            'phone': 'phone',
            'address1': 'addr1',
            'city': 'city',
            'state': 'state',
            'zip': 'zip',
            'country': 'country'
        }
    };

    /**
     * Get input data - parse CSV file
     */
    const getInputData = () => {
        const script = runtime.getCurrentScript();
        const fileId = script.getParameter({ name: 'custscript_import_file' });

        if (!fileId) {
            throw new Error('No import file specified');
        }

        log.audit('Import Start', `Processing file: ${fileId}`);

        const csvFile = file.load({ id: fileId });
        const csvContent = csvFile.getContents();

        return parseCSV(csvContent);
    };

    /**
     * Parse CSV content to array of objects
     */
    const parseCSV = (content) => {
        const lines = content.split('\n');
        const headers = lines[0].split(',').map(h => h.trim().toLowerCase().replace(/"/g, ''));
        const data = [];

        for (let i = 1; i < lines.length; i++) {
            const line = lines[i].trim();
            if (!line) continue;

            const values = parseCSVLine(line);
            const row = {};

            headers.forEach((header, index) => {
                row[header] = values[index] || '';
            });

            row._lineNumber = i + 1;
            data.push(row);
        }

        log.audit('CSV Parsed', `Found ${data.length} rows`);
        return data;
    };

    /**
     * Parse CSV line handling quoted values
     */
    const parseCSVLine = (line) => {
        const values = [];
        let current = '';
        let inQuotes = false;

        for (let i = 0; i < line.length; i++) {
            const char = line[i];

            if (char === '"') {
                inQuotes = !inQuotes;
            } else if (char === ',' && !inQuotes) {
                values.push(current.trim());
                current = '';
            } else {
                current += char;
            }
        }

        values.push(current.trim());
        return values;
    };

    /**
     * Map - validate and transform each row
     */
    const map = (context) => {
        try {
            const row = JSON.parse(context.value);
            const script = runtime.getCurrentScript();
            const importType = script.getParameter({ name: 'custscript_import_type' });

            // Validate required fields
            const validation = validateRow(row, importType);

            if (!validation.valid) {
                context.write({
                    key: 'error',
                    value: JSON.stringify({
                        line: row._lineNumber,
                        data: row,
                        error: validation.errors.join(', ')
                    })
                });
                return;
            }

            // Transform data
            const transformed = transformRow(row, importType);

            context.write({
                key: 'record',
                value: JSON.stringify({
                    line: row._lineNumber,
                    externalId: row.external_id,
                    data: transformed
                })
            });

        } catch (e) {
            log.error('Map Error', e.message);
            context.write({
                key: 'error',
                value: JSON.stringify({
                    line: context.key,
                    error: e.message
                })
            });
        }
    };

    /**
     * Validate row data
     */
    const validateRow = (row, importType) => {
        const errors = [];

        if (!row.external_id) {
            errors.push('Missing external_id');
        }

        if (!row.company_name) {
            errors.push('Missing company_name');
        }

        if (row.email && !isValidEmail(row.email)) {
            errors.push('Invalid email format');
        }

        return {
            valid: errors.length === 0,
            errors: errors
        };
    };

    /**
     * Validate email format
     */
    const isValidEmail = (email) => {
        return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email);
    };

    /**
     * Transform row using field mapping
     */
    const transformRow = (row, importType) => {
        const mapping = FIELD_MAPPING[importType] || FIELD_MAPPING.customer;
        const transformed = {};

        Object.keys(row).forEach(csvField => {
            const nsField = mapping[csvField];
            if (nsField && row[csvField]) {
                transformed[nsField] = row[csvField];
            }
        });

        return transformed;
    };

    /**
     * Reduce - create/update records
     */
    const reduce = (context) => {
        const script = runtime.getCurrentScript();
        const importType = script.getParameter({ name: 'custscript_import_type' });
        const updateExisting = script.getParameter({ name: 'custscript_update_existing' });

        if (context.key === 'error') {
            // Pass through errors
            context.values.forEach(value => {
                context.write({ key: 'error', value: value });
            });
            return;
        }

        // Process records
        context.values.forEach(value => {
            try {
                const item = JSON.parse(value);
                const result = createOrUpdateRecord(importType, item.data, item.externalId, updateExisting);

                context.write({
                    key: result.action,
                    value: JSON.stringify({
                        line: item.line,
                        externalId: item.externalId,
                        recordId: result.recordId
                    })
                });

            } catch (e) {
                log.error('Reduce Error', e.message);
                context.write({
                    key: 'error',
                    value: JSON.stringify({
                        line: JSON.parse(value).line,
                        error: e.message
                    })
                });
            }
        });
    };

    /**
     * Create or update record
     */
    const createOrUpdateRecord = (importType, data, externalId, updateExisting) => {
        const recordType = getRecordType(importType);

        // Check if exists
        const existingId = findExisting(recordType, externalId);

        if (existingId && updateExisting) {
            // Update existing
            record.submitFields({
                type: recordType,
                id: existingId,
                values: data
            });

            return { action: 'updated', recordId: existingId };

        } else if (!existingId) {
            // Create new
            const newRecord = record.create({
                type: recordType,
                isDynamic: true
            });

            Object.keys(data).forEach(fieldId => {
                try {
                    newRecord.setValue({ fieldId: fieldId, value: data[fieldId] });
                } catch (e) {
                    log.debug('Field Error', `${fieldId}: ${e.message}`);
                }
            });

            // Handle address
            if (data.addr1) {
                addAddress(newRecord, data);
            }

            const recordId = newRecord.save();
            return { action: 'created', recordId: recordId };

        } else {
            return { action: 'skipped', recordId: existingId };
        }
    };

    /**
     * Find existing record by external ID
     */
    const findExisting = (recordType, externalId) => {
        const results = search.create({
            type: recordType,
            filters: [['externalid', 'is', externalId]],
            columns: ['internalid']
        }).run().getRange({ start: 0, end: 1 });

        return results.length > 0 ? results[0].id : null;
    };

    /**
     * Get NetSuite record type
     */
    const getRecordType = (importType) => {
        const types = {
            'customer': record.Type.CUSTOMER,
            'vendor': record.Type.VENDOR,
            'item': record.Type.INVENTORY_ITEM
        };
        return types[importType] || record.Type.CUSTOMER;
    };

    /**
     * Add address to record
     */
    const addAddress = (rec, data) => {
        rec.selectNewLine({ sublistId: 'addressbook' });

        const addrSubrecord = rec.getCurrentSublistSubrecord({
            sublistId: 'addressbook',
            fieldId: 'addressbookaddress'
        });

        addrSubrecord.setValue({ fieldId: 'addr1', value: data.addr1 || '' });
        addrSubrecord.setValue({ fieldId: 'city', value: data.city || '' });
        addrSubrecord.setValue({ fieldId: 'state', value: data.state || '' });
        addrSubrecord.setValue({ fieldId: 'zip', value: data.zip || '' });
        addrSubrecord.setValue({ fieldId: 'country', value: data.country || 'US' });

        rec.setCurrentSublistValue({
            sublistId: 'addressbook',
            fieldId: 'defaultbilling',
            value: true
        });

        rec.commitLine({ sublistId: 'addressbook' });
    };

    /**
     * Summarize - generate report and notify
     */
    const summarize = (context) => {
        log.audit('Import Complete', 'Generating summary');

        const results = {
            created: 0,
            updated: 0,
            skipped: 0,
            errors: []
        };

        context.output.iterator().each((key, value) => {
            const data = JSON.parse(value);

            switch (key) {
                case 'created':
                    results.created++;
                    break;
                case 'updated':
                    results.updated++;
                    break;
                case 'skipped':
                    results.skipped++;
                    break;
                case 'error':
                    results.errors.push(data);
                    break;
            }

            return true;
        });

        // Send summary email
        sendSummaryEmail(results);

        log.audit('Import Summary', JSON.stringify(results));
    };

    /**
     * Send summary email
     */
    const sendSummaryEmail = (results) => {
        const script = runtime.getCurrentScript();
        const userId = script.getParameter({ name: 'custscript_notify_user' });

        if (!userId) return;

        let html = `
        <html>
        <body style="font-family: Arial, sans-serif;">
            <h2>Data Import Complete</h2>

            <table style="border-collapse: collapse; margin: 20px 0;">
                <tr>
                    <td style="padding: 8px; border: 1px solid #ddd;"><strong>Created:</strong></td>
                    <td style="padding: 8px; border: 1px solid #ddd;">${results.created}</td>
                </tr>
                <tr>
                    <td style="padding: 8px; border: 1px solid #ddd;"><strong>Updated:</strong></td>
                    <td style="padding: 8px; border: 1px solid #ddd;">${results.updated}</td>
                </tr>
                <tr>
                    <td style="padding: 8px; border: 1px solid #ddd;"><strong>Skipped:</strong></td>
                    <td style="padding: 8px; border: 1px solid #ddd;">${results.skipped}</td>
                </tr>
                <tr>
                    <td style="padding: 8px; border: 1px solid #ddd;"><strong>Errors:</strong></td>
                    <td style="padding: 8px; border: 1px solid #ddd;">${results.errors.length}</td>
                </tr>
            </table>
        `;

        if (results.errors.length > 0) {
            html += `
            <h3>Errors</h3>
            <table style="border-collapse: collapse; width: 100%;">
                <tr>
                    <th style="background: #f44336; color: white; padding: 8px;">Line</th>
                    <th style="background: #f44336; color: white; padding: 8px;">Error</th>
                </tr>
            `;

            results.errors.slice(0, 50).forEach(err => {
                html += `
                <tr>
                    <td style="padding: 8px; border: 1px solid #ddd;">${err.line}</td>
                    <td style="padding: 8px; border: 1px solid #ddd;">${err.error}</td>
                </tr>
                `;
            });

            if (results.errors.length > 50) {
                html += `<tr><td colspan="2">... and ${results.errors.length - 50} more errors</td></tr>`;
            }

            html += '</table>';
        }

        html += '</body></html>';

        email.send({
            author: runtime.getCurrentUser().id,
            recipients: [userId],
            subject: `Data Import Complete - ${results.created} Created, ${results.errors.length} Errors`,
            body: html
        });
    };

    return {
        getInputData,
        map,
        reduce,
        summarize
    };
});

Deployment Configuration​

<?xml version="1.0" encoding="UTF-8"?>
<mapreducescript scriptid="customscript_data_import_mr">
    <name>Data Import Map/Reduce</name>
    <scriptfile>[/SuiteScripts/MapReduce/data_import_mr.js]</scriptfile>
    <isinactive>F</isinactive>

    <scriptcustomfields>
        <scriptcustomfield scriptid="custscript_import_file">
            <label>Import File ID</label>
            <fieldtype>INTEGER</fieldtype>
        </scriptcustomfield>

        <scriptcustomfield scriptid="custscript_import_type">
            <label>Import Type</label>
            <fieldtype>TEXT</fieldtype>
        </scriptcustomfield>

        <scriptcustomfield scriptid="custscript_update_existing">
            <label>Update Existing</label>
            <fieldtype>CHECKBOX</fieldtype>
        </scriptcustomfield>

        <scriptcustomfield scriptid="custscript_notify_user">
            <label>Notify User</label>
            <fieldtype>SELECT</fieldtype>
            <selectrecordtype>-4</selectrecordtype>
        </scriptcustomfield>
    </scriptcustomfields>

    <scriptdeployments>
        <scriptdeployment scriptid="customdeploy_data_import_mr">
            <status>RELEASED</status>
            <title>Data Import</title>
            <isdeployed>T</isdeployed>
            <loglevel>DEBUG</loglevel>
        </scriptdeployment>
    </scriptdeployments>
</mapreducescript>

Next Steps​