Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
tom2qlik
Contributor

Reading Excel from Sharepoint Issue

Hi Everyone,

I am loading 2 excels into my qlikview app located on sharepoint.  The problem I am having is Qlikview appears to read them differently even though they are the same format/layout in excel.  Both are saved as Excel 97-2003 Workbook.

Below is a screenshot of the two excel files and the worksheets I'm loading into Qlikview.   The first excel on the left I'll refer to as excel 3, the one on the right I'll call excel 5.

When I load these into qlikview, I go to the script > Data > Web Files.  I paste the sharepoint location into the Internet File address bar and hit next.  This is where the problem lies.

Excel 3                                                                                Excel 5

You can see that the headers (Product / ID) for Excel 3 already appear at the top without changing the header lines even though in the excel they are on row 3.

Excel 5 is loaded how I would expect.  The headers and on row 3 in Qlikview like they are on row 3 in Excel.  For this to move the headers to row 1 I would change header size to "Lines" and enter 2 into the box next to it.

My question is why is Excel 3 being loaded differently than Excel 5 when both appear to be exactly the same.

Thanks,

Tom

9 Replies
robin_hausdoerfer
Valued Contributor III

Re: Reading Excel from Sharepoint Issue

could you upload some sample files for testing please?

tom2qlik
Contributor

Re: Reading Excel from Sharepoint Issue

One thing I didn't point out is these excel file get replaced daily on sharepoint with a new file.  The format is the same as the previous day with only the file name changing to the current date (filename_YYYYMMDD).  To combat this I'm loading the file name with a variable like:

[http://sharepoint/sites/Qlikview/Filename_$(vAll).xls](biff, embedded labels, table is PBL$)

Next day the file gets replaced in the same format and qlikview gives the following error.

Field not found - <Product>

[http://sharepoint/sites/Qlikview/Filename_$(vAll).xls](biff, embedded labels, table is PBL$)

The fix find the field is to change the excel load from

(biff, embedded labels, table is PBL$)

to 

(biff, embedded labels, header is 2 lines, table is PBL$);

You can see how this is the problem in the original post.

I am saving today's excel file and tomorrows when I comes.  I will upload the two excels tomorrow.

Anyone have a theory?

dmac1971
Contributor II

Re: Reading Excel from Sharepoint Issue

Tom might be easier to open the excel file as an excel file, if you get my meaning.  Go to the file in SharePoint and there's an option to open the file location, then copy the path to the file and adjust your script accordingly allowing for the date string change each day?

tom2qlik
Contributor

Re: Reading Excel from Sharepoint Issue

Yea I get your meaning.  I'll test this on tomorrows rebuild.

Cheers!

tom2qlik
Contributor

Re: Reading Excel from Sharepoint Issue

Hi Dermot,

Unfortunately what I tried didn't work and I'm wondering did I do what you were suggesting correctly.

When you said there is an option to open the file location, I took this as opening the file location in the explorer and taking the explorer file location into the script.  I also incorporated the date change in the script.

Was this what you were suggesting?

Thanks,

Tom  

dmac1971
Contributor II

Re: Reading Excel from Sharepoint Issue

You can copy the link of the excel file from Sharepoint, ie its direct url.  Then in qlikview script editor point you select an excel file and then paste in the link.  Will pull some images together on Monday if you are still stuck.

Dermot

tom2qlik
Contributor

Re: Reading Excel from Sharepoint Issue

This is the address is was using by left clicking the file name, selecting properties and copy the address to the script.  This address is a direct link to open the file.  Unfortunately my problem still occurs with this address.

Thanks,

Tom

dmac1971
Contributor II

Re: Reading Excel from Sharepoint Issue

Hi Tom.  In sharepoint browse to the location the excel file is located, not sure if its the same for all but suspect it is, there should be a menu at the top, click on Library to see view below :

Tom Neal.JPG

Then just click open with Explorer to open the SharePoint folder in Windows Explorer.  Then Shift and Right Click on the actual file and you will get the path, just remove the quotes and you should be able to use in Qlikview, that's how I do it and it works fine.  Note that's within intranet but don't suspect its any different?

If still struggling email me and we can try and go further.  Thanks.

tom2qlik
Contributor

Re: Reading Excel from Sharepoint Issue

Hi Dermot,

Just to clarify is it the 'DavwwwRoot' file path I should try.

\\sharepoint\DavWWWRoot\.....\

I'm waiting for access to it from our support otherwise I would have tested it already.

All the 'http' filepaths I have found still cause the same issue.

Thanks,

Tom