Re: Reading Excel from Sharepoint Issue - Qlik Community

tom2qlik · ‎2017-08-11

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

Anonymous · ‎2017-08-12

could you upload some sample files for testing please?

tom2qlik · ‎2017-09-06

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 · ‎2017-09-06

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 · ‎2017-09-06

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

Cheers!

tom2qlik · ‎2017-09-08

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 · ‎2017-09-08

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 · ‎2017-09-11

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 · ‎2017-09-11

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 :

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 · ‎2017-09-15

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