topic QVD to Excel in QlikView

QVD to Excel

Wed, 21 Sep 2011 03:53:13 GMT

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

gussfish — Wed, 21 Sep 2011 04:18:54 GMT

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

Wed, 21 Sep 2011 04:30:20 GMT

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

Wed, 21 Sep 2011 07:32:39 GMT

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