How do I change incoming text data

[#1]

Hi,

Can anyone advise how i can change data being imported from an excel spreadsheet?

I'm reporting fuel transaction data, our supplier has changed the format of the report from showing 'DIESEL' to now showing 'Diesel' & 'Premium Diesel'.

Is there an easy way to make all the new data appear as 'DIESEL'.

I have the same problem with other purchased fuels.

Many Thanks

[rustyfishbones]

Use REPLACE and UPPERFunction

UPPER(REPLACE(MyField,'Premium Diesel','Diesel')) as MyField

[flipside · Accepted solution]

Try using a mapping table (essentially works like an Excel lookup) as it's easier to maintain. Consider this example ...

FuelDesc:

Mapping Load * inline [

Lookup,    Return

Diesel,    DIESEL

Deisel,    DIESEL

Gas,    PETROL];

Data:

Load *, ApplyMap('FuelDesc',Fuel,'*' & Fuel) as MappedFuel Inline [

Fuel

Diesel

Deisel

Petrol

Gasoline

Gas];

In this you will see I add an asterix to highlight those values which need adding to the mapping table.

flipside

[#4]

Perfect - thankyou.