Load a single field with NO TRIM of spaces around record

[#1]

Hello !

Have a plain AscII file, variable length records , that I want to load as A SINGLE FIELD (a string with say 80 characters).

Here's a sample of the data :

SBL01 16/04/2010 15:54:14 TC00101
SBL01 16/04/2010 15:54:44 TC00101
SBL01 16/04/2010 15:55:14 TC00101
16/04/2010 07:29:10 TC00101
16/04/2010 07:30:14 TC00101
16/04/2010 07:30:43 TC00101
16/04/2010 07:31:12 TC00101
16/04/2010 07:31:41 TC00101
16/04/2010 07:32:10 TC00101
CDES1 16/04/2010 08:49:49 TC06699 086330 1
CDES1 16/04/2010 08:49:49 TC06701
CDES1 08:49:49 TC00901
CDES1 16/04/2010 08:50:00 TC00202
CDES1 16/04/2010 08:50:01 TC06501
CDES1 16/04/2010 08:50:01 TC06701
CDES1 16/04/2010 08:50:01 TC00901
CDES1 16/04/2010 08:50:04 TC06606
CDES1 16/04/2010 08:50:05 TC06699 086330 1
CDES1 16/04/2010 08:50:05 TC06701
CDES1 16/04/2010 08:50:05 TC00901
CDES1 16/04/2010 08:54:10 TC00101
CDES1 16/04/2010 08:54:38 TC00101
CDES1 16/04/2010 08:54:42 TC06606
CDES1 16/04/2010 08:54:43 TC06699 086330 1
CDES1 16/04/2010 08:54:43 TC06701
CDES1 16/04/2010 08:54:43 TC00901
CBITE 01/04/2010 12:08:10 TC06699 324068 3
CBITE 01/04/2010 12:08:10 TC06701
CBITE 01/04/2010 12:08:10 TC25901
CBITE 01/04/2010 12:08:20 TC26301
CBITE 01/04/2010 12:12:00 TC00101
CBITE 01/04/2010 12:12:18 TC00101
CBITE 01/04/2010 12:12:47
CBITE 01/04/2010 12:13:15 TC00101
CBITE 01/04/2010 12:13:45 TC00101
CBITE 01/04/2010 12:14:14 TC00101
CBITE 01/04/2010 12:14:50 TC00101
CBITE 01/04/2010 12:15:19 TC00101
CBITE 01/04/2010 12:15:48 TC00101
CBITE 01/04/2010 TC00101
CBITE 01/04/2010 12:16:47 TC00101
CBITE 01/04/2010 12:17:16 TC00101
CBITE 01/04/2010 12:17:45 TC00101
CBITE 01/04/2010 12:18:14 TC00101
CBITE 01/04/2010 12:18:43 TC00101
CBITE 01/04/2010 12:19:12 TC00101
CBITE 01/04/2010 12:19:41 TC00101
CBITE 01/04/2010 12:20:10 TC00101
CBITE 01/04/2010 12:20:39 TC00101
CBITE 01/04/2010 12:21:08 TC00101
CBITE 01/04/2010 12:21:37 TC00101

I need to load this data as A SINGLE FIELD, without trimming spaces around the record.

Say record # 4, it should be loaded as " 16/04/2010 07:29:10 TC00101" , that is, with spaces on the left side as on the original record.

How do I do that ?

Thanks !

By the way, have provided a simple kit file for testing purposes...

[#2]

Maybe you are looking for something like this:

Test1:
LOAD @1:5 as F1,
 @6:25 as F2,
 @27:33 as F3,
 @34:40 as F4,
 @41:42 as F5

FROM
C:\1\TEST189P\TEST189P.TXT
(fix, utf8, record is 1 lines);

See attachment

[#3]

It is close to what I need.

However, look at this record :

"CBITE 01/04/2010 TC00101"

The time field of this record is not OK.

I really would like simply to load the records as a single string of 80 bytes...

[jonathandienst]

Adriano

This seems to work (tested on the first 20 of your records:

TEST:
LOAD F1 & F2 & F3 & F4 & F5 AS Full;
LOAD @1:5 AS F1,
@6:16 AS F2,
@17:25 AS F3,
@26:34 AS F4,
@35:n AS F5
FROM
[..\Test Folder\TestData.txt]
(fix, codepage is 1252);

Jonathan

[#5]

Is it necessary to explain how to write a field value derivation If condition in case of the value is null?

[Anonymous]

Cant you just load it with

LOAD @1:80 AS Full
FROM
[..\Test Folder\TestData.txt]
(fix, codepage is 1252);

[#7]

Nop. It swallows the spaces...

[#8]

Instead of load a whole string and parse it by left(), mid() and right() functions you can just do this data cleaning by one load statement:

Test1:

Load
 F1, F4, F5, F6,
 timestamp#(F2 & ' ' & F3, 'DD/MM/YYYY hh:mm:ss') as DateTime
;
load
 if(len(F1)=0, 'Default Code', F1) as F1,
 if(len(F2)=0, date(today()), F2) as F2,
 if(len(F3)=0, '00:00:00', F3) as F3,
 if(len(F4)=0, 'Default Code', F4) as F4,
 F5,
 F6
;

LOAD Trim(@1:5) as F1,
 Trim(@6:16) as F2,
 Trim(@17:25) as F3,
 Trim(@26:33) as F4,
 Trim(@34:40) as F5,
 Trim(@41:n) as F6
FROM
C:\1\TEST189P\TEST189P.TXT
(fix, utf8);

See attachment.

[hector · Accepted solution]

Hi, check the variable Verbatim

From the help:

Verbatim

Normally all field values are automatically stripped of preceding and succeeding blank characters (ASCII 32) before being loaded into the QlikView database. Setting this variable to 1 suspends the stripping of blank characters.

Example:

set Verbatim = 1;

Rgds

[#10]

Yeeeeeeeeeeeeahh !

I had read that before on the docs but I couldnt REMEMBER where it was. I though it was on the LOAD statement documentation but it wasnt !

Perfect ! Simple !

Thank you all guys who have cooperated for the issue. I'm sticking with Verbatim !

Thanks !