3 Replies Latest reply: Sep 29, 2011 11:20 AM by Anand Chouhan RSS

    Loading Data form .xls file

      Hi...

            I am usnig a .xls file to load data in to QlickView. The .xls file has a column which is named as "Left Space" and the values in this column are like (12TB, 2.3GB, 0B, 4.37MB,............). My question is,  how can I convert these values in to one single unit (lets say in gigabyte (GB))?

       

      Thanks and Regards

      Asad

        • Loading Data form .xls file
          Stefan Wühl

          Hi asadimehmud,

           

          maybe like this:

           

          INPUT:

          LOAD * INLINE [

          Left Space

          12TB

          2.3GB

          0B

          4.37MB

          ];

           

          SPACES:

          LOAD *,

          num(Amount*Pick(Match(Unit,'B','KB','MB','GB','TB'),1e-9,1e-6,1e-3,1,1e3)) as SpaceInGB;

          LOAD *,

          num#(KeepChar([Left Space],'0123456789.'), '#.#', '.' , ',') as Amount,

          KeepChar([Left Space],'TGMKB') as Unit

          resident INPUT;

           

          Regards,

          Stefan

            • Loading Data form .xls file

              Thanks a lot Stefan: My data not included just the numbers above (that was just an example).... Any ways I got the other way around and did some work inside my .xls file and got my data ready to be used in QlickView...

               

              Thanks for your suggestion and help

               

              Regards

              Asad

            • Loading Data form .xls file
              Anand Chouhan

              Hi,

               

              I suggest if you created a column and divide it by  1024 and again 1024 to bytes then you get GB value

               

              if you want MB value so divide it once by 1024 then get MB value.

               

               

              Rgds

              Anand