12 Replies Latest reply: May 24, 2012 9:05 PM by Sokkorn Cheav RSS

Cannot read text file.

Sokkorn Cheav

Dear values visitors,

 

I got an issue with (.dat) file. For each file store like this:

{1=A    2=B    3=C}{1=X    2=Y    
3=Z}{1=N    4=M    5=O}{1=K    6=P    10=R}

This is mean that one record seperate by {} and have no specific enter line.

 

So the result after QlikView read is like below:

1=A    2=B    3=C
1=X    2=Y    3=Z
1=N    4=M    5=O
1=K    6=P    10=R

 

How can I achieve this?

 

Very much appreciate for your idea.

 

Enclosure: DATA.dat    (Open with Notepad to see content)

 

Regards,

Sokkorn

  • Re: Cannot read text file.
    Sokkorn Cheav

    Hi,

     

    Any suggestion on my case?

     

    Regards,

    Sokkorn

    • Cannot read text file.
      flipside

      Hi Sokkorn,

       

       

      Two solutions come to mind.  One would be to feed your .dat file through a vbs routine to read the data between the { and } characters line by line.

       

      The second option is to loop through the rows in the load script and rebuild the data as necessary.  Something like this would work ...

       

      DataRowTmp:
      LOAD RecNo() as DataRecRef, @1 as Data
      FROM
      [DATA.dat]
      (txt, codepage is 1252, no labels, delimiter is \x7f, no quotes); // using a delimiter you know doesn't exist

       

      DataRows: //Need to force the order of the table
      NoConcatenate LOAD DataRecRef, Data
      resident DataRowTmp order by DataRecRef;

      DROP TABLE DataRowTmp;


      LET vRowBuilder = '';

      For r = 1 to NoOfRows('DataRows')

      LET vRow = peek('Data',$(r),'DataRows');

      //let x = msgbox('$(vRow)'); //testing
      If vRow = '}' then

      RealData:
      LOAD '$(r)' as RecRef, '$(vRowBuilder)' as RealRow
      resident DataRows;

      LET vRowBuilder = '';

      ELSE

      LET vRowBuilder = '$(vRowBuilder)' & '    ' & '$(vRow)';  //not sure on tab ascii character needed

      ENDIF

      Next;

       

      It will need some tweaking to make it work exactly but should give you a start.

       

       

      flipside

    • Cannot read text file.
      Giampiero Cina

      HI,

      just to understand.

      Do you want a result table with a record like this:

       

      A    B    C    X    Y    Z    N    M    O    K    P    R

      1    2    3    1    2    3    1    4    5    1    6    10

  • Re: Cannot read text file.
    Ralf Becher

    Hi Sokkorn,

     

    I hope I understand it right, that's my proposal:

     

    temp_data:

    LOAD rec, SubField(line, '=', 1) as field, SubField(line, '=', 2) as value;

    LOAD if(RecNo()>1 and delim='', peek(rec), autonumber(if(delim='{', RecNo()))) as rec, line, delim;

    LOAD trim(left(@1:n, 1)) as delim, @1:n as line

    FROM DATA.dat

    (fix, codepage is 1252);

     

    data:

    generic load * resident temp_data where field <> '{' and field <> '}';

     

    -----

    There are also some examples here to consolidate the huge amount of tables of the generic load ..

     

    - Ralf

    • Cannot read text file.
      Sokkorn Cheav

      Hi Ralf,

       

      Your solution is awesome. Is it posible to merge all tables into one? So that I can easy to store data into one QVD.

       

      Copy: flipside: Your solution is working for small amount of data. Totally I have 10GB of data source. When apply your solution, then my pc getting stuck.

       

      Copy: giampiero: Not really. You can download Ralf's file to see data store.

       

      Copy: Ashutosh: Your solution working find but pc performance used memory arount 680,000K while Ralf's solution use only 350,000K for 3 million records.

       

      Again, Ralf's solution is the best for me. But I still need your help to combine all those generic load into one table only. The reason is easy to store into QVD file.

       

      By the way, still say many thanks for your kind support on my case.

       

      Regards,

      Sokkorn

      • Cannot read text file.
        Ralf Becher

        Sokkorn,

         

        did you see my second upload "data2.qvw" above? This is consolidated into one table at the end..

         

        - Ralf

      • Cannot read text file.
        flipside

        Hi Sokkorn,

         

         

        Yes, my solution of looping over data might not be the best for large datasets!!  I did find later that the second table load (DataRows) was not needed to force the row order.  Taking that out and changing the original table name to DataRows would make it run faster.

         

        flipside

  • Re: Cannot read text file.
    Sokkorn Cheav

    Hi All,

     

    Many thank for your prompt response. Let me test your solution after back to office.

     

    Regards,

    Sokkorn

    • Re: Cannot read text file.
      Ashutosh Paliwal

      Hi Sokkorn,

      Please find the attached file.

      While it seems that it does give the desired results.

      One strange thing I am seeing is that it is giving line breaks with every new data value, if I use any delimiter else than tab.

       

      But anyways this seems to solve your case.

       

      Please find the attached file.

       

      Thanks

      Ashutosh Paliwal

    • Re: Cannot read text file.
      Ralf Becher

      Btw, I have extended the example with the table consolidation (adjusted from Rob Wunderlich's example).

       

      - Ralf

      • Re: Cannot read text file.
        Sokkorn Cheav

        Hi Ralf,

         

        Many thanks for your solution. It working fine.

         

        Copy: Dave: I try your file with 3 million records and it spend around 3 minute. Actually I have data more than billion records. By the way your solution is good for me to get new idea.

         

        Thanks once again to all of you. You are my best friend .

         

        Sharing culture!!!!

         

        Cheer!!!!!!

        Sokkorn

  • Re: Cannot read text file.
    David Braune

    Here's a slightly different approach to collapsing the "generic" tables.  With the current data I can't measure a LOAD time but included the calculation in the QVW for use when it's run against the entire dataset.