topic Re: Reading Excel from Sharepoint Issue in QlikView

Reading Excel from Sharepoint Issue

tom2qlik — Fri, 11 Aug 2017 12:02:30 GMT

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

Re: Reading Excel from Sharepoint Issue

Anonymous — Sat, 12 Aug 2017 08:01:59 GMT

could you upload some sample files for testing please?

Re: Reading Excel from Sharepoint Issue

tom2qlik — Wed, 06 Sep 2017 15:25:22 GMT

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$)

(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?

Re: Reading Excel from Sharepoint Issue

dmac1971 — Wed, 06 Sep 2017 15:51:03 GMT

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?

Re: Reading Excel from Sharepoint Issue

tom2qlik — Wed, 06 Sep 2017 15:59:15 GMT

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

Cheers!

Re: Reading Excel from Sharepoint Issue

tom2qlik — Fri, 08 Sep 2017 14:29:37 GMT

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

Re: Reading Excel from Sharepoint Issue

dmac1971 — Fri, 08 Sep 2017 15:45:32 GMT

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

Re: Reading Excel from Sharepoint Issue

tom2qlik — Mon, 11 Sep 2017 08:06:33 GMT

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

Re: Reading Excel from Sharepoint Issue

dmac1971 — Mon, 11 Sep 2017 08:50:27 GMT

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.

Re: Reading Excel from Sharepoint Issue

tom2qlik — Fri, 15 Sep 2017 08:34:15 GMT

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