how to load from text file but don't want all the fields be on a different line ?!?

[ivan_will]

Hi All,

want to load some fields with one value in it but when I load it from a text file have the situation:

so I want to have all the information on one line ! ? How to do that? In the load script I use if-clauses and textbetween funktions

Thanks in advance 🙂

[lironbaram]

hei

try this maybe this will help you

[prieper]

Cannot access the indicated source - why not post a (short) textfile here?

Anyway, the solution from RoiUser should work, if there is a common ID, if not - and if all files are regular (likein the first posting), you may create a kind of own id by counting the number of lines, so script may look

Directory;
tab1:
load ID,
 1 as temp,
 maxstring(model) as model,
 MaxString(make) as make,
 MaxString(date) as date
group by ID;
LOAD CEIL(RECNO()/3) as ID,
 TextBetween( @2, '<>','<>') as model,
 TextBetween( @2, '!','!') as make,
 TextBetween( @2, '#','#') as date
FROM [New Text Document.txt] (txt, codepage is 1251, no labels, delimiter is ' ', msq);

HTH
Peter

[ivan_will]

Hi Peter, here are the files !

As I told in the beginning don't want use temp tables or load .... from load .... statements !

[ivan_will]

Could anyone explain me why this happens ? I can't explain me why ...

[#6]

Normally the carriage return and linefeed are possible to exclude by a simple replace

replace(replace(@1,chr(10),''),chr(13),'') as Data //excludes linefeed (10) and carriage return (13)

But in this example, I just can't get it to work. The simple solution would be to open the source file in any good text editor (read notepad++) and delete all carriage returns and linefeeds. After that, it's a simple matter to just open the file and use textbetween() to find your data. Probably not too quick on the load though if there are many records.

//Jakob

[#7]

One solution (though agreed not pretty) is:

Test:
LOAD
textbetween(@1:n,'>','<') as ID,
textbetween(@1:n,'!','!') as Brand,
textbetween(@1:n,'#','#') as Date
FROM
[http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.PostAttachments/00.00.16.45.47/New-Text-Document.txt]
(fix, codepage is 1252, record is 9999999 lines);

The "record is XXX lines" makes the whole .txt-file into one big string, i.e same as deleting all carriagre returns and linefeeds.

//Jakob

[ivan_will]

Yeah! It works but not for my original data ... there I use the new lines + textbetween function and everything works fine If I use your solution have to change the whole script ( 460lines 🙂 )

Could anyone give another solution if it's possible 🙂

[ivan_will]

Is there any function fix that can that problem in the first beginning using qlikview load module?