Load from Excel pull from substring

[#1]

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

[swuehl · Accepted solution]

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

[#3]

Thanks Swuehl,