Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a set of Excel-files, all with the same column headers but with a vairable amount of rows which contain data. Below the rows with the data I want to retreive, some other calculations are performed.
An example is given in the uploaded file:
If I load the whole sheet, I become rubbish in the tables because I only need the base data (product names and amounts = yellow cells) but all data (also the calculations) are imported and shown in my table.
Extra information on the source-files: The structures is always the same.
The only variable is here "X" (=amount of products), but is unknown at time of executing the script.
Can someone help me to create a LOAD-statement that can perform the above ?
Thanks in advance !
Message was edited by: brugst13 upon request from Deepak Vadithala
Hi Again,
Okay, that's great. Below is the final script and I have changed the script. Please note that I'm not using Mapping Load within the loop because the Mapping table will be removed after full execution of the script and not within in each loop iteration. So I have created a temporary table and used the Peek() function to extract the relevant row number.
I'm also attaching the sample QVW file for your reference. I hope this helps.
__________________________________________________________________________
DIRECTORY;
LET x = 0;
For each vFileName in FileList('.\*.xls')
LET x = $(x) + 1;
LET vPK = 0;
TestTable:
LOAD
'SourceFile' & $(x) AS SourceFileName,
[Product name],
[Amount sold],
RowNo() AS PK
FROM
$(vFileName)
(biff, embedded labels, table is SourceFile1$);
PK_Value:
LOAD
PK
Resident TestTable
where [Product name] = 'Total sold';
LET vPK = Peek('PK', -1, PK_Value);
DROP Table PK_Value;
FinalTable:
LOAD
SourceFileName,
[Product name] AS ProductName,
[Amount sold] AS AmountSold
Resident TestTable
where PK < $(vPK)
and IsNull([Product name])=0;
Drop Table TestTable;
NEXT
__________________________________________________________________________
Cheers,
DV
Hi,
I'm not sure if I have understood your question completely. Looking at the excel file do you want to load only the range A3:B11 in your excel file, Is this right? If not please can you paste the expected output in another excel file? And I'm sure one of us from community would help you.
Thanks,
DV
Hi,
I tried to clarify the problem a little bit more in the text above and in the attachment.
Thanks in advance for the quick response !
Is it possible to add another column into your Excel plan that indicate which lines would be loaded?
Hi,
I have made some progress with the following assumptions.Please let me know if these assumptions are invalid!
PS : This is not finished and it is WIP script. I'll update you tomorrow with complete script. Meanwhile, you can look at the script and let me know if this idea works.
______________________________________________________________________
For each vFileName in FileList('.\*.xls')
LET x = $(x) + 1;
LET vPK = 0;
TestTable:
LOAD
'SourceFile' & $(x) AS SourceFileName,
[Product name],
[Amount sold],
RowNo() AS PK
FROM
$(vFileName)
(biff, embedded labels, table is SourceFile1$);
MappingLoad:
Mapping LOAD
[Product name],
PK
Resident TestTable;
LET vPK = ApplyMap('MappingLoad', 'Total sold');
LOAD
SourceFileName,
[Product name] AS ProductName,
[Amount sold] AS AmountSold
Resident TestTable
where PK < $(vPK);
Drop Table TestTable;
NEXT
______________________________________________________________________
Cheers,
DV
The assumptions are correct.
- All files are in the same folder and do have the same sheetname.
- The "Total Sold Value" is present in each file (and same spelling).
I understand the suggested method. I think it could work, I'll give it a try as well.
Looking forward to see your method !
Hi Again,
Okay, that's great. Below is the final script and I have changed the script. Please note that I'm not using Mapping Load within the loop because the Mapping table will be removed after full execution of the script and not within in each loop iteration. So I have created a temporary table and used the Peek() function to extract the relevant row number.
I'm also attaching the sample QVW file for your reference. I hope this helps.
__________________________________________________________________________
DIRECTORY;
LET x = 0;
For each vFileName in FileList('.\*.xls')
LET x = $(x) + 1;
LET vPK = 0;
TestTable:
LOAD
'SourceFile' & $(x) AS SourceFileName,
[Product name],
[Amount sold],
RowNo() AS PK
FROM
$(vFileName)
(biff, embedded labels, table is SourceFile1$);
PK_Value:
LOAD
PK
Resident TestTable
where [Product name] = 'Total sold';
LET vPK = Peek('PK', -1, PK_Value);
DROP Table PK_Value;
FinalTable:
LOAD
SourceFileName,
[Product name] AS ProductName,
[Amount sold] AS AmountSold
Resident TestTable
where PK < $(vPK)
and IsNull([Product name])=0;
Drop Table TestTable;
NEXT
__________________________________________________________________________
Cheers,
DV
Have you got chance to test the above script?
Any update? Curious to know...
Thanks - DV
Have you got this working? Please let me know if you need more help.
Cheers - DV
Hello Deepak,
sorry for the late response, but I wasn't able to try your answer until now. It works as expected when converted to the right settings.
Thanks very much for your help and suggestions !