topic Re: MSQ format truncates data while loading from delimited text file in QlikView

MSQ format truncates data while loading from delimited text file

Sat, 18 Jul 2015 03:27:28 GMT

Hi,

I am reading data from Semicolon delimited Text file in Qlikview.

For first time seen an issue where text file has 1000 records but qlikview read only 300 records.

By default MSQ quoting is been used.

Semicolon delimiter is used.

Could see there are few columns specifically in this text file alone that has semicolon in its value as well.

Just tried reading same file using Standard format with again semicolon delimiter and could see all 1000 records have been read successfully.

Any reason why MSQ quoting dint work here. I got a requirement to read around 15 different text files. In this case which one is better to use whether MSQ quoting or standard one.

Thanks in advance.

Thanks,

Avin

Re: MSQ format truncates data while loading from delimited text file

marcus_sommer — Sat, 18 Jul 2015 05:13:09 GMT

It will be depend from the datastructure in those files if it's adviseable to use msq or not. In most cases it's not necessary to use it - I personal load each txt-file without msq.

I think you should simply try it with the different options and add a recno() and rowno() to your loads to make sure that all records are loaded and if not you could see which one is (why) missing.

- Marcus

Re: MSQ format truncates data while loading from delimited text file

Sun, 19 Jul 2015 04:39:55 GMT

Thanks Marcus. However trying to understand any use case or specific example why we would go for msq format and why data is truncated while using msq.

Thanks,

Avin

Re: MSQ format truncates data while loading from delimited text file

marcus_sommer — Sun, 19 Jul 2015 08:11:36 GMT

MSQ is only an extended option to handle data-structures from txt/csv-files whose field-values are wrapped with some kind of delimiter which should avoid that other chars within them will be interpreted as row-delimieter. If you applied msq the data-structure and your file-format settings needs to fit excactly - if they don't fit it could lead to unpredictable results (read in help).

If you don't have such field-wrapping use no quotes instead of msq and my suggestions from above with recono/rowno to find the reasons. Further if the data-structure from your files is bad then you might need several loadings to get your data maybe at first something like:

LOAD repalce(@1:n, 'x', 'y') FROM temp.txt (fix, codepage is 1252);

which is then the source for your main-load.

Helpful could be if you tried to load your data with the table-wizard. There are many options available to handle unstructured data and it have a pre-view function. Unfortunately is the wizard not well documented but in this book (is generally recommended) you could find examples:

QlikView 11 for Developers

Barry Harmsen und Miguel Garcia

ISBN-13: 978-1849686068

- Marcus

Re: MSQ format truncates data while loading from delimited text file

Wed, 22 Jul 2015 02:51:16 GMT

Thanks Marcus for the detailed explanation.