3 Replies Latest reply: Sep 21, 2011 3:32 AM by Rosalie Ramos RSS

    QVD to Excel

      Hello,

       

      Please help, I need to export a QVD file to excel.

       

      I have a huge number of QVD files that needs to be exported to Excel. Can anybody help me to find a way so that I can create a macro that will automatically do this?

       

      I read a topic here in qlikview community almost related to this but not quite. It says I need to reload the qvd first. I saw a script for macro but I don't know what to do next. (I'm so new to Qlikview scripting). =)

       

      Here's my sample script:

       

      TB01:
      LOAD MetricIndex,
           [RFS Processing - Request for Service (RFS)],
           [RFS Processing - Early Start Letters (ESL)],
           [RFS Processing - Close - Out Letters (COL)],
           [IS Reports - IS Volume Significant Variance],
           [IS Reports - Telecom Significant Variance],
           [IS Reports - RFS List],
           [IS Reports - ADHOC Files],
           [IS Reports - National Summary Bill],
           [IS Reports - AT&T VPN],
           [IS Reports - IDB Reports],
           [IS Reports - Share Point Upload],
           [IS Reports - PC Dispute Log],
           [IS Reports - Bell Volume Master],
           [IS Reports - IS Volume Master],
           [IS Reports - Shared Drive Work Area Changes],
           [IS Reports - IS Recommendations - Corporate],
           [IS Reports - IS Recommendations - Investment],
           [IS Reports - AT&T Loose Invoice],
           [IS Reports - IS Recommendation - Investment],
           [IS Reports - Telecom Volume Master],
           [IS Reports - Issue Log],
           [RFS Request for Access - UPDating RFS List],
           [RFS Request for Access - ACL Proxy],
           [RFS Request for Access - Posting / Updating RFS Database],
           [Customer Service - Lotus Notes Disputes],
           [Customer Service - Personal Computer Disputes],
           [Customer Service - Personal Storage Disputes],
           [Customer Service - Ergonomic Upgrades],
           [Customer Service - Black Berry Disputes],
           [Customer Service - Shared Drive Disputes],
           [Customer Service - LN Database Disputes],
           [Customer Service - Request for Information],
           [Customer Service - Voip Dispute],
           [Executive Summary Reports - IBM Executive Summary],
           [Executive Summary Reports - Bell Executive Summary],
           [Executive Summary Reports - Monthly Severity 1 & 2 Chart],
           [Executive Summary Reports - Quarterly Tracking & Analysis - Severity 1],
           [Executive Summary Reports - IBM Dashboard],
           [Executive Summary Reports - Bell Dashboard],
           [Executive Summary Reports - Status Report],
           [0],
           [01],
           [02],
           [03],
           [04],
           [05],
           [Days Present],
           [Vacation Leave],
           [Sick Leave],
           [Overtime / (Undertime)],
           [06],
           [07],
           [08],
           [09],
           [010],
           [011],
           [012],
           Management,
           [Quality Checking],
           Training,
           Meeting,
           Coaching,
           Lockout,
           [System Down Time],
           [Idle Time],
           [Report Preparation],
           [Total Work Time],
           [013],
           [014],
           [Total SPT],
           [015],
           [016],
           [017],
           [018],
           [019],
           [020],
           [021],
           [022],
           [023],
           [024],
           [025],
           [026],
           [027],
           [028],
           [029],
           [Number of Items Failed - TOTAL - 1P],
           [Number of Items Checked - TOTAL - 1P],
           [030],
           [031],
           [032],
           [033],
           [034],
           [035],
           [036],
           TeamID,
           [Employee Number],
           Operations,
           Operations1,
           [Technology / Systems],
           [Projects / Implementations],
           Metrics,
           People,
           [HC Remarks],
           [Volume Remarks],
           [Capacity Remarks],
           [Number of Items Failed - TOTAL - 2P],
           [Number of Items Checked - TOTAL - 2P],
           [Number of Items Beyond Standard],
           [Number of Items Within Standard]
      FROM
      [CDN IS FIN 201108.qvd]
      (qvd);

       

      then I just add the script below for Macro.

       

      sub Export_Excel
                                     

          set obj = ActiveDocument.GetSheetObject("TB01") 
         
          obj.ExportBiff "C:\Documents and Settings\ramosros\My Documents"&Date()&".xls"
         
          MsgBox("Data Has Been Transfer into Excel in C:\Documents and Settings\ramosros\My Documents")

          End Sub

       

       

      Please advise.

       

      Thanks in advance!

       

      Salie

        • Re: QVD to Excel
          Angus Monro

          I'm guessing that either your QVD isn't in the same directory as your

          QVW; or you've misspelled the QVD's name.

           

          As an aside, if you're merely wanting to export in a form that Excel can

          read (rather than export in Excel workbook format), then you could just

          use

              STORE TB01 INTO destination.csv (txt);

            • QVD to Excel

              Hi Angus,

               

              Thanks for that. I placed the STORE TB01 INTO destination.csv in the last part of my script. But this is the content of the excel file:

               

              <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
              <QvdTableHeader>
                 <QvBuildNo>7440</QvBuildNo>
                 <CreatorDoc></CreatorDoc>
                 <CreateUtcTime>2011-09-21 04:26:44</CreateUtcTime>
                 <SourceCreateUtcTime></SourceCreateUtcTime>
                 <SourceFileUtcTime></SourceFileUtcTime>
                 <SourceFileSize>-1</SourceFileSize>
                 <StaleUtcTime></StaleUtcTime>
                 <TableName>TB01</TableName>
                 <Fields>
                   <QvdFieldHeader>
                     <FieldName>MetricIndex</FieldName>
                     <BitOffset>39</BitOffset>
                     <BitWidth>5</BitWidth>
                     <Bias>0</Bias>
                     <NumberFormat>
                       <Type>0</Type>
                       <nDec>0</nDec>
                       <UseThou>0</UseThou>
                       <Fmt></Fmt>
                       <Dec></Dec>
                       <Thou></Thou>
                     </NumberFormat>

              .

              .

              .

              .

              .

               

              I know I did the wrong thing..

               

              Or should I have a new software that will automatically convert all may QVDs to excel without opening and reloading it first in Qlikview?

               

              Thanks,

              Salie

                • QVD to Excel

                  Hi Angus,

                   

                  Thanks for that. I placed the STORE TB01 INTO destination.csv in the last part of my script. But this is the content of the excel file:

                   

                  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
                  <QvdTableHeader>
                     <QvBuildNo>7440</QvBuildNo>
                     <CreatorDoc></CreatorDoc>
                     <CreateUtcTime>2011-09-21 04:26:44</CreateUtcTime>
                     <SourceCreateUtcTime></SourceCreateUtcTime>
                     <SourceFileUtcTime></SourceFileUtcTime>
                     <SourceFileSize>-1</SourceFileSize>
                     <StaleUtcTime></StaleUtcTime>
                     <TableName>TB01</TableName>
                     <Fields>
                       <QvdFieldHeader>
                         <FieldName>MetricIndex</FieldName>
                         <BitOffset>39</BitOffset>
                         <BitWidth>5</BitWidth>
                         <Bias>0</Bias>
                         <NumberFormat>
                           <Type>0</Type>
                           <nDec>0</nDec>
                           <UseThou>0</UseThou>
                           <Fmt></Fmt>
                           <Dec></Dec>
                           <Thou></Thou>
                         </NumberFormat>

                  .

                  .

                  .

                  .

                  .

                   

                  I know I did the wrong thing..

                   

                  Or should I have a new software that will automatically convert all may QVDs to excel without opening and reloading it first in Qlikview?

                   

                  Thanks,

                  Salie