Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I got a real brainteaser,
don't even know if it's possible.
A client of mine has a dropfolder on the server where excel files are being dropped. The only thing that could separate the data between files is the timestamp in the title (YYYYMM). You can see the xls files in picture 1.
Now I would love to be able to pick these files up and store them seperately in a qvd with an extra column. In this collumn should be the date stamp. So for example, 3 xls files turn into 3 qvd files and each qvd has an extra collumn where the date from the title is set in.
Any suggestions?
Try this:
LOAD
*,
MonthName(Date#(FileBaseName(),'YYYYMM') )as YearMonth,
Month(Date#(FileBaseName(),'YYYYMM') )as Month,
Year(Date#(FileBaseName(),'YYYYMM') )as Year
From
LIB:\\Source\*.xls (...) ;
What about using filebasename() in your load?
LOAD
*,
FileBaseName() as Date
From *.xls (...) ;
Hello,
Only problem with this is that each file contains the same fields.
And the date has to be linked to this for example:
Article, Article description are non variable fields that stay the same value trough every excel.
But Stock amount taken, delivered and ordered change every month.
But the titles are all the same.
for example if i select june 2019, I should only see the data from the excel 201906.xlsx
This way I would be able to do month to month comparrisons between the stock.
Hopefully this clears something up.
The ideal scenario would be if I could import the excel, take the title date and add it in a qvd all together...
Thanks in advance
As I understand your description of your scenario and issue my solution will be useful.
When all your files contains exactly the same field names the LOAD * FROM LIB:\\Source\*.xls will automatically concatenate into one single table.
LOAD
*,
MonthName(Date#(FileBaseName(),'YYYYMM') )as AsOfMonth
From LIB:\\Source\*.xls (...) ;
When you select June 2019 you will only get the data from your 201907.xls file.
- Vegar
Hello,
I will try this as soon as I can and get back to you.
thanks in advance
Hello,
this seems to work, but I only have one question.
Now it shows up as june 2019, july 2019,....
is it possible to also split the year and month?
thanks in advance.
Try this:
LOAD
*,
MonthName(Date#(FileBaseName(),'YYYYMM') )as YearMonth,
Month(Date#(FileBaseName(),'YYYYMM') )as Month,
Year(Date#(FileBaseName(),'YYYYMM') )as Year
From
LIB:\\Source\*.xls (...) ;
Thank man,
works like a charm!