load 2 excel sheets from Sharepoint

[Anonymous]

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)') ;

[marcus_sommer]

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

- Marcus

[tamilarasu]

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.

[Anonymous · Accepted solution]

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