Pattern 5: File-Based

Exchange data via files for legacy systems, banks, or EDI.

---

How It Works

FILE-BASED PATTERN
─────────────────────────────────────────────────────────────────

EXPORT (NetSuite → SFTP):
┌─────────────────┐      ┌─────────────────┐      ┌─────────────────┐
│ Saved Search    │ ───▶ │ Scheduled/MR    │ ───▶ │ SFTP Server     │
│ (Data Source)   │      │ (N/sftp module) │      │ (CSV/XML file)  │
└─────────────────┘      └─────────────────┘      └─────────────────┘

IMPORT (SFTP → NetSuite):
┌─────────────────┐      ┌─────────────────┐      ┌─────────────────┐
│ SFTP Server     │ ───▶ │ Scheduled/MR    │ ───▶ │ Create Records  │
│ (CSV/XML file)  │      │ (N/sftp module) │      │ in NetSuite     │
└─────────────────┘      └─────────────────┘      └─────────────────┘

---

When to Use

Scenario	Direction	File Type	Partner
Bank payment file	Export	BAI2, MT940	Banks
EDI orders	Import/Export	EDI X12	Trading partners
Inventory feed	Export	CSV	Warehouse system
Price list update	Import	CSV	ERP/PIM
Daily sales report	Export	CSV	Legacy BI
Vendor catalog	Import	XML	Suppliers

---

Scenario A: Export Invoices to SFTP

Case: Every night, export yesterday's invoices as CSV to partner SFTP server.

FLOW
─────────────────────────────────────────────────────────────────

Midnight (Scheduled)
        │
        ▼
┌─────────────────┐
│ Search:         │
│ Yesterday's     │
│ Invoices        │
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Build CSV       │
│ File            │
└────────┬────────┘
         │
         ▼
┌─────────────────┐      ┌─────────────────┐
│ N/sftp          │ ───▶ │ Partner SFTP    │
│ Upload          │      │ /incoming/      │
└─────────────────┘      └─────────────────┘

Script: Scheduled Script

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 * @description Export invoices to SFTP as CSV
 */
define(['N/search', 'N/file', 'N/sftp', 'N/log', 'N/runtime'], function(search, file, sftp, log, runtime) {

    function execute(context) {
        // Get invoices from yesterday
        var invoices = search.create({
            type: search.Type.INVOICE,
            filters: [
                ['mainline', 'is', 'T'],
                'AND',
                ['trandate', 'within', 'yesterday']
            ],
            columns: ['tranid', 'entity', 'total', 'trandate', 'status']
        }).run().getRange({ start: 0, end: 1000 });

        if (invoices.length === 0) {
            log.audit('Export', 'No invoices to export');
            return;
        }

        // Build CSV content
        var csv = 'InvoiceNumber,Customer,Amount,Date,Status\n';

        invoices.forEach(function(inv) {
            csv += [
                '"' + inv.getValue('tranid') + '"',
                '"' + inv.getText('entity').replace(/"/g, '""') + '"',
                inv.getValue('total'),
                inv.getValue('trandate'),
                '"' + inv.getText('status') + '"'
            ].join(',') + '\n';
        });

        // Create file
        var csvFile = file.create({
            name: 'invoices_' + formatDate(new Date()) + '.csv',
            fileType: file.Type.CSV,
            contents: csv
        });

        // Connect to SFTP
        var sftpConnection = sftp.createConnection({
            username: runtime.getCurrentScript().getParameter('custscript_sftp_user'),
            passwordGuid: runtime.getCurrentScript().getParameter('custscript_sftp_pwd_guid'),
            url: runtime.getCurrentScript().getParameter('custscript_sftp_host'),
            port: 22,
            hostKey: runtime.getCurrentScript().getParameter('custscript_sftp_hostkey')
        });

        // Upload file
        sftpConnection.upload({
            file: csvFile,
            directory: '/incoming/netsuite/',
            replaceExisting: true
        });

        log.audit('Export Complete', invoices.length + ' invoices exported');
    }

    function formatDate(date) {
        return date.toISOString().split('T')[0]; // YYYY-MM-DD
    }

    return { execute: execute };
});

Script Parameters:

• custscript_sftp_user: SFTP username
• custscript_sftp_pwd_guid: Password GUID (from Secrets Management)
• custscript_sftp_host: SFTP server hostname
• custscript_sftp_hostkey: SSH host key

---

Scenario B: Import Orders from SFTP

Case: Every hour, check SFTP for new order files and import them.

FLOW
─────────────────────────────────────────────────────────────────

Every Hour (Scheduled)
        │
        ▼
┌─────────────────┐      ┌─────────────────┐
│ N/sftp          │ ◀─── │ Partner SFTP    │
│ List files      │      │ /outgoing/      │
└────────┬────────┘      └─────────────────┘
         │
         │ For each new file:
         ▼
┌─────────────────┐
│ Download &      │
│ Parse CSV       │
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Create Sales    │
│ Orders          │
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Move file to    │
│ /processed/     │
└─────────────────┘

Script: Scheduled Script

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 * @description Import orders from SFTP
 */
define(['N/sftp', 'N/record', 'N/search', 'N/log', 'N/runtime'], function(sftp, record, search, log, runtime) {

    function execute(context) {
        // Connect to SFTP
        var connection = sftp.createConnection({
            username: runtime.getCurrentScript().getParameter('custscript_sftp_user'),
            passwordGuid: runtime.getCurrentScript().getParameter('custscript_sftp_pwd_guid'),
            url: runtime.getCurrentScript().getParameter('custscript_sftp_host'),
            hostKey: runtime.getCurrentScript().getParameter('custscript_sftp_hostkey')
        });

        // List files in incoming directory
        var files = connection.list({ path: '/outgoing/orders/' });

        files.forEach(function(fileInfo) {
            if (!fileInfo.name.endsWith('.csv')) return;
            if (fileInfo.directory) return;

            log.audit('Processing', fileInfo.name);

            try {
                // Download file
                var downloadedFile = connection.download({
                    directory: '/outgoing/orders/',
                    filename: fileInfo.name
                });

                // Parse and import
                var content = downloadedFile.getContents();
                var orders = parseCSV(content);
                importOrders(orders);

                // Move to processed folder
                connection.move({
                    from: '/outgoing/orders/' + fileInfo.name,
                    to: '/outgoing/processed/' + fileInfo.name
                });

                log.audit('Completed', fileInfo.name + ': ' + orders.length + ' orders');

            } catch (e) {
                log.error('Failed', fileInfo.name + ': ' + e.message);

                // Move to error folder
                connection.move({
                    from: '/outgoing/orders/' + fileInfo.name,
                    to: '/outgoing/errors/' + fileInfo.name
                });
            }
        });
    }

    function parseCSV(content) {
        var lines = content.split('\n');
        var headers = lines[0].split(',');
        var orders = [];

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

            var values = lines[i].split(',');
            var order = {};
            headers.forEach(function(header, index) {
                order[header.trim()] = values[index] ? values[index].trim().replace(/^"|"$/g, '') : '';
            });
            orders.push(order);
        }

        return orders;
    }

    function importOrders(orders) {
        orders.forEach(function(orderData) {
            // Check if already imported
            if (orderExists(orderData.OrderNumber)) {
                log.debug('Skip', 'Order ' + orderData.OrderNumber + ' already exists');
                return;
            }

            // Create Sales Order
            var so = record.create({
                type: record.Type.SALES_ORDER,
                isDynamic: true
            });

            var customerId = findCustomer(orderData.CustomerEmail);
            so.setValue('entity', customerId);
            so.setValue('memo', 'Imported: ' + orderData.OrderNumber);
            so.setValue('custbody_external_order_id', orderData.OrderNumber);

            // Add item
            var itemId = findItem(orderData.SKU);
            if (itemId) {
                so.selectNewLine({ sublistId: 'item' });
                so.setCurrentSublistValue({ sublistId: 'item', fieldId: 'item', value: itemId });
                so.setCurrentSublistValue({ sublistId: 'item', fieldId: 'quantity', value: orderData.Quantity });
                so.setCurrentSublistValue({ sublistId: 'item', fieldId: 'rate', value: orderData.Price });
                so.commitLine({ sublistId: 'item' });
            }

            so.save();
        });
    }

    function orderExists(externalId) {
        var results = search.create({
            type: search.Type.SALES_ORDER,
            filters: [['custbody_external_order_id', 'is', externalId]]
        }).run().getRange({ start: 0, end: 1 });
        return results.length > 0;
    }

    function findCustomer(email) {
        var results = search.create({
            type: search.Type.CUSTOMER,
            filters: [['email', 'is', email]]
        }).run().getRange({ start: 0, end: 1 });
        return results.length > 0 ? results[0].id : null;
    }

    function findItem(sku) {
        var results = search.create({
            type: search.Type.ITEM,
            filters: [['itemid', 'is', sku]]
        }).run().getRange({ start: 0, end: 1 });
        return results.length > 0 ? results[0].id : null;
    }

    return { execute: execute };
});

---

Scenario C: Bank Payment File (BAI2)

Case: Export payment batch as bank file format.

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 * @description Generate bank payment file
 */
define(['N/search', 'N/file', 'N/sftp', 'N/log'], function(search, file, sftp, log) {

    function execute(context) {
        // Get pending payments
        var payments = search.create({
            type: search.Type.VENDOR_PAYMENT,
            filters: [
                ['custbody_bank_file_sent', 'is', 'F'],
                'AND',
                ['status', 'is', 'VendPymt:B'] // Approved
            ],
            columns: ['tranid', 'entity', 'total', 'account']
        }).run().getRange({ start: 0, end: 500 });

        if (payments.length === 0) {
            log.audit('Export', 'No payments pending');
            return;
        }

        // Build bank file format (simplified NACHA example)
        var content = generateNACHA(payments);

        // Upload to bank SFTP
        var connection = sftp.createConnection({
            username: 'bankuser',
            passwordGuid: 'BANK_PWD_GUID',
            url: 'sftp.bank.com',
            hostKey: 'AAAA...'
        });

        var bankFile = file.create({
            name: 'ACH_' + new Date().getTime() + '.txt',
            fileType: file.Type.PLAINTEXT,
            contents: content
        });

        connection.upload({
            file: bankFile,
            directory: '/payments/'
        });

        // Mark payments as sent
        payments.forEach(function(pmt) {
            record.submitFields({
                type: record.Type.VENDOR_PAYMENT,
                id: pmt.id,
                values: { 'custbody_bank_file_sent': true }
            });
        });

        log.audit('Bank File Sent', payments.length + ' payments');
    }

    function generateNACHA(payments) {
        // Simplified NACHA format
        var lines = [];

        // File header
        lines.push('101 ...');

        // Batch header
        lines.push('5200 ...');

        // Entry details
        payments.forEach(function(pmt) {
            lines.push('6' + formatAmount(pmt.getValue('total')) + '...');
        });

        // Batch control
        lines.push('8200 ...');

        // File control
        lines.push('9 ...');

        return lines.join('\n');
    }

    function formatAmount(amount) {
        return String(Math.round(parseFloat(amount) * 100)).padStart(10, '0');
    }

    return { execute: execute };
});

---

File Format Examples

CSV Export Template

function buildCSV(records, columns) {
    // Header row
    var csv = columns.map(function(col) {
        return '"' + col.label + '"';
    }).join(',') + '\n';

    // Data rows
    records.forEach(function(rec) {
        var row = columns.map(function(col) {
            var value = rec.getValue(col.id) || '';
            // Escape quotes and wrap in quotes
            return '"' + String(value).replace(/"/g, '""') + '"';
        });
        csv += row.join(',') + '\n';
    });

    return csv;
}

XML Export Template

function buildXML(records) {
    var xml = '<?xml version="1.0" encoding="UTF-8"?>\n';
    xml += '<orders>\n';

    records.forEach(function(rec) {
        xml += '  <order>\n';
        xml += '    <id>' + escapeXML(rec.getValue('tranid')) + '</id>\n';
        xml += '    <customer>' + escapeXML(rec.getText('entity')) + '</customer>\n';
        xml += '    <total>' + rec.getValue('total') + '</total>\n';
        xml += '  </order>\n';
    });

    xml += '</orders>';
    return xml;
}

function escapeXML(str) {
    return String(str)
        .replace(/&/g, '&amp;')
        .replace(/</g, '&lt;')
        .replace(/>/g, '&gt;')
        .replace(/"/g, '&quot;');
}

---

SFTP Configuration

Getting Host Key

# On Linux/Mac
ssh-keyscan -t rsa sftp.partner.com

# Result looks like:
# sftp.partner.com ssh-rsa AAAAB3NzaC1yc2EAAA...

Password Storage

Use NetSuite Secrets Management:

1. Go to Setup → Company → Secrets Management
2. Create new secret for SFTP password
3. Copy the GUID
4. Use GUID in script parameters

---

Error Handling

function processFile(connection, filename) {
    try {
        // Download
        var file = connection.download({
            directory: '/incoming/',
            filename: filename
        });

        // Process
        processContent(file.getContents());

        // Move to success
        connection.move({
            from: '/incoming/' + filename,
            to: '/processed/' + filename
        });

    } catch (e) {
        log.error('File Error', filename + ': ' + e.message);

        // Move to error folder
        try {
            connection.move({
                from: '/incoming/' + filename,
                to: '/errors/' + filename
            });
        } catch (moveError) {
            log.error('Move Failed', moveError.message);
        }

        // Create error notification record
        createErrorRecord(filename, e.message);
    }
}

---

Best Practices

Practice	Reason
Move processed files	Prevent re-processing
Store errors separately	Easy to investigate
Use timestamps in filenames	Avoid overwrites
Validate file before import	Catch format issues early
Log file metrics	Track volumes
Use script parameters for credentials	Don't hardcode
Implement retry logic	Handle temporary failures

---

Related Patterns

• Push Out - API-based data export
• Pull In - API-based data import