How to load multiple sheet using transform map

[Samiksha2]

Hi All,

I have an requirement to load excel with multiple sheets using transform map.
Sheet 1 - Supplier(Coulmn- Supplier name, Tax ID, Details) - Target table is  sn_fin_supplier
Sheet 2 - Details(runs automatically when Details is Y ) - Target table is  sn_fin_supplier_detail

I have create two data source for each sheet and respective transform maps. Both sheets data will store in different table and scoped application.

Here is my Supplier Oncomplete Transform script:

(function runTransformScript(import_set, map, log) {

    try {
        if (source.u_details_ == 'Y') {

            gs.info('Transform map details=Y detected - Triggering ASP Transform...');

            // Get TM_Details  Transform Map
            var grTransformMap = new GlideRecord('sys_transform_map');
            grTransformMap.addQuery('sys_id', '6e53b7bb6905cfe0954c3e45abe');
            grTransformMap.query();

            if (!grTransformMap.next()) {
                gs.info('Transform Map TM_Details not found!');
                return;
            }

            var transformMapSysId = grTransformMap.getValue('sys_id');
            log.info('Found TM_Details : ' + transformMapSysId);

            // Create new Import Set for Details 
            var grNewImportSet = new GlideRecord('sys_import_set');
            grNewImportSet.initialize();
            grNewImportSet.setValue('label',
                'ASP Auto Import - ' + new GlideDateTime().toString());
            grNewImportSet.setValue('table_name', 'u_details_import');
			grNewImportSet.setValue('data_source','77910c7377650f90cf760ce91bfc7')
            grNewImportSet.setValue('state', 'loaded');
			grNewImportSet.setValue('short_description','Type: File Format: Excel')
            var newImportSetSysId = grNewImportSet.insert();

            if (gs.nil(newImportSetSysId)) {
                gs.info('Failed to create Details Import Set!');
                return;
            }

            gs.info('Transform map Created Details  Import Set: ' + newImportSetSysId);

            // Link tagged ASP records to new Import Set
            var grASPRecords = new GlideRecord('u_details_import');
            grASPRecords.addEncodedquery('u_supplier_name='+source.u_u_supplier_name);
            grASPRecords.query();

            var recordCount = 0;
            while (grASPRecords.next()) {
                grASPRecords.setValue('sys_import_set', newImportSetSysId);
                grASPRecords.setValue('sys_import_state', 'pending');
                grASPRecords.update();
                recordCount++;
            }

            gs.info('Transform map Total Details records linked: ' + recordCount);

            if (recordCount == 0) {
                log.warn('No Details records linked - Stopping');
                return;
            }

            // Run Details Transform
            var transformer = new GlideImportSetTransformer();
            transformer.setImportSetID(newImportSetSysId);
            transformer.setTransformMapID(transformMapSysId);
            transformer.transform();

            gs.info('Transform map Details Transform completed successfully!');
        }
    } catch (ex) {
        log.error('Error in Details Auto Transform: ' + ex.message);

    }
})(import_set, map, log);

I can get the logs till gs.info('Transform map Total Details records linked: ' + recordCount);
but after that it is giving error log log.error('Error in Details Auto Transform: ' + ex.message);


Please help me.

Thanks,
Sam

[Ankur Bawiskar]

@Samiksha2 

1 data source and 2 transform (1 for each table) is sufficient

why 2 data sources? it will create 2 import set tables

If there is dependency between the records of those tables then you can keep proper order for those 2 transform maps

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Samiksha2]

Hi Ankur,
Could you please let me know the solution.
I tried a lot but its not working.