2 Replies Latest reply: Jan 5, 2012 12:16 PM by Miguel Angel Baeyens de Arce RSS

    quoting - non-standard characters

      I have a text file to import where the fields are separated by carets (^) and the text fields are surrounded by tildes (~). I figured out how to use the custom delimiter but how do I specify tildes as the quoting character? Is this even possible?

       

      Thanks,

      Jimmy

        • quoting - non-standard characters
          Stefan Wühl

          I think the only place where you can specify the quoting style is in the format specifier, where you can specify 'msq' (modern style quoting) or 'no quotes'. I think standard quoting using single or double quotes is default.

           

          So have you tried importing your file with one of these settings and then REPLACE or PURGECHAR the tildes in your text fields?

          • Re: quoting - non-standard characters
            Miguel Angel Baeyens de Arce

            Hi Jimmy,

             

            Since it's not possible to tell QlikView what character is the quoting symbol, you can do something like the following:

             

            Data:
            LOAD
            SubField(Replace(@1, '~', Chr(39)), '^', 1) AS Field1,
            SubField(Replace(@1, '~', Chr(39)), '^', 2) AS Field2,
            // and so
            
            FROM
            File.txt
            (txt, codepage is 1252, no labels, delimiter is '^');
            

             

            That will load each row, storing each string separated by "^" as a different field, and replace the "~" char for the single quote (ascii 39) or any other more suitable character, or even nothing.

             

            Load will take longer since it's doing some string formatting, but it might do.

             

            Hope that helps.

             

            Miguel