Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Have a query on handling the .xlsx structure formate change.
Currently on daily basis we are reading a excel file. After certain time additional fields are added to excel file.
How to handle this while reading into the Qlikview when we load specifiying the filed names.
Hi!
I am trying to recreate your scenary,
I have a directory with severals Files.
All with the same format, only the F05 has different format.
Well.. as I comment above I loop the filelist.
FOR Each var_FileName in Filelist ('C:\Test\*.xlsx')
QUALIFY *;
Aux:
Load
*
From [$(var_FileName)]
(ooxml, embedded labels, table is Sheet1);
UNQUALIFY *;
var_NoOfFields = NoOfFields('Aux');
_tableName = 'Table';
SET _TableExist = "not(isnull(TableNumber('$(_tableName)')))";
LET _concat = if($(_TableExist), 'CONCATENATE ($(_tableName))', '');
drop Table Aux;
IF $(var_NoOfFields) = 3 THEN
[$(_tableName)]:
$(_concat)
LOAD A,
B,
C
FROM
[$(var_FileName)]
(ooxml, embedded labels, table is Sheet1);
ELSEIF $(var_NoOfFields) = 5 THEN
[$(_tableName)]:
$(_concat)
LOAD A,
B,
C,
D,
E
FROM
[$(var_FileName)]
(ooxml, embedded labels, table is Sheet1);
ENDIF
NEXT var_FileName
I hope this can help you..
Kind regard
Agustin.
Hi Swe,
Maybe you can use '*' on the load.
Load *
FROM
[file.xlsx]
(ooxml, embedded labels, table is sheet1);
best regards,
Agustin.
Thanks Agustin.
But however there is a existing code which I cannot touch.
Now i have to read this new files along with old files. Just that in new files additional fileds are added.
Say earlier A,B,C fields existis in old files. where as new files have A,B,C,D,E.
But all the files exists in the same folder.
u can do concatenate ....by that it will do cath all the data .
Well,
You can load the new file separately and concatenate both, trying to maintain the same name of the old files.
Old:
Load
A,
B,
C
From OldFiles.xlxs
Concatenate(Old)
Load
A,
B,
C,
D,
E,
From NewFiles.xlxs
something like this I think
Best regard,
Agustin
I Augstin, Am looking for logic which says new file and oldfile?
You can identify which is new and old with the name of the file? or only you know beacause has more field?
Hi,
Based on time stamp i can verify new or old. But how to read the file name from the directory?Any idea?
The way you can read the file name is with the function filename() in the load, returns the name of the table file currenrly being read.
Load *,
filename( ) as Filename
from excel.xlxs
This is after reading the excel.xlsx. But am looking for reading a file name from the directory. Say there are 100s of files. Would like to read those file name and get the timestamp out of it.