Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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
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