how can i create list of fields name dynamically?

[#1]

Hi,

I have many number of excel files i have to add a field i. fieldlist in all of these files dynamically in loop.how can i do that??.

I have tried this by the following code in script.And i want to capture field not found error in the other table how can i achieve this.please help me anybody. Here  i am attaching my code which i have written for this please have a look on it.

FOR Each File in FileList('C:\Users\vasudha\Documents\TestExce\*.xlsx')

tab:

LOAD

     Data,

     Value,

    ' ' as FieldList

FROM

'$(File)'

(ooxml, embedded labels, table is Sheet$(i));

join(tab)

load

* Inline [

FieldList

Data,

Value ];

if ScriptError = 11 then

newtab:

  Load *,

  fName& '|' &SheetNumber& '|' &FieldList as FieldKey;  

  LOAD

  FieldList,

  FileName() as fName,

  $(i) as SheetNumber,

  '$(File)' as FilePath

  FROM

'$(File)'

(ooxml, embedded labels, table is Sheet$(i), filters(Transpose()));

ENDIF

next File

[Anonymous]

Hi Vasudha,

Manually enter below line in script

ODBC CONNECT TO [Excel Files;DBQ=C:\Users\Source\YourExcelFileName.xlsx];

XlsTables:
SQLTables;

//XlsTables:  and  SQLTables will create field TABLE_NAME with list of sheetnames as rows.

//the below code will loop thru this rows and loads data from the  sheets in excel file.

LET vRows = NoOfRows('XlsTables');


FOR i = 0 TO $(vRows)-1

LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);

$(vSheetName):
LOAD *
FROM [ExcelFile.xlsx]
(ooxml, embedded labels, table is '$(vSheetName)');

NEXT i

Hope this helps

Regards

Neetha

[MarcoWedel]

Hi,

this should give you a field list of your files:

tabFields:
CrossTable (MyFieldName, MyFieldValue)
LOAD FileBaseName() as File, *
FROM [*.xlsx] (ooxml, embedded labels, table is MySheetName)
Where RecNo()=1;

hope this helps

regards

Marco

[#4]

Hi MarcoWedel,

Thanks for your reply.

I have tried your above solution.But its not giving me all numbers of fields in the table let say i have 5 fields in my table it is skiping first field in the MyFieldName column.How can i have all the column names.Please let me know.

For ex.

Tab:

CrossTable (MyFieldName, MyFieldValue)

Load  Field1,

          Field2,

          Field3,

          Field4,

          Field5

FROM

(ooxml, embedded labels, header is 5 lines, table is [Output Tracker]);

in the above example it is skiping first field i.e Field1 in MyFieldName column.

Regards,

Vasudha

[MarcoWedel · Accepted solution]

Crosstable needs at least one qualifier field, that's why I added a filename field before loading the other fields using '*'.

If you don't need the filename information, you could as well load a dummy field at first like:

LOAD 1, *

FROM ...

regards

Marco

[vikasmahajan]

Can you attach sample qvw for the I have same requirement

Vikas