Monday, June 27, 2011

Export All Data From All QV Tables to Text Files

Last week one of our clients asked me to provide a method that they could use to export all of the data from within a QlikView document. They had hundreds of archived documents and they wanted to be able to export the data and use it in another application.
“Sure, No problem. I’ll email you some instructions.” I sent them instructions for exporting all of the data from all QlikView tables within a document into tab-delimited text files. This is what I sent to them:

1. Create a new folder on your laptop or server. The computer where you do this must have QlikView installed.

2. Copy the QlikView .qvw document file into your folder. This will make sure you don't accidently update the original document.

3. Double-click on the .qvw file to start up QlikView.

4. From the menu, select File->Edit Script. This opens up the Loadscript Edit Window.

5. At the very top of the loadscript, before the first line, copy and paste in these lines:

/* Export all QlikView tables to tab-delimited files */
if IsPartialReload() then
for ix= 0 to 100
let tn=trim(tablename($(ix)));
if len('$(tn)')<1 then
exit script;
end if
Store "$(tn)" into "EXPORTED_QV_$(tn).TXT" (txt, delimiter is "\t");
next ix
end if
exit script;

6. Close the Loadscript Edit Window by clicking the OK button in the lower right corner of the window.

7. From the menu, select File->Partial Reload

8. You don't need to save the document, but if you do save it, do not copy it back to the original location.

If everything works correctly, each of the QlikView tables will be written out as tab-delimited text files into the folder. For example, if the QlikView document contains a QlikView table named HISTCUST then it would write out a text file named, EXPORTED_QV_HISTCUST.txt. The first record will contain the field names.

Note the syntax for specifying the delimiter character. If your Store command simply includes (txt) without specifying the delimiter you will get comma-delimited text files. Use the file specification, (qvd), to get QlikView QVD files.

I would've preferred a more elegant piece of code using a Do While loop but I had trouble getting the test to run correctly and I had limited time. The code above should work fine for documents with under 100 QlikView tables.

* * *