file name to a value in a new field

[Anonymous]

Hi,

I have multiple files that contain Time Stamp. Each file could be associated with a certain date (e.g. 10 April, etc.). I want to download these multiple files and name each file with associated date (e.g. File 1 is 10 April, file 2 is 11 April, etc.) and make them as values in a new field called Period.

This way I can select which day I'd like to analyse and compare.

Any idea what's the best way to do this?

Many thanks for help.

[sunny_talwar]

May be use FileBaseName() function to grab the filename into a field while loading the data and use that field to remove everything except the date.

[sorrakis01]

HI,

I think we need more information about you need....

for load the filename is:

LOAD *, FileName( ) as X from

C:\UserFiles\abc.txt

it returns:

X = 'abc.txt'

You can transformate X on a precedent LOAD or in the same LOAD

Or maybe this link can helps you: https://www.youtube.com/watch?v=VhaIaO156JM

Regards

[#4]

Hi Ahmad,

you can use filebasename() function.

load

Field1,

field1,

filebasename() as FileBasename

from path\abc*.txt

-Uva

[Anonymous]

Thank you!

[MarcoWedel]

Hi,

one solution might be:

SET LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';

table1:
LOAD *,
     Day(Date)   as Day,
     Month(Date) as Month,
     Year(Date)  as Year;
LOAD *,
     SetDateYear(Date#(FileBaseName(),'DD MMMM'),Year(Today())) as Date
FROM [yourfilepath\*.xlsx] (ooxml, embedded labels, table is Tabelle1);

hope this helps

regards

Marco

[Peter_Cammaert · Accepted solution]

Something like this? Let's assume that you have a field called TimeStamp in each file. Then you could use this as a role model to get the job done:

FOR EACH File in FileList('C:\Data\*.QVD')

   TempTable:

   LOAD * FROM [$(File)] (qvd);

   LET dStr = date(peek('TimeStamp'), 'DD MMM');

   STORE TempTable INTO (qvd);

   DROP Table TempTable;

NEXT

Best,

Peter