How to merge two text files that are created with a Turbo Integrator process

by ·0 Comments

Product:
Cognos TM1 10.2.2
Microsoft Windows 2008 R2 Server

 

Issue:
How to merge two text files that are created with a Turbo Integrator process? ASCIIOUTPUT will overwrite a file if called in a different tab of the same process in TI editor in TM1 Architect program.

 

Possible solution:
Merge the files after the TI process with a MS DOS command. Here a suggestion, the variables are filled with values earlier in TI code with CellGetS or from the cubeview that is read.

 

In PROLOG create the file name for the output, static or from a SYSTEM cube.

#————————————————–

# define file name to be used by combination a lot of variables or prompts.

#—————————————————-

sFileName = ‘RESULTAT_’| pTarget |’_SaldoHB_’| pHuvudbokstyp |’_’| paCompany |’_’|  pPeriod  |’_’| vTime |’.csv’;

 

#—————————————————————————-

#   save the filename in the system cube

#   to make it possible to use the file later

#—————————————————————————

if (LONG (pFileStorage) = 0);

pFileStorage= ‘File1’;

endif;

CellPutS ( sFilename, ‘Sys.Settings’, pFileStorage , ‘Value’ );

 

In METADATA create a unique file name for the file

#——————————————

# create the output in own file in meta tab

#——————————————

sFilename2 = sfilename | ‘_meta.csv’;#——————————————

# create the file from the list of variables

#——————————————

q=char(73);

Asciioutput( sPath | ‘\’ | sFilename2,

 

sBASE_DT ,

sHUVUDBOKSTYP ,

sFORETAG ,

sKONTO ,

sREDOVISNINGSPRODUKT ,

sVALUTA ,

sURSPRUNGSVALUTA ,

sINTERNMOTPART_FORETAG ,

sLANDSKOD,

sCOUNTRY  ,

sSALDO

);

 

In the DATA tab you fill with new data to file with unique name

#—————————————————————–

#  create data in own filename

#————————————————————————–

sFilename3 = sfilename | ‘_data.csv’;

 

#—————————————————————–

#  output the data in variables from data to a own file

#————————————————————————–

q=char(73);

Asciioutput( sPath | ‘\’ | sFilename3,

 

sBASE_DT ,

sHUVUDBOKSTYP ,

sFORETAG ,

sKONTO ,

sREDOVISNINGSPRODUKT ,

sVALUTA ,

sURSPRUNGSVALUTA ,

sINTERNMOTPART_FORETAG ,

sLANDSKOD,

sCOUNTRY  ,

sSALDO

);

 

In EPILOG you create the DOS command and do the concatenation of the files.

#———————————-

#  merge the two files to one

#———————————-

sFileA = sPath | ‘\’ | sFilename2;

sFileB = sPath | ‘\’ | sFilename3;

sFileC = sPath | ‘\’ | sFilename;

 

Executecommand ( ‘CMD /c copy ‘| sFileA |’  +  ‘| sFileB |’  ‘| sFileC |’  ‘, 1);

 

#———————————————————-

# erase the temp files from the folder

#———————————————————–

Executecommand ( ‘CMD /c DEL ‘| sFileA |’   ‘, 1);

Executecommand ( ‘CMD /c DEL ‘| sFileB |’   ‘, 1);

 

But above code have a error, it gives a file with a line-return last, so to get rid of the the last empty line in the final file you need to use the TYPE command instead of COPY. This copy the FileA to the FileB, so FileB will contain both files content.

Executecommand ( ‘CMD /c   TYPE ‘| sFileA |’  >>  ‘| sFileB |’    ‘, 1);

Then you need to adjust above code to create that sFileB variable contain the name of the file you finally want it to be.

Note, that below will not work because RENAME does not support paths in name.

Executecommand ( ‘CMD /c  REN ‘| sFileB |’    ‘| sFileC |’    ‘, 1);