how to check if data format is correct txt load

[felcar2013]

     Hi

i wanted to know if you check when loading data from text files if  the data has the right format

Ex.

I got a text file and a couple of records contained ">" which is the sign for ">" in html. Qlikview interpreted the last ";" as separator and i got those column values in the new column. How can i check that no semicolons are inside where they should not be or it is better to set a data Transfer rule, avoiding this

thanks

[rustyfishbones]

You can use Replace() function or Purgechar() to remove these

Can you share a sample txt file

Regards

Alan

[felcar2013]

but i am using a text file with " ; " as separator. how should i use the purgechar() or replace()?

[felcar2013]

i attached an ex

[felcar2013]

sorry i don't see how to attach the file ( i see the opt for vides or Picture)

[ali_hijazi]

go to Edit Discussion to be able to add an attchment

[felcar2013]

thanks, even though is funny i  cannot see the Option when i answer, only when editing

[jonathandienst · Accepted solution]

Hi

You might need to load the text file by line, rather than by field. Then replace the HTML encoded values and do a LOAD ... FROM_FIELD to get the data. You can use a MapSubstring to decode the values. Like this:

//** HTML code mapping table

HTMLDecode:

Mapping LOAD * Inline

[

  Code, Value

  >, >

  &lt;, <

  &amp;, &

];

//*Initial load via mapping to decode. Skip labels.

T_All:

LOAD MapSubString('HTMLDecode', @1:n) As All

FROM FromField.txt

(fix, header is 1 lines, codepage is 1252);

//** FROM_FIELD load to unpack the line - note the explicit labels

Data:

LOAD @1 as Country,

     @2 as SalesCode

FROM_FIELD

(T_All, All)

(txt, utf8, explicit labels, delimiter is ';', msq);

DROP Table T_All;

You will need to adjust the last load to suit your model and data.

HTH

Jonathan

[felcar2013]

thanks it worked perfeclty; but not good for Million rows