String manipulation

[#1]

Hello Community,

I have two string manipulation questions:

1. at the moment my .csv files have the following format: 20141016#name.csv.
   Using the filename function how can I turn "20141016" into an actual date (feel free to recommend a better formatting of the date) and also get the name value (without the ".csv") to load both into separate columns?
   
   
2. how do I split a filename (in this case it has nothing to do with the filename function) into name and extension when the filename contains more than one "."
   1. Example:

      Lower(Right(Name, Len(Name)-Index(Name, '.', 1))) AS Extension

      This works with a "normal" file name but what about:

                              "filename 1. of 2.txt"

                    So basically how do I go from the right to get the first "."?

Thank you.

[alexandros17]

For the second question use filename() and fileextension() they shoul have to work ...

for the first question an option (apart date formatting) could be:

Makedate(left(filename,4), mid(filename,5,2), mid(filename,7,2))

LEt me know

[martinpohl]

look at filebasename and fileextension

Fileinformation:

LOAD

     date(left(filebasename(),6),'DD.MM.YYYY') as Date,

     FileExtension() as Extension

FROM

*.qvd

(qvd);

Regards

[jonathandienst · Accepted solution]

Hi

1.     Date(Date#(Left(FileBaseName(), 8), 'YYYYMMDD'))

2a.     SubField(Name, '.', -1) As Extension

2b.     Left(Name, Len(Name) - Len(SubField(Name, '.', -1)) -1) As FileName

HTH

Jonathan

[#5]

Thank you all for showing different ways.