Using QlikView to Look at a Big Text File

This week I was loading data from a very large text file into a database and had some problems with some of the records. I needed to look at the group of records in the file that was causing the problem but the file was big, over 12 million records, too big for Notepad or Wordpad. I decided to load it into QlikView – not the most elegant use for QlikView but setting it up was fast and it worked out better than I hoped.

I put these lines into the loadscript:

BIGFILE:
Load RecNo() as recordno,
Len(@1) as recordlen,
@1 as RECORD
FROM E:\Data\bigtextfile.dat (ansi, txt, delimiter is '`', no labels)
where RecNo()<1000000;>

That RecNo() function in the Where clause limited the load to the first million records. I tried loading all 12 million rows but on my laptop with 2GB of memory the loaded report was pretty sluggish. I defined the backtick character as a delimiter even though it doesn’t appear in the data so I could load the entire record by calling it field @1.

The main tab on the report itself was a table box for all three fields and list boxes for each of the three fields. Selecting by recordno let me look at specific sections of the file. The list box for RECORD was very useful – all I had to do was type a few characters of the data I was looking for and hit the Enter key to select it and QlikView quickly showed me what I needed from the file.

It worked so well that I added a browse-for-the-file macro and changed the load statement to use variables that are defined with input boxes on the report to make it a useful, general purpose report for whenever I need to examine a text file. An example of this document, named BIG_Text_File.qvw, is available here – http://finmagic.0catch.com/ Sorry about the ads and popups – just click on the specific file download.