topic Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA in QlikView

LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

hopkinsc — Fri, 11 Dec 2015 10:34:40 GMT

Hi All,

i need some help with getting some files stored to QVD but removing some characters from the data and field names.

there are over 500 files in total, i can do it manually but it will be very time consuming as each file has different field names ( i can't get * to work).

basically the field names are in the format of

@@FIELD1@@

@@FIELD2@@

@@FIELD3@@

etc

and the data held against each field is in the same format, i.e...

@@FIELD1@@

@@VALUE1@@

@@VALUE2@@

@@VALUE3@@

etc

also, the last field in in each file has a SPACE before the last 2@@ i.e.

[@@FIELD40@@ ]

the space also needs to be removed.

I have attached an example, can anyone help please?

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

sunny_talwar — Fri, 11 Dec 2015 10:58:56 GMT

I don't really understand, your qvw does seem to work. What is the issue?

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

hopkinsc — Fri, 11 Dec 2015 11:05:58 GMT

Hi, yes the sample i uploaded does work. what i am asking though is there a quick way of doing the same thing for over 500 files, all with different fields.

otherwise i have to load each file int the script, perform a find and replace @@ with nothing, remove the space from the last field name and edit the delimiter to @@|@@ manually which will be very time consuming.

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

sunny_talwar — Fri, 11 Dec 2015 11:13:45 GMT

That make sense.

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

sivarajs — Fri, 11 Dec 2015 13:10:09 GMT

You can remove the special characters in field values using mapsubstring. Here is an example,

ReplaceMap:

MAPPING LOAD * INLINE [

char, replace

]

;

TestData:

LOAD

MapSubString('ReplaceMap', data) as ReplacedString,

MapSubString('ReplaceMap', data1) as ReplacedString1

;

LOAD * INLINE [

data,data1

@@@AAA@@@,@@123@

@@@BBB@@@,@@345@@@

@CCC@@@,@1111@

@@@DDD@,@@@22@

]

;

I am not sure how we can do for all field names.

Regards,

Sivaraj

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

hopkinsc — Fri, 11 Dec 2015 15:46:50 GMT

does anyone have any ideas on how i can do this?

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

tamilarasu — Mon, 14 Dec 2015 05:34:28 GMT

Hi Hopkinsc,

Try something like below,

Sub ScanFolder(Root)

For Each FileExtension in 'txt'
   For Each FoundFile in FileList( Root & '\*.' & FileExtension)

Data:
LOAD 
SubField('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1) as FileName,
Trim(Replace (@@CVCRCD@@,  '@@','')) as CVCRCD,
Trim(Replace (@@CVDL01@@,  '@@','')) as CVDL01,
Trim(Replace (@@CVEC@@,    '@@','')) as CVEC,
Trim(Replace (@@CVCDEC@@,  '@@','')) as CVCDEC,
Trim(Replace (@@CVCKR@@,   '@@','')) as CVCKR,
Trim(Replace (@@CVUSER@@,  '@@','')) as CVUSER,
Trim(Replace (@@CVPID@@,   '@@','')) as CVPID, 
Trim(Replace (@@CVUPMJ@@,  '@@','')) as CVUPMJ,
Trim(Replace (@@CVJOBN@@,  '@@','')) as CVJOBN,
Trim(Replace ([@@CVUPMT@@],'@@','')) as CVUPMT
FROM
[$(FoundFile)]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);
Next FoundFile
Next FileExtension
End Sub

Call ScanFolder('C:\Users\Tamilarasu.Nagaraj\Desktop\Test_196312') ;

Sample application attached against your data. Let me know.

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

MarcoWedel — Tue, 15 Dec 2015 23:20:42 GMT

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

effinty2112 — Wed, 16 Dec 2015 08:57:23 GMT

Hi,

The Purgechar and SubField functions might be useful to you:

Currencies:

Load

PurgeChar(SubField(@1,'|',1),'@') as Abbreviation,

PurgeChar(SubField(@1,'|',2),'@') & ' ' & @2 as Currency;

LOAD @1,

FROM

Data1.txt

(txt, codepage is 1252, no labels, delimiter is ' ', msq) Where RecNo()>1;

gives this table:

Abbreviation	Currency
ESP	Spanish Peseta
EUR	Euro
FRF	French Franc
GBP	Pounds Sterling
ITL	Italian Lira
PLZ	Polish Zloty
PTE	Portugese Escudo
USD	U.S.A. Dollar

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

hopkinsc — Wed, 16 Dec 2015 09:08:39 GMT

Hi Andrew / Marco

Thanks for the reply. unfortunately this isnt the answer as the data was provided to me using a custom delimiter of @@|@@ because the data values include pipes. so if i strip out the @ characters and use a pipe as a delimiter then this would cause problems with the pipes that are supposed to be present in the values. (if that makes sense).

What i need is to strip out all @ from the field names, store out and read back in using the @@|@@ delimiter.

unless there is another way where i don't have to read in each file twice.

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

effinty2112 — Wed, 16 Dec 2015 09:41:06 GMT

Hi,

You can set '@@|@@' as the delimiter, we're not restricted to single characters. Then after the delimiter's done it's job we can wrap the expression in a PurgeChar:

Currencies:

Load

PurgeChar(SubField(@1,'@@|@@',1),'@') as Abbreviation,

PurgeChar(SubField(@1,'@@|@@',2),'@') & ' ' & @2 as Currency;

LOAD @1,

FROM

Data1.txt

(txt, codepage is 1252, no labels, delimiter is ' ', msq) Where RecNo()>1;

--------------------------------------------------------------------------------------------------------------

For getting rid of the '@' character from tablenames and fieldnames try something like this. The script loops through the tables and field to construct a couple of mapping tables to rename the tables and fields.

FOR i = 0 to NoOfTables()-1

Let vTblName = TableName($(i));

Let vNewTblName = PurgeChar('$(vTblName)','@');

TableNameMap:

Mapping

LOAD

'$(vTblName)' as OldName,

'$(vNewTblName)' as NewName

AutoGenerate 1;

For k = 1 to NoOfFields('$(vTblName)')

Let vFieldName = FieldName($(k), '$(vTblName)');

Let vNewFieldName = PurgeChar('$(vFieldName)','@');

FieldNameMap:

Mapping

LOAD

'$(vFieldName)' as OldFieldName,

'$(vNewFieldName)' as NewFieldName

AutoGenerate 1;

NEXT k

NEXT i

RENAME Tables using 'TableNameMap';

RENAME Fields using 'FieldNameMap';

Re: LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

MarcoWedel — Thu, 17 Dec 2015 23:51:41 GMT

Hi,

without reading a file twice:

tabTemp:
LOAD RecNo() as ID,
    IterNo() as SeqNo,
    SubField(RawData,'@@|@@',IterNo()) as SubRawData
While IterNo()<=SubStringCount(RawData,'@@|@@')+1;
LOAD Mid([@1:n],3,Len([@1:n])-4) as RawData
FROM [https://community.qlik.com/servlet/JiveServlet/download/933463-201901/Data1.txt] (fix, utf8);

mapFieldNames:
Mapping LOAD SeqNo, SubRawData Resident tabTemp
Where ID=1;

tabTemp2:
Generic
LOAD ID,
    SeqNo,
    SubRawData
Resident tabTemp
Where ID>1;

tabFinal:
LOAD Distinct ID
Resident tabTemp
Where ID>1;

DROP Table tabTemp;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'tabTemp2.*') THEN
  LEFT JOIN (tabFinal) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

RENAME Fields using mapFieldNames;

DROP Field ID;

using some code from rwunderlich to recombine the tables created by the generic load.

hope this helps

regards

Marco