Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
Can you attach sample qvw for the I have same requirement
Vikas