topic Re: load 2 excel sheets from Sharepoint in QlikView

load 2 excel sheets from Sharepoint

Anonymous — Tue, 15 Dec 2015 20:13:26 GMT

hi ,

i want to load data from 2 excel sheets names below.

1.2014 US REMS Audit Workbook DOL

2.2015 US REMS Audit Workbook DOL

Tab Names:

2014 Audit stats Tracking

2015 Audit stats Tracking

local its working fine but it fails to load from sharepoint path

sharepoint path:http://xxxx.com/documents......

sub ScanFolder(Root)

for each FileExtension in 'xlsx'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

Let vFileName = subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1);

if (Wildmatch(vFileName, '*US REMS Audit Workbook DOL*')) then

Let vYear = Left(vFileName,4);

Let vSheetName = vYear & ' Audit stats Tracking';

Audit_Stats_Tracking:

LOAD

[Audit Date],

[Pharmacy ID],

[Pharmacy Name],

Address,

City,

State,

PharmacyTYpe as [Pharmacy Type],

LeadAuditor as [Lead Auditor],

AuditType as [Audit Type],

AccountManagerName as [Account Manager Name],

MAAccountDirector as [MA Account Director],

[Product Audited],

[Overall Audit Result],

[Observation Yes or No],

[Repeat Observations],

[Tier 1],

[Tier 2],

[Number of Counselors],

[Date Audit Report Issued],

[Date Response Received],

//[Date Certificate sent],

[Effectiveness Check],

Comments,

[Observation Rating],

subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1) as FileName

FROM [$(FoundFile)] (ooxml, embedded labels, table is '$(vSheetName)');

End If

next FoundFile

next FileExtension

end sub

Call ScanFolder('$(vFilePath)') ;

Re: load 2 excel sheets from Sharepoint

marcus_sommer — Wed, 16 Dec 2015 00:54:50 GMT

Have a look here Loading multiple Excel files from SharePoint on the answer from jontydkpi

- Marcus

Re: load 2 excel sheets from Sharepoint

tamilarasu — Wed, 16 Dec 2015 01:41:28 GMT

Hi Are Babu,

Change the Call ScanFolder line like below and try to reload the script.

Call ScanFolder('htttp://sharepointlink.com/Documents') ;

Let me know.

Re: load 2 excel sheets from Sharepoint

Anonymous — Wed, 16 Dec 2015 10:32:51 GMT

Hi tamil Nagraj,Marcus Summer,

Thanks for Your Time.

As far as I know, the http and https protocols do not support file iteration using wildcard loads or FileList() loops. For loading data with ese protocols you will need to explicitly name the files.-By Jonathan dienst.

so i made some code change to read the Correct file from Share point

Here is the Code i modified.

set vFilePath ='http://teamsites..com/sites/Documents';

sub ScanFolder(Root)

for Vcurrent= 2014 to Year(today())+1

for each FileExtension in 'xlsx'

for each FoundFile in vFilePath&'\'&$(Vcurrent)&' US REMS Audit Workbook DOL'&'.'& FileExtension

Let vFileName = subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1);

if (Wildmatch(vFileName, '*US REMS Audit Workbook DOL*')) then

Let vYear = Left(vFileName,4);

Let vSheetName = vYear & ' Audit stats Tracking';

Audit_Stats_Tracking:

LOAD

[Audit Date],

[Pharmacy ID],

[Pharmacy Name],

Address,

City,

State,

PharmacyTYpe as [Pharmacy Type],

LeadAuditor as [Lead Auditor],

AuditType as [Audit Type],

AccountManagerName as [Account Manager Name],

MAAccountDirector as [MA Account Director],

[Product Audited],

[Overall Audit Result],

[Observation Yes or No],

[Repeat Observations],

[Tier 1],

[Tier 2],

[Number of Counselors],

[Date Audit Report Issued],

[Date Response Received],

//[Date Certificate sent],

[Effectiveness Check],

Comments,

[Observation Rating],

subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1) as FileName

FROM [$(FoundFile)] (ooxml, embedded labels, table is '$(vSheetName)');

End If

next FoundFile

next FileExtension

next Vcurrent

end sub

Call ScanFolder('$(vFilePath)') ;

its worked fine and iam able to read from Share point

Thanks,

Are.Babu