Looping through excel rows..

[sravan_v007]

Hi All,

I am trying to pull table structure from excel, instead of mentioning each field name at the Script level.

To avoid changes at script level, if any new fields add in to the table.

table structure in excel: In the below table 1st column will be field name and 2nd will be Alias name.

MetricID,	MetricID,
MetricName,	MetricName,
MetricCode,	MetricCode,
Quadrants,	Quadrants,
Cellformat,	Cellformat,
OrderID	OrderID

.Script:

Set vQVD='\\..\QVD\';

refMetric_Fields:
LOAD FieldName,
AsName
FROM
[\\...\Excel Tables.xls]
(biff, embedded labels, table is tbl_BI_Fact$);

LET vTableCount = NoOfRows('refMetric_Fields');

For vi =0 to $(vTableCount)-1
LET vFieldName = Peek('FieldName',$(vi),'refMetric_Fields');
LET vAsName = Peek('AsName',$(vi),'refMetric_Fields');

refMetric:
LOAD
$(vFieldName) as $(vAsName)
FROM $(vQVD)D_TABLE_Metric.qvd
(qvd);

NEXT

DROP Table refMetric_Fields;

which is giving me below error....

Syntax error, missing/misplaced FROM:

refMetric:

Please help me with the issue.

Thanks,

Regards,

Sravan.

[Silambarasan1306]

Hi,

Can you please share the source file.

or else you can refer the below code.

SheetNameFile:

LOAD

    SheetName,

    FileName

FROM [lib://Excel File/SheetNameFile.xlsx] (ooxml, embedded labels, table is Sheet1);

For i = 0 to NoOfRows('SheetNameFile')-1

       LET vExcelSheets = PEEK('SheetName',i,'SheetNameFile');

       LET vFileName = PEEK('FileName',i,'SheetNameFile');

Target_Temp:

LOAD

      *

FROM [lib://Excel File/$(vFileName).xlsx]

(ooxml, embedded labels, table is '$(vExcelSheets)');

NEXT i

Drop Table SheetNameFile;

[sravan_v007]

Hi,

Thanks for replay..

Please find sample data format.

Metric table:

MetricID	MetricName	MetricCode	Quadrant
E123	AS	123	People
B345	DF	234	People
C456	FG	345	Finace
D567	HJ	456	HR

I have multiple tables like above, with field names and corresponding values.

Some times the field names has to replace and there will be scenarios where new fields will be added to table.

So, instead of loading like below..

LOAD MetricID,
MetricName,
MetricCode as MetricID,
Quadrant
FROM
[\\svrin000mbp06.global.anz.com\vs3$\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Fact);

Want to take field names in excel and loop through the rows and save the field names.

FieldName	AsName
MetricID	MetricID
MetricName	MetricName
MetricCode	MetricID	//For example
Quadrant	Quadrant

Regards,

Sravan.

[Silambarasan1306]

Hi,

Instead of taking like below,

LOAD MetricID,
MetricName,
MetricCode as MetricID,
Quadrant
FROM
[\\svrin000mbp06.global.anz.com\vs3$\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Fact);

you can use,

LOAD *

[\\svrin000mbp06.global.anz.com\vs3$\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Fact);

If you want the exact query, better you can create a sample data excel file and share it.

[marcus_sommer]

Here you will find various methods to create such a load-statement:

Re: Interesting challenge: How to create a real table from a list of field names?

- Marcus