2 Replies Latest reply: Jan 19, 2012 5:39 AM by Anne Duffy RSS

    Load from Excel pull from substring

      Hi Guys

       

      I am loading an excel spreadsheet and the data is not 100% how I would like it ,

      there is a column called Reasons, which may contain

       

      "No receipt","No Authorisation","No Paper","No Ticket"

       

      Or a combination such as

       

      "No receipt,No Paper,No Ticket" , "No Authorisation,No Ticket"

       

      I wish to be able to load the column as normal so as Reasons and have both singular reason and combined as results.

       

      But then another load where the data is split by "," so for the combinations have

       

      "No receipt","No Paper","No Ticket" - it would be the data text to columns function in excel dont know if there is an equivlent in QV

       

      Can anyone advise ?

       

      Thanks


      Anne

        • Load from Excel pull from substring
          Stefan Wühl

          I believe the excel function will fill in each substring into a different column, right? If you consider the Excel lines as records and columns as fields, do you really want to create new fields?

           

          I assume that you probably don't want to do that, but I assume that you want to create new records per substring in one field, so you get e.g. in a list box of that field a line per substring.

           

          You could use subfield for that.

           

          If you have loaded your table

           

          INPUT:

          LOAD

          recno() as RecID,

          Reason,

          ..

          from ExcelTable.xls;

           

          You could do something like:

           

          LOAD RecID,

          subfield(Reason, ',') as ReasonSubs

          resident INPUT;

           

          Hope this helps,

          Stefan