Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to know if there is a way to load this file without separating the sheets up and creating synthetic keys? I have attached the excel file with the different sheets and the field names. Each month a new excel file is created.
I load the sheets: Alpharetta, Duluth, Chicago, DC, and Philadelphia together since the field names are all the same and the header is 3 lines for all of them.
FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philadelphia'
LOAD Year,
Facility,
Month,
Associate,
Attendance,
Defects,
[In/Out Pool Indicator],
[Payout - Attend],
[Payout - Defects],
Payout
FROM
[\\\My Documents\PFP\2014\*.xlsx]
(ooxml, embedded labels, header is 3 lines, table is $(vSheet));
NEXT
The Call Center and Client Services sheets are a little different the field names are called different names and may be positioned in different spots and there is more data needed from these sheets as well:
LOAD Year,
[Call Center],
Month,
Associate,
Attendance,
Quality,
Product,
[In/Out Pool Indicator1],
[Payout - Attend],
[Payout - Quality],
[Payout - Product],
Payout
FROM
[\\\My Documents\PFP\2014\*.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [Call Center]);
LOAD Year,
[Client Services],
Month,
Associate,
Escalations,
Credits,
Defects,
[In/Out Pool Indicator1],
[Payout - Escal],
[Payout - Credits],
[Payout - Defects],
Payout
FROM
[\\\My Documents\PFP\2014\*.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [Client Services]);
Any help is appreciated!!
Hi,
Try this way and add FileName() in the load script for identify the File name of the Xls and also as gwassenaar suggest for $(vSheet) as for sheet name then add that also
Script:-
FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philadelphia'
Tables:
LOAD Year,
Facility,
Month,
Associate,
Attendance,
Defects,
[In/Out Pool Indicator],
[Payout - Attend],
[Payout - Defects],
Payout
,'$(vSheet)' as Source,
FileName() as FileName
FROM
[Source*.xlsx]
(ooxml, embedded labels, header is 3 lines, table is $(vSheet));
NEXT
LET vSheet = Null();
NoConcatenate
Final:
LOAD * Resident Tables;
DROP Table Tables;
LET vSheet = 'Call Center';
Concatenate
LOAD Year,
[Call Center],
Month,
Associate,
Attendance,
Quality,
Product,
[In/Out Pool Indicator1],
[Payout - Attend],
[Payout - Quality],
[Payout - Product],
Payout
,FileName() as FileName, '$(vSheet)' as Source
FROM
[Source\OCT.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [Call Center]);
LET vSheet = 'Client Services';
Concatenate
LOAD Year,
[Client Services],
Month,
Associate,
Escalations,
Credits,
Defects,
[In/Out Pool Indicator1],
[Payout - Escal],
[Payout - Credits],
[Payout - Defects],
Payout
,FileName() as FileName,'$(vSheet)' as Source
FROM
[Source\OCT.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [Client Services]);
Hope this helps
Regards
Anand
Considering the number of common fields I'd concatenate all the source tables into one table while adding a new field in which the source of the data will be stored:
FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philadelphia'
LOAD Year,
Facility,
....etc
Payout,
$(vSheet) as Source
FROM
[\\\My Documents\PFP\2014\*.xlsx]
(ooxml, embedded labels, header is 3 lines, table is $(vSheet));
Perhaps it makes sense to rename the fields Facility, Call Center and Client Services too so the data ends up in one field. And add a new field for the type: Facility, Call Center or Client Services.
Hi
Try the below code by making all the data start from row 4(if 4 (ooxml, no labels, header is 3 lines, table is $(vSheet)); ) or 5 ((ooxml, no labels, header is 4 lines, table is $(vSheet));
FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philadelphia'
LOAD A as Year,
B as Facility,
C as Month,
D as Associate,
E as Attendance,
F as Defects,
G as [In/Out Pool Indicator],
H as [Payout - Attend],
I as [Payout - Defects],
J as Payout
FROM
[\\\My Documents\PFP\2014\*.xlsx]
(ooxml, no labels, header is 3 lines, table is $(vSheet));
Regards
Harsha
Hi,
Try this way and add FileName() in the load script for identify the File name of the Xls and also as gwassenaar suggest for $(vSheet) as for sheet name then add that also
Script:-
FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philadelphia'
Tables:
LOAD Year,
Facility,
Month,
Associate,
Attendance,
Defects,
[In/Out Pool Indicator],
[Payout - Attend],
[Payout - Defects],
Payout
,'$(vSheet)' as Source,
FileName() as FileName
FROM
[Source*.xlsx]
(ooxml, embedded labels, header is 3 lines, table is $(vSheet));
NEXT
LET vSheet = Null();
NoConcatenate
Final:
LOAD * Resident Tables;
DROP Table Tables;
LET vSheet = 'Call Center';
Concatenate
LOAD Year,
[Call Center],
Month,
Associate,
Attendance,
Quality,
Product,
[In/Out Pool Indicator1],
[Payout - Attend],
[Payout - Quality],
[Payout - Product],
Payout
,FileName() as FileName, '$(vSheet)' as Source
FROM
[Source\OCT.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [Call Center]);
LET vSheet = 'Client Services';
Concatenate
LOAD Year,
[Client Services],
Month,
Associate,
Escalations,
Credits,
Defects,
[In/Out Pool Indicator1],
[Payout - Escal],
[Payout - Credits],
[Payout - Defects],
Payout
,FileName() as FileName,'$(vSheet)' as Source
FROM
[Source\OCT.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [Client Services]);
Hope this helps
Regards
Anand
I am just a beginner and this has helped me so much thank you!