Complex Record Map with no Leading Data in body records

I have tackled a challenge like this - with additional complex wrinkles where the related records were all in separate files in one big zip-file pulled down via SFTP by my production Service and I needed to generate a combined file for the Complex Record Map to process with the appropriate prefixes in place.

I hope this will be useful to you - after getting it working, the vendor I had to write it for went belly up so I didn't finish cleaning up my traces or comments but this was fully working.  The general flow is an SFTP pickup of a zip file daily that contains 4 files... a PAT (patient) file, a data element 1, data element 2 and data element 3 file - all comma separated. the Patient Identifier in the first file could link to the other 3 files in one of the columns.

At the end, I up with a single file I pushed into CRM that looks like (where PAT ID is 123456):

PAT|123456,SMITH,JOHN,M,moredata,etc
DATA1|49492,123456,data1data,data1moredata,data1etc
DATA2|577545,123456,data1data,data1moredata,data1etc
DATA3|454543,123456,data1data,data1moredata,data1etc

I hope it's useful to you to at least get an idea of how to get started on your particular use case. Happy to try and clarify anything if needed.

 

/// Custom business service to handle ingesting multiple related delimited flat files (contained in a single ZIP!) and 
/// combining them into a single message per Patient that is then fed into a Complex RecordMap.
Class MyPkg.Services.CRM.BatchZip Extends EnsLib.RecordMap.Service.ComplexBatchStandard [ Final ]
{

Parameter ADAPTER = "EnsLib.FTP.InboundAdapter";

Parameter SETTINGS = "ZipUtility:Basic";

Parameter Slash = {$Case($System.Version.GetOS(),"Windows":"\",:"/")};

/// Operating System Utility, with parameters, that will be executed to extract zip file from vendor.
/// Use {filename} as placeholder for the dynamic ZIP file name within the parameters.
/// 
/// Note that any utility used must write the filenames of the contents to stdout for interrogation
/// by this service.
/// 
/// Default: unzip (GNU) linux utility for unix/linux based operating systems
Property ZipUtility As %String [ InitialExpression = "unzip -o {filename}" ];

Method OnProcessInput(
    pInput As %FileBinaryStream,
    pOutput As %RegisteredObject,
    ByRef pHint As %String) As %Status
{
    Set tSC = $$$OK
    Set instanceID = $System.Util.CreateDecimalGUID()
    $$$TRACE("Unique InstanceID: "_instanceID)
    Set ^MyPkg.Temp(instanceID) = "Creating CRM-compatible masterfile for this batch file input: "_pInput.Filename
    $$$TRACE("Starting to process "_##class(%File).GetFilename(pInput.Filename))
    $$$TRACE("Executing GetZipContents")
    Set tSC = ..GetZipContents(pInput.Filename, .files)
    If $$$ISERR(tSC) Quit tSC

    // Process each sub-file into a temporary global so we can add our CRM fixed leading data and join the records together
    $$$TRACE("Processing each file into a temporary global: ^MyPkg.Temp("_instanceID_")")
    Set ptr=0
    While $ListNext(files,ptr,file)
    {
        $$$TRACE("Processing file "_file)
        If ..startsWith($P(file,..#Slash,*,*),"patients_")
        {
            Set tSC = ..ProcessFile(file, ",", instanceID)
        }
        ElseIf ..startsWith($P(file,..#Slash,*,*),"dataElement1_")
        {
            Set tSC = ..ProcessFile(file, ",", instanceID)
        }
        ElseIf ..startsWith($P(file,..#Slash,*,*),"dataElement2_")
        {
            Set tSC = ..ProcessFile(file, ",", instanceID)
        }
        ElseIf ..startsWith($P(file,..#Slash,*,*),"dataElement3_")
        {
            Set tSC = ..ProcessFile(file, ",", instanceID)
        }
        Else
        {
            Do ##class(%File).Delete(file)
        }
    }
    
    $$$TRACE("Creating MasterInputFile that we'll feed into a Complex Record Map.")
    Set tSC = ..CreateMasterInputFile(pInput.Filename, instanceID, .masterInputFile)
    
    $$$TRACE("MasterInputFile: "_masterInputFile)
    $$$TRACE("Now processing MasterInputFile into Complex RecordMap.")
    Try {
        Set masterInputFileStream = ##class(%FileBinaryStream).%New()
        Set masterInputFileStream.Filename = masterInputFile
        Set tLookAhead = ""
        Set tIOStream = ##class(EnsLib.RecordMap.Service.FileServiceStream).%New(masterInputFileStream)
        Set tIOStream.Name = ..GetFileName(masterInputFileStream)
        
        While 'tIOStream.AtEnd {
            Set tPosition = tIOStream.Position
            Set tSC = ..GetBatch(tIOStream, .tBatch,,.tLookAhead)
            If $$$ISERR(tSC) || (tPosition=tIOStream.Position) Quit
            
            Set ..%SessionId = ""
            Set tStatus = ..ForceSessionId()
            If $$$ISERR(tStatus) Quit
            
            Set tSC = ..SendRequest(tBatch,'..SynchronousSend)
            If $$$ISERR(tSC) Quit
        }
        If $$$ISERR(tSC) Quit
        
        If 'tIOStream.AtEnd {
            $$$LOGWARNING($$$FormatText($$$Text("Failed to advance record stream. Stopped reading file '%1' at position %2, not at end.","Ensemble"),tIOStream.Name,tIOStream.Position))
        }
    }
    Catch ex {
        Set tSC = $$$EnsSystemError
    }
    If $get(tLookAhead) '= "" {
        $$$LOGINFO("Discarding trailing characters: '"_tLookAhead_"'")
    }
    
    $$$TRACE("Cleaning up the temporary global we created.")
    Set tSC = ..CleanUp(instanceID)
    $$$TRACE("Completed "_##class(%File).GetFilename(pInput.Filename))
    Quit tSC
}

Method ProcessFile(
    pFilename As %String,
    pDelimiter As %String = ",",
    pInstanceID As %String) As %Status [ Private ]
{
    Set tSC = $$$OK
    Set skipHeader = 1
    
    Set file=##class(%File).%New(pFilename)
    Set tSC = file.Open("RU")
    While 'file.AtEnd
    {
        Set line = file.ReadLine()
        If skipHeader
        {
            Set skipHeader = 0
            Continue    
        }
        
        If line '[ pDelimiter Continue
        
        If ..startsWith($P(pFilename,..#Slash,*,*),"patients_")
        {
            // How do we identify the 'key' value to link up the other pieces? Get a piece of the row and store it as a part of the global key!
            Set key = $Piece(line,pDelimiter,1,1)_","_$Piece(line,pDelimiter,2,2)
            // Let's give ourselves a prefix! PAT|
            Set ^MyPkg.Temp(pInstanceID,"PAT|",key) = "PAT|"_line
        }
        ElseIf ..startsWith($P(pFilename,..#Slash,*,*),"dataElement1_")
        {
            // Each dataElement has a key for itself but also a linking key to the PAT
            Set ^MyPkg.Temp(pInstanceID,"DATA1|",$Piece(line,pDelimiter,1,1),$Piece(line,pDelimiter,2,2)) = "DATA1|"_line
        }
        ElseIf ..startsWith($P(pFilename,..#Slash,*,*),"dataElement2_")
        {
            // Each dataElement has a key for itself but also a linking key to the PAT
            Set ^MyPkg.Temp(pInstanceID,"DATA2|",$Piece(line,pDelimiter,2,2),$Piece(line,pDelimiter,1,1)) = "DATA2|"_line
        }
        ElseIf ..startsWith($P(pFilename,..#Slash,*,*),"dataElement3_")
        {
            // Each dataElement has a key for itself but also a linking key to the PAT
            Set ^MyPkg.Temp(pInstanceID,"DATA3|",$Piece(line,pDelimiter,3,3),$Piece(line,pDelimiter,5,5)) = "DATA3|"_line
        }
    }
    
    Do file.Close()
    Do ##class(%File).Delete(pFilename)
    Quit tSC
}

/// Let's start putting everything together into one big file that CRM will process!
Method CreateMasterInputFile(
    pSourceFilename As %String,
    pInstanceID As %String,
    Output MasterInputFilename) As %Status [ Private ]
{
    Set tSC = $$$OK
    Set MasterInputFilename = $Replace(pSourceFilename,".zip",".txt")
    
    Set fileObj = ##class(%File).%New(MasterInputFilename)
    Set tSC = fileObj.Open("WSN")
    If ($SYSTEM.Status.IsError(tSC)) {
        Do $System.Status.DisplayError(tSC)
        Quit $$$NULLOREF
    }
    
    Set key=$Order(^MyPkg.Temp(pInstanceID,"PAT|",""))
    While key'=""
    {
        Set patID = $Piece(key,",",2,2)
        
        // Write out PAT| 
        Do fileObj.WriteLine(^MyPkg.Temp(pInstanceID,"PAT|",key))
        
        // Get dataElement1 for that PAT next... patID Key 1, dataElement1 Key 2
        Set data1Key = $Order(^MyPkg.Temp(pInstanceID,"DATA1|",""))
        While data1Key'=""
        {
            If data1Key = patID
            {
                Set data1Key2 = $Order(^MyPkg.Temp(pInstanceID,"DATA1|",data1Key,""))
                While data1Key2'=""
                {
                    Do fileObj.WriteLine(^MyPkg.Temp(pInstanceID,"DATA1|",data1Key,data1Key2))    
                    Set data1Key2 = $Order(^MyPkg.Temp(pInstanceID,"DATA1|",data1Key,data1Key2))
                }
            }
            Set data1Key = $Order(^MyPkg.Temp(pInstanceID,"DATA1|",data1Key))
        }
        
        // Get dataElement2 for that PAT next... patID Key 1, dataElement2 Key 2
        Set data2Key = $Order(^MyPkg.Temp(pInstanceID,"DATA2|",""))
        While data2Key'=""
        {
            If data2Key = patID
            {
                Set data2Key2 = $Order(^MyPkg.Temp(pInstanceID,"DATA2|",data2Key,""))
                While data2Key2'=""
                {
                    Do fileObj.WriteLine(^MyPkg.Temp(pInstanceID,"DATA2|",data2Key,data2Key2))    
                    Set data2Key2 = $Order(^MyPkg.Temp(pInstanceID,"DATA2|",data2Key,data2Key2))
                }
            }
            Set data2Key = $Order(^MyPkg.Temp(pInstanceID,"DATA2|",data2Key))
        }

        // Get dataElement3 for that PAT next... patID Key 1, dataElement3 Key 2
        Set data3Key = $Order(^MyPkg.Temp(pInstanceID,"DATA3|",""))
        While data3Key'=""
        {
            If data3Key = patID
            {
                Set data3Key2 = $Order(^MyPkg.Temp(pInstanceID,"DATA3|",data2Key,""))
                While data3Key2'=""
                {
                    Do fileObj.WriteLine(^MyPkg.Temp(pInstanceID,"DATA3|",data3Key,data3Key2))    
                    Set data3Key2 = $Order(^MyPkg.Temp(pInstanceID,"DATA3|",data3Key,data3Key2))
                }
            }
            Set data3Key = $Order(^MyPkg.Temp(pInstanceID,"DATA3|",data3Key))
        }
        
        Set key = $Order(^MyPkg.Temp(pInstanceID,"PAT|",key))
    }
    
    Do fileObj.Close()
    
    Quit tSC
}

/// Using full path, will extract Zip file using $ZF(-100) - OS-level execution - and read in the filenames
/// of what was extracted for further processing, returning as a list to OnProcessInput
Method GetZipContents(
    pFilename As %String,
    Output pContentFilenames As %List) As %Status [ Private ]
{
    Set tSC = $$$OK, tempFilenames = ""
    Set stdoutFilename = ##class(%File).TempFilename("myTempCRMBatch")
    
    Set unzipCmd = $Replace(..ZipUtility,"{filename}",pFilename)
    $$$TRACE("Executing OS command: "_unzipCmd)
    Set workingDirectory = $Piece(pFilename,..#Slash,1,*-1)
    Set unzipCmd = "cd "_workingDirectory_";"_unzipCmd
    Set sc = $ZF(-100,"/SHELL /NOQUOTE /STDOUT+="""_stdoutFilename_""" /STDERR+="""_stdoutFilename_"""",unzipCmd)
    
    Set stdout=##class(%File).%New(stdoutFilename)
    Set stdout.LineTerminator = $char(10)
    Set tSC = stdout.Open("RU")
    While 'stdout.AtEnd
    {
        Set stdoutLine = stdout.ReadLine()
        If stdoutLine [ ".csv"
        {
            Set temp = $LFS(stdoutLine," ")
            Set ptr = 0
            While $ListNext(temp,ptr,piece)
            {
                If $ZStrip(piece,"*W") [ ".csv"
                {
                    //$$$TRACE("Found file in zip: "_$ZStrip(piece,"*W"))
                    If tempFilenames '= "" Set tempFilenames = tempFilenames_","
                    Set tempFilenames = tempFilenames_workingDirectory_..#Slash_$ZStrip(piece,"*W")
                }
            }
            
        }
    }
    
    Do stdout.Close()
    Set tSC = ##class(%File).Delete(stdoutFilename)
    Set pContentFilenames = $LFS(tempFilenames,",")
    Quit tSC
}

Method CleanUp(pInstanceID As %String) As %Status [ Private ]
{
    Kill ^MyPkg.Temp(pInstanceID)
    Quit $$$OK
}

Method startsWith(
    value As %String,
    string As %String) As %Boolean [ CodeMode = expression, Internal, Private ]
{
($E($g(value),1,$L($g(string)))=$g(string))
}

}

This looks like a job for a regular record map with a batch class rather than a complex record map ...