Load xlsb files from sub folders

Mon, 26 Sep 2011 12:03:17 GMT

Hi all,

I have been task with loading data from an existing file structure into to QV which is causing me a few headaches.

Firstly the files are XLSB files (Excel Binary Files) which means I have to use to ODBC driver to load them. Ths only seems to except single files and the script below doesn't work.

ODBC CONNECT TO [Property Valutions;DBQ=W:\Property Statistics\Valuers Templates\2011\June 2011\KF\*.xlsb];

LOAD `BL Prop Ref`,

`Current Valuation`,

`Equivalent Yield`,

`Final Reversionary Yield`,

`Initial Yield`,

`Landmark Previous Valuation`,

`Previous Valuation`,

`Valuation ERV`,

`Valuers Type`;

SQL SELECT `BL Prop Ref`,

`Current Valuation`,

`Equivalent Yield`,

`Final Reversionary Yield`,

`Initial Yield`,

`Landmark Previous Valuation`,

`Previous Valuation`,

`Valuation ERV`,

`Valuers Type`

FROM `W:\Property Statistics\Valuers Templates\2011\June 2011\KF\*.xlsb`.`_FilterDatabase`;

Secondly the second line of each binary file is blank which means QV doesn't load any data. (example attched)

Thirdly I need to be able to load all files from a whole deirectory structure. ie load all files contained in any subfolders. The file structure is as below

Level 1 - Year (eg 2008,2009,2010, 2011 etc)

Level 2 - Valuation Quater (March 2011, June 2011, Sept 2011)

Level 3 - Comany (Comapny 1, compnay 2, Company 3 etc)

See attached folder structure

Unfortuneatly I have no control over the file type, structure, or folder structure so need to get QV to load this data in its current format.

Your help would be most appreciated.

Many thanks,

Lawrence Chapman

topic Load xlsb files from sub folders in QlikView