3 Replies Latest reply: Dec 9, 2017 3:05 AM by Marcus Sommer RSS

    How to load and extract information from multiple CSV files

    Suraj Ramachandrappa

      @Hey guys,

       

      I'm a newbie in the Qlikview world and require your support in resolving 2 queries:

       

      1. I intend to read data into Qlikview from multiple CSV files and correspondingly, use that data to plot a graph.  Each of these files also contain some unwanted header information and the data to be read from all files is also identical

       

      My intention is to load these files, delimit them to remove the unwanted headers and to extract the required data information into separate tables.[For  Y1, Y2]

       

      2. I loaded a single CSV file and tried to extract the required data. The script runs successfully, but I did not receive any information in the corresponding fields. Please find code used for this particular operation

       

      Also, I did not face this problem while importing and carrying out the operation on a XLS file.

       

      Code:

       

      A:
      LOAD "X [ms]",Y1,Y2,Y3,Y4,Y5,Y6,Y7,Y8 as Data
      FROM
      [..\Test.csv]
      (
      txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 17 lines);
      B:
      LOAD  SubField(Data, ',' ,5) as Relative,
      SubField(Data, ',' ,2) as Bit
      Resident A;
      DROP
      Table A;

       

      Could anyone suggest the best way to carry out these activities?

       

      Thanks!! .

        • Re: How to load and extract information from multiple CSV files
          Marcus Sommer

          I think that the load-result from A is quite different to your expectation and load B doesn't make much such sense on it. Therefore add a rowno() to load A and uncomment load B and the drop-statement and take a look on these data within a tablebox - I assume this B load won't be needed.

           

          - Marcus

            • Re: How to load and extract information from multiple CSV files
              Suraj Ramachandrappa

              Hello Marcus,

               

              Yes, you're right. This was an incorrect approach that I took to load the CSV files.

               

              Since the header information for all the CSV files were the same, I used the * operator to import all the files into Qlikview. As a follow-up question, if I want to extract some information as a separate list for each file imported, what must be done to achieve this?

               

              Eg: The information from the field Y1 must be present as a separate list from all the files imported [Y1 from file 1 is one list, Y1 from file 2 is a separate list table]

               

              Thanks for your help.

               

              Regards,

              Suraj

                • Re: How to load and extract information from multiple CSV files
                  Marcus Sommer

                  I wouldn't do it in this way. If you have for example 100 files you would get 100 separate Y-fields which would be quite difficult to handle as dimensions and also within expressions.

                  More common is the approach to load all these data into a single table respectively field and to tag their sources. For this you could add for example filename() or filebasename() - maybe with some further text-functions like subfield(), mid(), left(), right() to extract valuable meta-informations from the path and/or the filename.

                   

                  These field(s) could be later used for selections or as a filter within the expressions, for example:

                   

                  count({< SourceField = {'SourceFieldValue1'}>} Y)

                   

                  - Marcus