Concatenation and sort by case ID

[lls]

I made a code to automatically concatenate all my .dat files.
I would like to have all cases sorted ascending based on case identification. Cases are named by year and N (2011001, 2011002,, 2012001, 2012002, etc.). I know I can do that using the Sort Data tool, but it would be more convenient to have it sorted here automatically.

How can I do that?

Code: Select all

	elseif $ = 8 then  // concatenate

fileconcat(maketext("%s\\Concatenation\\%d_%s",strip(dataFolderName),sysdate("DDMMYYYY"),strip(concatFileName)),
		maketext("%s\\%s",strip(dataFolderName),"*.dat"));
		
				 		errmsg("Success");
				 
		reenter;

[Gregory Martin]

There's no way to do this easily because the fileconcat function doesn't take a parameter that specifies the order of the files to be concatenated. However, with some logic, you can achieve your goal. You can call the dir command, specifying the sort order, and redirecting this to a file. Then you could parse that file and create a concatenate PFF from the output. It's potentially overkill, but it works. See below, and the attached application. It combines several files in a certain folder and puts the output on the Windows desktop.

PROC GLOBAL

FILE dirFile,pffFile;

alpha (300) str;
alpha (300) baseFolder;

alpha (3) sortOrder = "/on"; // for ascending alphabetical order
//alpha (4) sortOrder = "/o-n"; // for descending alphabetical order

PROC AFL_FF

preproc

    baseFolder = concat(pathname(application),"Data");

    str = maketext('cmd /c "dir "%s\*.dat" /b %s > "%sdir.txt""',strip(baseFolder),sortOrder,pathname(temp));
    execsystem(str,wait);
    
    setfile(dirFile,maketext("%sdir.txt",pathname(temp)));
    setfile(pffFile,maketext("%sconcat.pff",pathname(temp)));
    
    filewrite(pffFile,"[Run Information]");
    filewrite(pffFile,"Version=CSPro 4.1");
    filewrite(pffFile,"AppType=Concatenate");
    filewrite(pffFile,"[Files]");
    filewrite(pffFile,"OutputData=%sAllFiles.dat",pathname(Desktop));

    while fileread(dirFile,str) do
        filewrite(pffFile,"InputData=%s\%s",strip(baseFolder),strip(str));
    enddo;
    
    filewrite(pffFile,"[Parameters]");
    filewrite(pffFile,"ViewListing=Never");
    filewrite(pffFile,"ViewResults=No");
    
    
    close(dirFile);
    filedelete(filename(dirFile));
    
    close(pffFile);
    execpff(filename(pffFile),wait);
    filedelete(filename(pffFile));

[lls]

Thank you so much for your help.
The output is not exactly what I expected. If I understand correctly your code, it will reorder all .dat files based on their name (dacb --> abcd)?

What I was trying to do is the following.
For example, I have the following .dat files based on the keyers initials:

AD .dat
ES .dat
FB .dat
SW .dat

If I concatenate those files using the code I made (it will create 24072012_concat.dat), all cases will not be sorted in a logical order, it will be like this:

2012222
2012888
2012989
2011021
2011007
2011006
2012111
2012999
2012555
2012777

What I would like to have after concatenation (24072012_concat.dat) is cases sorted ascending as in the following:

2011006
2011007
2011021
2012111
2012222
2012555
2012777
2012888
2012989
2012999

This output has been generated automatically using the Sort Data tool. That is the result I would like to have through a logic code associated with the fileconcat code I made.

The purpose of this is that I made a pff which will put all cases on a html file and open it on FireFox. Then, it is possible to visualize entered data case by case (including dictionnary questions and labels) as well as to print out selected cases using html and css codes. The html file is made from the concatenated file so the order of cases on the concatenated file is reflected on the html file.

Is it possible to reorder cases on a single .dat file using logic?

[Gregory Martin]

Ah, then you don't need my code at all. You can continue to use fileconcat with the *.dat wildcard. But what you'll have to do is write out a .ssf (sort specification file) and a .pff to run the sorting. Then you can use execpff to sort the data file, and then open the file in your web browser.

Does this make sense? You should open the sort tool, open your dictionary, select the keys to sort on, and then save a .ssf file. Open that file in a text viewer and you'll have a clue as to how to write your logic.

[lls]

I am sorry for the misunderstanding.
Thank you for the tip, I will try as you said.

[lls]

OK, it seems to be working now with the following code.
I don't know if this is the easyest way, please feel free to comment if possible to improve.

Code: Select all

	elseif $ = 3 then   // create an HTML file with KEYER_NAME .dat file
	
  // First, create .srt file

		setfile(pffFile,"..\..\GDC_Questionnaire\Batch\SortKeyer.ssf",create);

		filewrite(pffFile,"[CSSort]");
		filewrite(pffFile,"Version=CSPro 4.1");

		filewrite(pffFile,"[Dictionaries]");
		filewrite(pffFile,"File=%s%s\\GDC_Dossiers.dcf","00.00.00 00:00:00,",strip(entryFolderName));
		
		filewrite(pffFile,"[SortType]");
		filewrite(pffFile,"Type=Questionnaire");

		filewrite(pffFile,"[Keys]");
		filewrite(pffFile,'Key=Ascending,NUMDOSID,"Numero de dossier"');
		
  // Second, run .srt file
		
		setfile(pffFile,"..\..\GDC_Questionnaire\Batch\SortKeyer.pff",create);
				
		filewrite(pffFile,"[Run Information]");
		filewrite(pffFile,"Version=CSPro 4.1");
		filewrite(pffFile,"AppType=Sort");
				
		filewrite(pffFile,"[Files]");
		filewrite(pffFile,"InputData=%s\\%s.dat",strip(dataFolderName),KEYER_NAME);
		filewrite(pffFile,"Application=%s\\SortKeyer.ssf",strip(batchFolderName));		
		filewrite(pffFile,"OutputData=%s\\%s.srt",strip(dataFolderName),KEYER_NAME);
		filewrite(pffFile,"Listing=%s\\SortKeyer.lst",strip(batchFolderName));
		
		filewrite(pffFile,"[Parameters]");
		filewrite(pffFile,"ViewListing=OnError");
		filewrite(pffFile,"ViewResults=No");

		close(pffFile);
		execpff(filename(pffFile));
		
  // Third, create HTML file from .srt file
		
		setfile(pffFile,"..\..\GDC_Questionnaire\Batch\BatchHTML.pff",create);
				
		filewrite(pffFile,"[Run Information]");
		filewrite(pffFile,"Version=CSPro 4.1");
		filewrite(pffFile,"AppType=Batch");
		
		filewrite(pffFile,"[Files]");
		filewrite(pffFile,"Application=%s\\BatchHTML.bch",strip(batchFolderName));
		filewrite(pffFile,"InputData=%s\\%s.srt",strip(dataFolderName),KEYER_NAME);
		filewrite(pffFile,"Listing=%s\\BatchHTML.lst",strip(batchFolderName));

		filewrite(pffFile,"[UserFiles]");
		filewrite(pffFile,"HTML=%s\\Questionnaires.html",strip(htmlFolderName));
		
		filewrite(pffFile,"[Parameters]");
		filewrite(pffFile,"ViewListing=OnError");
		filewrite(pffFile,"ViewResults=Yes");

		close(pffFile);
		execpff(filename(pffFile));
		
		reenter;

[lls]

Should anyone be interested, here is an example how to automatically (1) concatenate several .dat files, (2) Create a .sst (sorting) file (3) sort cases ascending, (4) create a html file containing sorted cases for viewing in a web browser.

Code: Select all

elseif $ = 6 then   // create an HTML file with concateneted .dat file

  // First, concatenate all .dat files

fileconcat(maketext("%s\\Concatenation\\%d_concat.dat",strip(dataFolderName),sysdate("DDMMYYYY")),
		maketext("%s\\%s",strip(dataFolderName),"*.dat"));
		
  // Second, create .srt file

		setfile(pffFile,"..\..\GDC_Questionnaire\Batch\SortKeyer.ssf",create);

		filewrite(pffFile,"[CSSort]");
		filewrite(pffFile,"Version=CSPro 4.1");

		filewrite(pffFile,"[Dictionaries]");
		filewrite(pffFile,"File=%s%s\\GDC_Dossiers.dcf","00.00.00 00:00:00,",strip(entryFolderName));
		
		filewrite(pffFile,"[SortType]");
		filewrite(pffFile,"Type=Questionnaire");

		filewrite(pffFile,"[Keys]");
		filewrite(pffFile,'Key=Ascending,NUMDOSID,"Numero de dossier"');
		
  // Third, run .srt file
		
		setfile(pffFile,"..\..\GDC_Questionnaire\Batch\SortKeyer.pff",create);
				
		filewrite(pffFile,"[Run Information]");
		filewrite(pffFile,"Version=CSPro 4.1");
		filewrite(pffFile,"AppType=Sort");
				
		filewrite(pffFile,"[Files]");
		filewrite(pffFile,"Application=%s\\SortKeyer.ssf",strip(batchFolderName));
		filewrite(pffFile,"InputData=%s\\Concatenation\\%d_concat.dat",strip(dataFolderName),sysdate("DDMMYYYY"));		
		filewrite(pffFile,"OutputData=%s\\Concatenation\\%d_concat.srt",strip(dataFolderName),sysdate("DDMMYYYY"));
		filewrite(pffFile,"Listing=%s\\SortKeyer.lst",strip(batchFolderName));
		
		filewrite(pffFile,"[Parameters]");
		filewrite(pffFile,"ViewListing=OnError");
		filewrite(pffFile,"ViewResults=No");

		close(pffFile);
		execpff(filename(pffFile));
		
  // Fourth, create HTML file from .srt file
			
		setfile(pffFile,"..\..\GDC_Questionnaire\Batch\BatchHTML.pff",create);
				
		filewrite(pffFile,"[Run Information]");
		filewrite(pffFile,"Version=CSPro 4.1");
		filewrite(pffFile,"AppType=Batch");
		
		filewrite(pffFile,"[Files]");
		filewrite(pffFile,"Application=%s\\BatchHTML.bch",strip(batchFolderName));
		filewrite(pffFile,"InputData=%s\\Concatenation\\%d_concat.srt",strip(dataFolderName),sysdate("DDMMYYYY"));
		filewrite(pffFile,"Listing=%s\\BatchHTML.lst",strip(batchFolderName));

		filewrite(pffFile,"[UserFiles]");
		filewrite(pffFile,"HTML=%s\\Questionnaires.html",strip(htmlFolderName));
		
		filewrite(pffFile,"[Parameters]");
		filewrite(pffFile,"ViewListing=OnError");
		filewrite(pffFile,"ViewResults=Yes");

		close(pffFile);
		execpff(filename(pffFile));
		
		reenter;