Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load xlsb files from sub folders

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

Community Browser