How to use Mapping load to add field in the table

[#1]

Hi,

I want to extract a part from the name of the file xlsx and add it as a field in the table using mapping.

In fact, I want to read the second part of the name of the file and change it, then I have to add it as a field in the table:

The name of my file is: "Règlements Bel 22022014.xlsx", I have to extract "Bel" and convert it into "Belgique" and add it as field "country" in the table.

I did this, it is correct?

PaysBel_Map:

Mapping load * ,

subfield(FileName(), ' ',2) as 'Belgique'

from [..\..\Réglements BEL 20022014.xls] ;

Réglement:

Load *,

     ApplyMap ('PaysBel_Map','Belgique') as Country

FROM

[..\..\Réglements BEL 20022014.xls] (biff, embedded labels, table is A$);

Then I have to extract the third part which is the date and add it in the table as field "Date"

How can I do this?

Help me please

[#2]

maybe:

right( subfield(FileName(),'.',1),8)

[Anonymous]

To use applymap, you need to have a map first.  There is no way the script can guess that 'BEL' means 'Belgique' unless you tell this in a mapping table, for example:

CountryMap:
MAPPING LOAD * INLINE [
A, B
BEL, Belgique
FRA, France
...
];

Réglement:
LOAD
*,
applymap('CountryMap', subfield(FileName(), ' ',2)) as Country,
date(date#(replace(subfield(FileName(), ' ',3), '.xls',''),'DDMMYYYY')) as Date
FROM [..\..\Réglements BEL 20022014.xls] (biff, embedded labels, table is A$);