0 Replies Latest reply: Sep 26, 2011 8:03 AM by Lawrence Chapman RSS

    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