load log file will stop at half

[Daora342423]

Hi, I have a problem, I have to load the lines of a log file but when I load it in the usual way, if in the file there is a field with "something" inside it qlik blocks loading to the previous line. Do you know if there is a system or 1 option to insert between those to eliminate the header or give it the type of separator? attached I send you what I did:

tabella:
LOAD @1 as Server_Started,
@2 as Timestamp,
@3 as Document,
@4 as Type,
@5 as User,
@6 as Message,
@7 as Id,
@8 as Session
FROM D:\Qlikview_Log\Audit_QLIK-NPRINTING_$(v_anno)-$(v_mese).log (txt, codepage is 1252, no labels, delimiter is ' ', msq, header is 1 lines);

Thx a lot

[Daora342423]

now that I reread it maybe it's not very understandable, I don't know what's in the quotation marks ("") so I would like to tell the program: 'if you find the quotation marks load it the same' or 'if you find the quotes, skip the line and keep loading

[marcus_sommer]

I'm not really sure if I understand your issue. If a txt-file isn't loaded completely the cause is often the msq-statement within the fileformat or an EOF char which could be handled with no EOF.

But because of your specified space as delimiter I assume that at least @6 contains some spaces within it and/or also some quotes. If this is the case the fileformat couldn't be defined appropriate and you need some additionally steps to clean your data - maybe by pre-loading the file with a fix length, like:

LOAD [@1:n] FROM Source.txt (fix, codepage is 1252);

and there counting/replacing your delimiter/quotes chars so that you could apply a "classical" fileformat-statement again.

- Marcus

[Daora342423]

Exactly, the problem is in the data and in the formatting, the data that gives problems is this (relative to the column @ 6):
Search: "666001"

this string is contained in column @ 6, the delimiter for each field is the shift. Between one field and another there may be one or two shifts, this is unspecified and the string in the @ 6 field does not always have the same content or the same length.

I tried to modify the string and write: Search: "666001

in this way it does not give problems and loads everything but if it finds another string inside the double quotes ("*" for example) it stops again.

I cannot modify the file or its formatting because it is generated every day every day for a month so I have to solve it in loading but the command does not work either
where not WildMatch (@ 6, '"*"')

[marcus_sommer · Accepted solution]

Depending on the varity of your real data it could be quite hard to handle all possibilities but with a multiple step approach in a preceeding-chain most of them should be solvable. This means something like this:

// LOAD replace(F, ' ', '|') as F;
LOAD replace(F, '"', '') as F;
LOAD replace(F, ': "', ':"') as F;
LOAD [@1:n] as F FROM Source.txt (fix, codepage is 1252);

Also helpful could be to count the number of the delimiter chars and to read their position respectively to find any other definite/unique values within your string/fields to be able to apply mid/left/right-functions to adjust the content.

- Marcus

[Daora342423]

I tried to make a replace while reading the column, in this case if in the column @ 6 there is a "I replace it with nothing ('') but also using this script:

table:
LOAD @ 1 as Server_Started,
@ 2 as Timestamp,
@ 3 as Document,
@ 4 as Type,
@ 5 as User,
replace (@ 6, '"', '') as Message,
@ 7 as Id,
@ 8 as Session
FROM 😧 Qlikview_Log Audit_QLIK-NPRINTING _ $ (v_anno) - $ (v_mese) _v2.log (txt, code page is 1252, no labels, delimiter is '', msq, header is 1 lines);

it continues to give me the same result, it stops as soon as it finds "*" and instead of replacing it during loading it blocks it

[marcus_sommer]

This won't work because the fileformat is used at first and therefore the afterwards following replacement-statements run against the wrong data. This means you need at first to clean the data before you could load them into your target-table.

Take a look on my preceeding-example from above and develop it step by step.

 - Marcus

[Daora342423]

mmm I understand, if I understand your example, you would like me to load all the string in one field first and then do the replace (right?), in this case it's not possible, I was explicitly asked to load all the column per column unfortunately ... having restrictions due to the customer's risk and having tried them all I don't get an idea to solve it ...

if you could change with replace the field before loading would be easy ...

[marcus_sommer]

Without a valid delimiter and valid quotes you don't have a valid data-structure within your files and there is no possibility to load the data "column per column" - at least not in Qlik from a txt (I doubt that other tools provide more options). If you could influence the datastructure in any way maybe in regard to the output-format so that it comes as xlsx, xml or as a sql-db it would be probably simplify your task.

Nevertheless I think my suggestion should work and I don't see much what could violate your customer ristrictions. Yes its loading at first everything into a singe field, adjusting the various issues and afterward you could load from this field with from_field or you stores the table as txt again and load the data from there.

- Marcus

[Daora342423]

Hi, I tried your solution I read the whole line in a table, I replaced the characters "with the blank and then I reread the line from the table but how can I tell it that there are 8 fields separated by shift now? My idea was to save the monocolumn table in a file and reread it but how do I in that case tell him that there are 8 columns to save with the shift in the middle?