Store into a text file excluding the field names

[draghici1109]

Hello Community,

I have the following homework where I don't get a progress:

I load a file like bellow:

IT0000:
LOAD
   field1, field2, field3 FROM
[..\DATA\file.qvd](qvd);

...

Later on, after some processing of the fields field1, field2 and field3, I want to store them into a txt file, yet without the name of the fields

STORE  FROM IT0000 INTO [..\DATA\Results.txt](txt, delimiter is ';');

My result looks like

field1;field2;field3
f11;f21;f31
f21;f22;f32

and yet I want to have only

f11;f21;f31
f21;f22;f32

How should the store format spec looks like?

Kind Regards, Alexandru

[marcus_sommer]

This could be done by using the field-values from the first record as field-names and then to discard this record within another load and then to rename there the field-names before storing it as txt. It needs some extra efforts in regard to a normal storing of a table.

AFAIK this isn't the first time for such a scenario so that you should find a few examples here in the community from people who solved it.

- Marcus 

[cwolf]

A simple and fast way for removing the field names from the file is using a (jscript) macro:

Macro:

function RemoveFieldNames(inPath){
	var ret="RemoveFieldNames: ";
	try{
		var inStream = new ActiveXObject("ADODB.Stream");
		inStream.CharSet = "utf-8";
		inStream.Open;
		inStream.LoadFromFile(inPath);
	
		var outPath=inPath.replace(".tmp",".txt");
		var outStream = new ActiveXObject("ADODB.Stream");
		outStream.CharSet = "utf-8";
		outStream.Open;
		
		inStream.skipline(); // skip the line with fieldnames
		while (!inStream.eos){
			outStream.WriteText(inStream.ReadText(-2) + "\n");
		};
	
		inStream.close;
		
		outStream.SaveToFile(outPath,2)
		outStream.close;
		ret += "Ok! / DeleteTmpFile: " + DeleteFile(inPath);
	}catch(err) {ret += err.message}
	return ret
}

function DeleteFile(path){
	var ret="Ok!";
	try{
		var fso=new ActiveXObject("Scripting.FileSystemObject");
		fso.DeleteFile(path);
	}catch(err) {ret = err.message}
	return ret;
}

Script:

STORE IT0000 INTO [..\DATA\Results.tmp](txt, delimiter is ';');

let qvDocPath=Replace(DocumentPath(),DocumentName(),''); // for macro it's need a full path

let jsResult=RemoveFieldNames('$(qvDocPath)..\DATA\Results.tmp');

trace $(jsResult);

- Christian

[draghici1109]

Hello Marcus,

thank you for your solution!

However I wonder why this kind of functionality is not available in the STORE command in Qlik. I suppose this functionality is needed often, mainly perhaps in the mainframe world, where this kind of storing data was usual.

Kind Regards, Alexandru

[marcus_sommer]

It's a good question why the store-command isn't so powerful as the load-command in which you could define if labels are embedded or not or how you would like to handle quotes ... IMO it's a great pity.

- Marcus 

[draghici1109]

Another solution in case the files are stored under linux is to use sed, awk or tail commands:

For example the file:

ID, BOOK_TITLE, AUTHOR, PRICE($)
1, A Knock at Midnight, Brittany K. Barnett, 13.99
2, Migrations: A Novel, Charlotte McConaghy, 13.99
3, Winter Counts, David Heska, 27.99
4, The Hour of Fate, Susan Berfield, 30.00
5, The Moon and Sixpence, W. Somerset Maugham, 6.99

These commands will skip/delete the first line/record of the file

$ sed '1d' books.csv

$ awk 'NR>1' books.csv

$ tail -n +2 books.csv

[draghici1109]

Or under Windows use the command findstr.

findstr /V /R "FieldNames" TestRNK.txt > GutesRNK.txt

[Shepherd69]

It needs some extra efforts in regard to a normal storing of a table.                      IndigoCard Login