9 Replies Latest reply: Jan 29, 2015 4:45 AM by Jonathan Penuliar RSS

    Loading .txt files

      HI ,

       

      Can any one of you Let me know how can I upload the attached text file with delimiter as Inverted question mark.

       

      Please find the attached file and let me know the solution .

       

      Regards,

      kunna

        • Re: Loading .txt files
          Jonathan Penuliar

          inverted question mark or ASCII code 168 is an extended code and does not seem to appear in the custom Delimiter list in Qlikview. You can have a open your file with a CSV editor and replace the character with a valid delimeter. you can use notepad++ to do that.

          You can also go back to the creator of the csv file and request to change the delimiter.

          • Re: Loading .txt files
            Dave Riley

            One option is to inspect the headers and build dynamic script based on the fields found ...

             

            raw:
            first 1
            LOAD
            @1 as header,
            SubStringCount(@1,'¿') as headercount
            FROM
            [Kunna.txt] (
            txt, codepage is 1252, no labels, no quotes);

            let h = peek('header');
            let hc = peek('headercount');
            let scr = '';
            let c = ',';

            Drop Table raw;

            //Build dynamic script
            for i = 1 to $(hc)

            if $(i) = $(hc) then
            let c = ''; //handles comma for last line
            end if

            let scr = scr & 'subField(@1,' & chr(39) & '¿' & chr(39) & ',$(i)) as [' & subfield('$(h)','¿',$(i)) & ']$(c)'; //wrap [] around fields

            next i;

            //Load data using dynamic script
            Data:
            Load
            $(scr)
            from Kunna.txt (txt, codepage is 1252, no labels, no quotes, header is 1 lines);


            It's not ideal and relies on files arriving in a consistent manner, but it's an option.

             

            flipside

              • Re: Loading .txt files
                Dave Riley

                Actually, this doesn't seem to load everything. This may be a limitation in Qlikview or even a bug because Excel loads the data correctly using codepage 1252.

                 

                If you can get the codepage used at output then that may allow you to select it using the wizard and the delimiter may change to one of the standard ones. For instance if you change the codepage (character set) to 20127 (US_ASCII) then the delimiter can be specified as a normal ? and this gives you the field names but the fields don't match up exactly.

                 

                flipside

                 


              • Re: Loading .txt files
                Marco Wedel

                Hi,

                 

                one solution could be to define the delimiter \xbf in the load statement manually:

                 

                 

                LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/701911-146733/Kunna.txt]
                (txt, codepage is 1252, embedded labels, delimiter is \xbf, msq);
                

                 

                 

                QlikCommunity_Thread_149708_Pic1.JPG

                 

                hope this helps

                 

                regards

                 

                Marco

                  • Re: Loading .txt files
                    Marco Wedel

                    fixed small issue with trailing character in field names:

                     

                    QlikCommunity_Thread_149708_Pic2.JPG

                     

                    tabHeader:
                    CrossTable (ColNum, ColNam)
                    LOAD 1, *
                    FROM [http://community.qlik.com/servlet/JiveServlet/download/701911-146733/Kunna.txt]
                    (txt, codepage is 1252, no labels, delimiter is \xbf, msq)
                    Where RecNo()=1;
                    
                    mapHeader:
                    Mapping LOAD ColNum, ColNam Resident tabHeader;
                    
                    DROP Table tabHeader;
                    
                    table1:
                    LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/701911-146733/Kunna.txt]
                    (txt, codepage is 1252, no labels, delimiter is \xbf, msq)
                    Where RecNo()>1;
                    
                    RENAME Fields using mapHeader;
                    

                     

                    hope this helps

                     

                    regards

                     

                    Marco

                      • Re: Loading .txt files
                        Dave Riley

                        Marco,

                         

                        It seems you are right that the delimiter may be ┐(ascii 191) which converts to hex bf (and therefore delimiter \xbf). And using codepage 65001 (UTF-8) we might have a simpler result ...

                         

                        Data:
                        Load * FROM
                        [Kunna.txt]
                        (
                        txt, codepage is 65001, embedded labels, delimiter is \xbf, no quotes);

                         

                        I also had to set quoting off ('no quotes').

                        flipside

                        • Re: Loading .txt files
                          Jonathan Penuliar

                          Thanks a lot Marco,

                           

                          I picked-up a few tricks from your example.

                          1. \xbf has lead me to mojibake.

                          2. Load from web attachment.

                          3. Cross Tab + Rename() combo

                           

                          Best Regards,

                          JP