Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hopkinsc
Valued Contributor II

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

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?

11 Replies

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

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

hopkinsc
Valued Contributor II

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

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

That make sense.

sivarajs
Valued Contributor II

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

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

hopkinsc
Valued Contributor II

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

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

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') ;

Capture.PNG

Sample application attached against your data. Let me know.

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

see also:

remove ## from field names

regards

Marco

effinty2112
Honored Contributor

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

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,

     @2

FROM

Data1.txt

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

gives this table:

Abbreviation Currency
ESPSpanish Peseta
EUREuro
FRFFrench Franc
GBPPounds Sterling
ITLItalian Lira
PLZPolish Zloty
PTEPortugese Escudo
USDU.S.A. Dollar
hopkinsc
Valued Contributor II

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

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.

Community Browser