FILE TITLE AS FIELD VALUE

[#1]

Hello Commnunity,

I want to do somtething a bit special.

I have this as files :

the file name contain the year of the data, but in the excel table itself the year field doesnt exist.

I want to know if its possible to extract that year from the file name et create a year field directly in the script.

Thanks

[SreeniJD]

Hi There,

Create a seperate table by loading all files with a loop and store the titles as field names in that table then join this with applymap.

Hope this help

Sreeni

[#3]

Hi Jd Sreeni,

Can you give me an example (maybe the script) pleasE?

Thnaks

[swuehl]

Try something like

LOAD

     TextBetween(FileBaseName(), '-','.') as Year,

     ...

FROM Excel-2009.xls

...;

[alexandros17 · Accepted solution]

Yes, when you load the instruction FileName() returns the name of the file , so

Load

....

....

Right(filename(),4) as Year

....

Resident

....

[Gabriel]

Hi,

Year you can use FileName() Function to extract the File Name and use Subfield function to extract the part that you need.

Example

Tab1:

LOAD

...... ,

......,

FileName()               AS File_Name

,Subfield(FileName(),'-',2)          AS File_Year

FROM

......SourceFile.xls;

[#7]

include -->   Date#(LEFT(right(Filename(),8),4),'YYYY') as Year   in your Load Statement

You can include some functions in the load, like this:

Load *, filename() as Filename, Date#(LEFT(right(Filename(),8),4),'YYYY') as Year

From 12015.txt (TXT);

[#8]

please close the thread by marking the correct answer / helpful answers