How to clean field in text file - load script

[#1]

Hi!

I have an issue where I on occasion get a data dump in a text file where some characters are properly exported from the DB2 database into a text file but when the record is loaded into QV it stops at this record.

Is there a way in qlik to do a "clean" function? (e.g. in Excel there is Clean() to remove "illegal characters". As I don't know exactly what kind of illegal characters that could be entered from a user POV until it is too late, so I am looking for a generic 80/20 rule function of some kind.

Any advice or pointers (no I do not have access to another ETL tool of some kind).

Thanks,

Erik

[PabloOrtiz]

Hey, you can upload the file to see the problem?

[marcus_sommer]

Maybe this character is an "eof" and you need to add "no eof" within your file-format statement.

- Marcus

[#4]

Hi!

Sorry to say the file is 2,3 GB so I have a hard time extracting this

latest case. The only thing we know is that the character(s) is different

and suspect they come from copy/paste from like a web page or similar

Problem is we don't know the cases so we are looking for something that

could potentially catch a bulk of these types of characters.

BR,

Erik

On Wed, Jun 3, 2015 at 10:56 AM, Pablo Ortiz <qcwebmaster@qlikview.com>

[MarcoWedel]

Maybe changing the Codepage in the load statement helps ?

Regards

Marco

[#6]

Hi Erik,

if you don't know which record has a pb, you can set up ErrorMode = 0; and QV will not stop but generate an error you can trap and rely to a recno() function to knwo the record

if all records are incriminated, you may try to use purgechar() function to remove (your suspiscious) characters from a string

best regards

Chris

[#7]

Thanks Chris!

That sounds like a good suggestion because that is exactly what happens.

The load "exits" on the faulty record(so it is actually the last record

loaded) and continues with the rest of the script, i.e. load of other

tables.

On Thu, Jun 4, 2015 at 10:27 AM, christianj <qcwebmaster@qlikview.com>

[#8]

Hi Erik

Not sure to understand if you are happy with the help provided or if you need further assistance ?

Chris

[#9]

Happy! We will try this and add s step to resume load on the next row in the text file. Keep u posted

Thanks

4 jun 2015 kl. 12:22 skrev christianj <qcwebmaster@qlikview.com>:

How to clean field in text file - load script

reply from christianj in New to QlikView - View the full discussion

Hi Erik

Not sure to understand if you are happy with the help provided or if you need further assistance ?

Chris

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in New to QlikView by email or at Qlik Community

Following How to clean field in text file - load script in these streams: Inbox

© 1993-2014 QlikTech International AB | Copyright & Trademarks | Privacy | Terms of Use | Software EULA

[#10]

Hi Christian,

how exactly can you trap the error? 

rgds

Dara