Write To CSV With QlikView STORE

Since launching the AskQV site I have found myself using it as my first port of call when reminding myself of what someone had put in their blog post a while back. This has however meant that I have spotted a couple of items that no one has blogged on before. This post aims to rectify that for the use of the STORE statement to write a CSV.

More In STORE

Most QlikView developers will use the STORE statement frequently for writing QVD files during load scripts. If you don’t do this then you should probably read this previous post on the topic. What many people don’t realise though is that there other values that can appear in the brackets at the end of the statement, to define the format of the output.

QlikView STORE

CSV Files – Not Just For Reading In

Generally text files are things that we interface with for loading data in from other systems – perhaps after an automated extract. Sometimes, however, there is cause to send data from the data sources that you are bringing into QlikView into other systems – after crunching them in your load script. Perhaps these data are pushed externally via FTP or loaded via SSIS into a database. Often on Qlik Community there are people asking around how exports can be achieved by using macros on the front end in order to send the content of a Table to a text file. This can be necessary when the expressions in the Table need to be evaluated before export, or the users selection at the time of export is important. Often though the desired effect can be achieved in the load script without user interaction or potentially problematic macros (see this post by Henric Cronstrom for more on that topic).

The STORE Statement

As you will be aware, if you use STORE for writing QVDs, the STORE statement writes the contents of a single table in memory to a file during load script execution. Any required calculations need to be done during the LOAD statement, and if any of those calculations are aggreations then the load must include a GROUP BY statement. The syntax is very similar for CSV output:


STORE MyTable INTO ..\Ouptut\MyCSVFile.csv (txt);

As with when the statement is used to store to a QVD file; it is possible to specify only certain fields are written to the output file:


STORE Name, Address1, Address2, PostCode FROM MyTable
INTO ..\Ouptut\MyCSVFile.csv (txt);

Unlike the QVD store, there are some optional parameters that can be given to further define the format of the file, such as:


STORE MyTable INTO ..\Ouptut\MyOutputFile.txt (delimiter is \t);

This will give you a tab delimited file, rather than a comma separated file (note that the extension of the file has no bearing on this, only the format specifier). There are other format specifiers that you can find in the help file or on Qlik Community.

Publishing to a Web Page with STORE

A neat little trick I have used on a few occaisions is to push output to a web page using the STORE statement. This can be simply done by writing out a HTML file during the load script. To see this in action simply try this in a QlikView load script:


MySource:
LOAD
	RowNo() as Row,
	Rand() * 1000 as Value
AUTOGENERATE(10)
;

HTMLOutput:
LOAD
	[<html>]
INLINE [
<html>
<head>
<title>HTML Output</title>
</head>
<body>
<h1>HTML Output Example</h1>
<table border=1 cellpadding=5 cellspacing=0>
<tr><td><b>Row Number</b></td><td align=right><b>Value</b></td></tr>
];

CONCATENATE(HTMLOutput) LOAD
	'<tr><td>Row ' & Row & '</td><td align=right>' &
	replace(num(Value, '#,##0.00'), ',', '-') & 
	'</td></tr>' as [<html>]
RESIDENT MySource;

CONCATENATE(HTMLOutput) LOAD
	[<html>]
INLINE [
<html>
</table>
</body>
</html>
];

DROP TABLE MySource;

STORE HTMLOutput INTO .\MyOutputFile.html (txt);

DROP TABLE HTMLOutput;

What you will get is an HTML file that you could target at a web server to allow users to view values in a browser without needing to go via AccessPoint. Whilst the output from this example does not look particularly elegant you could incorporate a style sheet in the header to make the output far more attractive.

You could even go as far as using DIV blocks of colour with the dimensions of those blocks driven by the data to build a fully HTML based bar chart for inclusion on your site. I have used it to write to a wall board that refreshed every five minutes giving stats on the status of QlikView and details of any error conditions.

With the STORE statement in your QlikView kit bag, and a bit of inspiration, many things can be achieved.

By |2016-06-25T01:37:46+00:00May 20th, 2014|QlikView Tutorial|40 Comments

About the Author:

Steve is owner and principal consultant at Quick Intelligence. He is a Qlik Luminary, Qlik Community MVP and Technical Editor of a number of QlikView Books.

40 Comments

  1. Amir.T May 20, 2014 at 4:54 am - Reply

    Nice.
    I will try it.

    Thanks
    Amir.T

    • Steve Dark May 20, 2014 at 6:25 am - Reply

      Thanks, Amir.

  2. Barry May 20, 2014 at 7:14 am - Reply

    Nice. I often use the CSV export, but hadn’t thought about using it to write to an HTML page. I will definitely find a use for this!

    • Steve Dark May 20, 2014 at 7:44 am - Reply

      Hi Barry. When I do this I tend to have the HTML structure in a spreadsheet, then join to the data values and use replace to mash them together. It works well this way.

  3. Steve Dark May 28, 2014 at 9:19 pm - Reply

    Just to add to this… another technique you can employ is writing a batch file with parameters derived in the load script, and then execute this with a Call statement.

  4. Brian May 29, 2014 at 2:47 pm - Reply

    Wow, the HTML part is a great idea! Do you have an example of using Excel to store the HTML and the .qvw that brings it in?

    • Steve Dark May 29, 2014 at 4:56 pm - Reply

      I will send this directly to you. It needs some tidying before I publish it on the blog. Thanks for the comment.

  5. Brian May 29, 2014 at 3:49 pm - Reply

    I was able to write out an HTML file, “store” it to a SharePoint document library, then display it on a SharePoint page using the Page Viewer web part. Took just a few minutes thanks to this great tip!

    • Steve Dark May 29, 2014 at 4:57 pm - Reply

      Nice!

  6. Brian May 29, 2014 at 5:01 pm - Reply

    This may also work using HTML5 to draw graphs. Of course, QV Web Parts would be the ideal way to do it but the cost wouldn’t be justified for just a simple requirement. I may have to give this a shot when I get some time.

    http://www.worldwidewhat.net/2011/06/draw-a-line-graph-using-html5-canvas/

    • Steve Dark May 29, 2014 at 5:12 pm - Reply

      It doesn’t even need to be HTML 5. You can build a bar chart using DIV and by setting the width in the style of each DIV. I feel a part two of this post coming on…

  7. Harini Padmanabhan September 24, 2014 at 10:01 am - Reply

    Hi Steve,

    I am trying to get qlikview to over write data into excel. The exact problem is as follows. I have a straight table in qlikview based on some numbers from excel. If the data is off in a cell, I want to edit it and get the edit to reflect in the back end excel also. Is it possible? Any hints as to what can help me do this?

    • Steve Dark September 24, 2014 at 12:48 pm - Reply

      You could read up on INPUT fields in QlikView, but in my experience these can be unreliable. As a rule of thumb is it best to assume that QlikView can not be used to capture input and write back to a file or database.

      • Brian Garland (@briankgarland) September 24, 2014 at 3:19 pm - Reply

        If you have the in-house resources, you can someone build a web form that is a frontend to the file (or, preferably, a database table) then use hyperlinks in your QV UI that open a browser window and passes the key value as a parameter in the URL.

        • Steve Dark September 24, 2014 at 3:27 pm - Reply

          We like Google Spreadsheets for that kind of thing. You can then read from these either by publishing as a CSV file (using public sharing in Google Sheets) or by using QV Source. If you have sensitive data then the latter is the best way, it’s also more robust.

  8. Ryuma Nakano September 24, 2014 at 1:02 pm - Reply

    Great post Steve,
    I have used the (txt) store before, but I had never thought about using it as an HTML structure, that blew my mind.
    Thanks for your teaching, keep doing it :D

    • Steve Dark September 24, 2014 at 3:24 pm - Reply

      Thanks Ryuma – it’s amazing what you can get QlikView to do when you think outside of the box a bit. I certainly intend to keep sharing these nuggets!

  9. chrisb October 2, 2014 at 7:48 am - Reply

    Nice post Steve.

    We have been making use of the store … (txt) command for some time in QVSource and the related starter apps (https://github.com/QVSource).

    It allows us to perform some tasks much more rapidly. For example, you can grab say 1000 Tweets using our Twitter Connector, then save them to a CSV file and then point the Text Analytics Connector to this file which will then process them (e.g. score them for sentiment) either in a number of large batches (if the API supports it) or at least on a number of parallel threads. All this happening while the load script runs.

    There’s a lot more detail on this here:
    http://wiki.qvsource.com/Synchronous-Asynchronous-And-Batch-Requests-Explained.ashx

    • Steve Dark October 2, 2014 at 8:12 am - Reply

      Thanks Chris – always nice to hear how other people are using the techniques we promote through the blog.

  10. dasetwas November 28, 2014 at 11:49 am - Reply

    Hi Steve

    Have you ever tried to get that csv in Matlab?

    Im having Problems with that.

    Greetings
    Julian

    • Steve Dark November 28, 2014 at 9:02 pm - Reply

      Hi Julian – I’ve not tried opening the file in Matlab – but it will just be a standard text file. There are a few oddities with it, such as how it handles quotes and commas. For instance, to get the HTML output to work correctly I had to omit all quote marks. You can do a bit with the format string (in the brackets with the txt) but it is still temperamental.

      The best bet is to open in a text editor and see what QlikView has actually put out and see how you can work with that.

  11. Yaron December 4, 2014 at 9:50 am - Reply

    Hi Steve,
    I need to store into .csv (txt, delimiter is ‘,’) but the headers and the values need to be surrounded with ”

    for example :
    “first_name”,”last name”
    “Charles”,”Bronson”

    is it possible to add a parameter to the store to capture this requirment?

    thanks in advance,
    Yaron.

    • Steve Dark December 9, 2014 at 9:54 pm - Reply

      Not as far as I am aware. Double quotes will be added when required, as per the CSV file specification – that is where there is a comma or quotation in the field itself.

      To be specific about file formats you can build a string into a single field and export that – which can be fiddly, or use a tool like NPrinting

  12. Mike March 27, 2015 at 3:17 am - Reply

    Hi Steve,

    Does QlikView or Qlik Sense offer Out of the box Write-back functionality from a dashboard or report?

    • Steve Dark March 27, 2015 at 9:35 am - Reply

      Hi Mike, neither product will write back natively, but both support extensions and QlikView support Macros, so there are possibilities for some level of write back. As a rule I try to keep data update and data presentation separate, so don’t attempt these kind of write-backs from Qlik.

  13. Mike March 27, 2015 at 4:21 pm - Reply

    Another question, Can you discuss Qlik’s Out of the box capability regarding Proactive event-based or threshold-based reporting. I understand time based proactive reporting is supported, but is even/threshold based supported out of the box?

    Thank you in advance.

    • Steve Dark March 28, 2015 at 3:06 pm - Reply

      Hi Mike, yes, Alerts in QlikView are very flexible and powerful. You can fire events based on an expression being met (i.e. a threshold breached) and then include data in the body of the mail, using the CONCAT function for multiple rows. This is perhaps something I should blog on soon…

      For more powerful threshold based pro-active reporting, particularly with regards to more attractive output, you should take a look at Qlik NPrinting. With this tool you can distribute PDFs or spreadsheets when a trigger condition is met.

  14. Mike March 28, 2015 at 2:57 pm - Reply

    Steve,

    Do you know if QlikView Mobile supports AirWatch? Airwatch is our companies MDM solution.

    http://www.air-watch.com/?cid=70150000000p2jC

    Thanks,
    Mike

    • Steve Dark March 28, 2015 at 3:08 pm - Reply

      Most of QlikView’s functionality is delivered using HTML 5 in the browser. As long as AirWatch does not impede the browsers functionality you should be fine. There is an iOS app that gives some offline capability on mobile devices, but the offline functionality is limited and I don’t know of anyone using this app. The browser delivers all you need.

  15. Mike March 29, 2015 at 2:52 pm - Reply

    Hi Steve,

    I am looking to take pictures and leverage GPS from within the HTML5 Mobile app. Is this possible with QlikView mobile?

    Secondly, with an extension, is Mobile write back available in the HTML5 app?

    Lastly, if I am using the ios mobile app, and in offline mode and interacting with bookmarked reports, does the analysis done in offline mode get saved when i come back online?

    You have been a huge help, thanks very much!!

    Mike

  16. Mir Asif Ali September 24, 2015 at 2:43 pm - Reply

    Hi Steve,

    I read your blogs, and they are amazing. I am looking for two things

    1) Export qlikview reports to google sheets.
    2) Auto-Sizing the dashboard based on the end-user device resolution.

    Can you help me on this.

    Regards,
    Asif.

    • Steve Dark September 25, 2015 at 11:12 am - Reply

      Hi, thanks for your comment. As far as I am aware there is no way to write back to Google Sheets from QlikView. In the main it is best to assume QlikView can not write back anything. There may be ways using macros or extensions, but these methods can be fiddly and/or unreliable.

      Regarding sizing, you are tied to what the browser will allow. If a responsive UI is important to you then you may want to spend some time looking at Qlik Sense.

  17. Andrew Walker October 1, 2015 at 9:04 am - Reply

    After selecting the dataset you need in QV, STORE is a great way to create a csv in the format you need to be picked up in R for analysis. For instance in a sales application you can select customers, product range, etc then STORE a csv for R to pick up and provide analysis of seasonal variation. I usually have the STORE command in the partial reload section of the script and with a button action to open R it takes no time to get the analysis.

    cheers

    Andrew

    • Steve Dark October 8, 2015 at 5:26 pm - Reply

      Thanks Andrew for sharing this use case.

  18. Ravi Teja M October 14, 2015 at 4:27 pm - Reply

    Hi Steve,

    Did you find a way to avoid Double Quotes when you store data as HTML file (.html).

    As you mentioned in this post (https://community.qlik.com/message/451605#451605), the issues only occur when I have a comma or quote symbol in the data.

    Cheers
    Teja

    • Steve Dark October 14, 2015 at 6:54 pm - Reply

      Hi Teja,

      No, unfortunately I had to leave it with slightly non-compliant HTML. It works a treat though.

  19. Snowball December 21, 2015 at 6:38 am - Reply

    You really saved my day!!!

    • Steve Dark December 21, 2015 at 7:08 am - Reply

      That’s really good to hear – many thanks for the comment.

  20. Anas Abbasi January 12, 2017 at 8:02 am - Reply

    HI Steve Dark
    I was wondering, is there a way we can store the table file in XLSX (excel) format, I have been trying to do that to create UDC in Qlik Sense. Please help me if there is a way. I have tried doing it in .csv but its not working.

    • Steve Dark January 12, 2017 at 8:42 am - Reply

      Hi. Thanks for your comment. Unfortunately you can’t write to an Excel file with the STORE statement. If you have QlikView you could create a straight table with the data in, and then export to Excel using a macro on an open trigger. You could have an Excel macro, or linked query, to the CSV file. If you have other requirements for NPrinting, this will make light work if it – but it’s cost prohibitive for just this purpose.

      Beyond that I would suggest emailing Qlik petitioning for UDCs to be able to load from CSV. This would seem to be a sensible feature.

      Hope that helps.

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.