topic Re: I want to load the FORMULAE from Excel cells not the values in QlikView

I want to load the FORMULAE from Excel cells not the values

sarahallen1 — Fri, 29 Nov 2013 14:17:19 GMT

Is this possible? I want to define fields etc in my LOAD statement as usual, but for the field values I want to read in the formula in each Excel cell, rather than the value.

Any ideas would be very welcome!

Thanks.

Re: I want to load the FORMULAE from Excel cells not the values

ngulliver — Fri, 29 Nov 2013 15:21:48 GMT

Hi, Sarah.

If I have misunderstood your question, my apologies.

What would you be wanting to do with these formula once they are in QlikView ? Are you hoping to continue to use them to derive values from the other source fields ? In which case, this is not possible.

If you are simply wanting to look at the formula as a text field, what you can do is a mass replace in Excel of '=' with '' which will reveal all formula as text before importing it as field values

Regards,

Neil

Re: I want to load the FORMULAE from Excel cells not the values

sarahallen1 — Fri, 29 Nov 2013 15:40:03 GMT

Hi Neil,

Thanks very much for your reply, it's appreciated.

I don't want to reuse the formulae in QV to derive values - I should have clarified this, so thanks for raising it.

What I'm trying to do is use QV to audit the formulae in a series of about 450 spreadsheets (each spreadsheet corresponds to a stock). At the moment I have a very nice QV system that reads in the values from all of these spreadsheets. But a little data quirk has highlighted that one particular metric is calculated differently for different stocks - i.e. the formula is different in a given cell for different spreadsheets. I want to know which stocks use each calculation method.

So unfortunately I don't think a mass replace within Excel will work as I'd have to do it within each of the 450 spreadsheets (also some formulae are quite complicated in Excel with nested IFs etc so might have more than one = sign in the formula, not just the one at the start).

Thanks for your help though!

Any other ideas much appreciated.

Sarah

Re: I want to load the FORMULAE from Excel cells not the values

sarahallen1 — Thu, 14 Aug 2014 14:11:35 GMT

Does anybody know of a potential solution here?

Re: I want to load the FORMULAE from Excel cells not the values

Thu, 14 Aug 2014 14:26:54 GMT

Hi Sarah,

We do it in our models but you will have to have the formula enclosed in these "" to prevent the formula from actualy calculating.

You can then import the excel spreadsheet into your QlikView model but I guess enclosing all of the expressions in excel with "" will be to much of a cumbersome task for you.

Regards,
Marius

Re: I want to load the FORMULAE from Excel cells not the values

sarahallen1 — Thu, 14 Aug 2014 14:29:27 GMT

Thanks Marius. Yes as you say, unfortunately it's not an option for us to make changes to the Excel spreadsheets (too many of them).

Does anybody know of a way that QlikView itself can directly read an Excel formula rather than an Excel formula's value?

Re: I want to load the FORMULAE from Excel cells not the values

Thu, 14 Aug 2014 14:55:20 GMT

Hi Sarah,

I have tried different ways of loading the data and it works perfectly if you have it enclosed with "" but can't get it to work the other way around.

Sorry.

Lets wait and see if theres not somebody out there that might know.

Regards,
Marius

Re: I want to load the FORMULAE from Excel cells not the values

jerem1234 — Thu, 14 Aug 2014 15:51:22 GMT

What you can try is:

Go into your excel files and click on "Show Formulas". Then save the excel file.

Then do a Save as for the same excel file, and save it as a text file(The formulas should stay intact and not be evaluated).

Then just load that text file into qlikview instead. The formulas should stay. Unfortunately, this requires a little work as well.

Hope this helps!

Re: I want to load the FORMULAE from Excel cells not the values

sarahallen1 — Thu, 14 Aug 2014 16:05:31 GMT

Thanks Jeremiah.

We've done this with some Excel/QV applications, and it works well - but the application I am asking about looks at over 500 different Excel spreadsheets, so I'm looking for a solution which doesn't require anything being changed at source. (I guess I could do a macro in Excel to convert cells to "show formulas" or put ' before the formulae - but I'd rather not have to resort to it as I want it to be a live analysis of the 500 Excel models that are being worked on).

What I'm looking for is something in the QV script which treats the Excel file differently (rather than biff or ooxml maybe). It may be that such a connector does not exist - but it would be nice if it did!

Re: I want to load the FORMULAE from Excel cells not the values

sarahallen1 — Wed, 09 Mar 2016 10:56:52 GMT

Has anybody else got any ideas on how to solve this?

I can't use a solution that requires me to do anything in Excel as there are hundred of spreadsheets - I use a QlikView *.xls type of load. I need to load in the formulae without changing the spreadsheets themselves (as this is dynamic so I want to monitor the formulae across all spreadsheets that get added to that folder too).

Any ideas on whether this is possible?

SpyrosD80‌

Re: I want to load the FORMULAE from Excel cells not the values

avinashelite — Wed, 09 Mar 2016 11:14:24 GMT

Hi Sarah,

unfortunately this is not possible in Qlikview ..you need to add a column using =FORMULATEXT(A1) and use this column in QV.

Re: I want to load the FORMULAE from Excel cells not the values

ravishankarqv — Wed, 09 Mar 2016 11:58:27 GMT

One idea that comes to my mind is writing VBS that take excel file path as input and save that with "show formulas" option selected into a new temporary file in CSV. Use this macro inside QlikView to create a temporary CSV file with exposed formula. Finally, load temporary file instead of original excel file

HTH

Ravi

Re: I want to load the FORMULAE from Excel cells not the values

petter — Wed, 09 Mar 2016 12:08:43 GMT

There is actually a way of getting the formulas in a spreadsheet. It is "cumbersome" and you will have to invest some time into implementing it. The prerequisite for what I am suggesting is that all the Excel files are OOXML ... the new Excel format that has XLSX-extension.

1) Iterate through all ~500 Excel-files

2) For each XLSX-file use the Unzip-routine I have suggested in a previous article.

How to extract Sheet Names from an Excel XLSX-file without ODBC

3) Then for each sheet that was extracted by UnZip - it will be an XML-file which contains both the value, formula and sheet reference for all cells.Read these into a table in QlikView with the necessary information.

Re: I want to load the FORMULAE from Excel cells not the values

ravishankarqv — Wed, 09 Mar 2016 13:19:54 GMT

In general there is an EXTENDED PROPERTY named Allow Formulas=True/False in OLE DB connection string that lets you access EXCEL formulas as text or evaluated values.

If you figure out how to do that in Connection String, your problem will be solved.

HTH

Ravi

Re: I want to load the FORMULAE from Excel cells not the values

sarahallen1 — Wed, 09 Mar 2016 13:57:28 GMT

Thanks Ravi I will have a go. I am not used to using macros so may have to find my way a bit! Thanks

Re: I want to load the FORMULAE from Excel cells not the values

tamilarasu — Wed, 09 Mar 2016 14:01:50 GMT

Hi Sarah,

I would suggest you to create an Excel vba to replace all the formulas (like "= ) by looping through the spreadsheets. By doing so, you need not to open each and every spreadsheets manually. Also you will not find any difficulties while uploading the spreadsheets into Qlikview.

Hope this helps you.

If you can't find an excel macro, let me know.

Re: I want to load the FORMULAE from Excel cells not the values

sarahallen1 — Wed, 09 Mar 2016 14:07:40 GMT

Thanks Petter, this looks promising.

I am not very familiar with macros so am finding my way through this a bit!

But I'm trying first of all just to do a single .XLSX sample file with step 2 (your unzip solution).

This may be a silly question, but does the VB script for the unzip function need to be in the QVW's Edit Module, or in the VB of the .XLSX file? I tried to put it in the XLSX but it wanted me to save as a XLSM as expected, then I tried to run the QVW with the XLSM file and it failed. I also tried to leave the XLSX module unchanged, and instead put it in QVW's Edit Module, but it still failed. Both times I get the error below.

Where am I going wrong?

Thanks

Re: I want to load the FORMULAE from Excel cells not the values

sarahallen1 — Wed, 09 Mar 2016 14:11:03 GMT

Thanks Tamil. It sounds like a good solution (ideally I don't want to have to create new files but if it's unavoidable then so be it!!). With this sort of solution, would it stay 'live'? The set of files changes regularly so the corresponding '= versions of each file would need to be refreshed each time the QVW loaded to check for the formulae.

Re: I want to load the FORMULAE from Excel cells not the values

petter — Wed, 09 Mar 2016 14:12:21 GMT

Hi Sarah,

The VBScript has to be in the QVW's Edit Module. I might have a go at it later this evening to see if I can put together a small sample app for you that illustrates how to do it completely.

Re: I want to load the FORMULAE from Excel cells not the values

sarahallen1 — Wed, 09 Mar 2016 14:12:37 GMT

This sounds great! How do I use the property? I've never done an OLE DB connection to an Excel file before.