load a field only if it exists in the data source

[avastani]

I want to be able to check in the load script if my excel source or a text file source has a particular column and then load it. The reason being I have a for loop around it that cycles through all the files in the directory and if one file doesn't have that column in the source, then my process errors out for that file but continues on.

I would like for the script to intelligently check if that column exists and only then load it.

any tips? thanks.

[#2 · Accepted solution]

Here's a solution. Not that intelligent, but it does the job if your tables always have the same starting field:

VanishingField = 'ProductCost';

AlwaysFirstColumn = 'ID';

TEMP:
LOAD Count(*) as Temp
FROM [Table.xls] (biff, embedded labels, table is Sheet1$, filters(
Transpose()
))
where $(AlwaysFirstColumn) = '$(VanishingField)';

IsFieldAvailable = peek('Temp');

DROP TABLE TEMP;

if IsFieldAvailable then
MyTable:
LOAD Field1, Field2, .... , $(VanishingField)
FROM [Table.xls] (biff, embedded labels, table is Sheet1$);

else

MyTable:
LOAD Field1, Field2, ....
FROM [Table.xls] (biff, embedded labels, table is Sheet1$);

endif

[rwunderlich]

For tips on handing varitions in Excel columns and sheets, see:

http://qlikviewnotes.blogspot.com/2008/09/loading-multiple-excel-sheets.html

The QV Cookbook also has examples based on the Blog post.

For the text file(s), does the file have embedded labels? If so, you could read the label line and parse that to see what the file contains.

-Rob

[avastani]

Thanks Rob and LuciaN. I guess there's no smart way of doing it in the LOAD statement when loading the excel file using BIFF.

I wonder if it is possible with databases.

However, both solutions do serve purpose.

[#5]

Yes, it is possible with databases. Rob's post about using "SQLTABLES" command is a general solution using ODBC.