I am trying to load an excel file that has more than one sheet with different field names but has the same type of data.

[neena123]

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!!

[Gysbert_Wassenaar]

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.

[Anonymous]

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

[its_anandrjs · Accepted solution]

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

[neena123]

I am just a beginner and this has helped me so much thank you!