topic Re: Transform an unstructured file? in QlikView

Transform an unstructured file?

rustyfishbones — Fri, 03 Oct 2014 22:24:14 GMT

Hi All,

I have a file that contains hundreds of data extracts from tables similar to below

Current max fieldLen for auditedfiles (1):

Fld# SQLWidth Max Width ERROR NAME

---- -------- --------- ----- ----

2: 60 17 masterfile

3: 60 17 unixfile

4: 10 5 appl-id

6: 60 25 object-name

8: 60 22 image-file-name

9: 10 5 set-image-appl-id

11: 60 21 set-image-object-name

Current max fieldLen for audittrxdeleted (2):

Fld# SQLWidth Max Width ERROR NAME

---- -------- --------- ----- ----

2: 12 2 warehouse

3: 12 2 trx-type

6: 48 8 customer

7: Date 0 trx-date

10: 360 44 comment

11: 114 15 added-by

12: Date 0 added-on

13: 16 0 edited-by

14: Date 0 edited-on

What I need to do is create a clean table in Qlikview that will look like this, I have hundreds of rows, I tried cleaning it up with SUBFIELD, TEXTBETWEEN etc.... but can't get it the way I want

Fld#	SQLWidth	MaxWidth	ERRORNAME	Table
02:00	60	17	masterfile	auditedfiles
03:00	60	17	unixfile	auditedfiles
04:00	10	5	appl-id	auditedfiles
06:00	60	25	object-name	auditedfiles
08:00	60	22	image-file-name	auditedfiles
09:00	10	5	set-image-appl-id	auditedfiles
11:00	60	21	set-image-object-name	auditedfiles
02:00	12	2	warehouse	audittrxdeleted
03:00	12	2	trx-type	audittrxdeleted
06:00	48	8	customer	audittrxdeleted
07:00	Date	0	trx-date	audittrxdeleted
10:00	360	44	comment	audittrxdeleted
11:00	114	15	added-by	audittrxdeleted
12:00	Date	0	added-on	audittrxdeleted
13:00	16	0	edited-by	audittrxdeleted
14:00	Date	0	edited-on	audittrxdeleted

Any ideas

Re: Transform an unstructured file?

stevedark — Fri, 03 Oct 2014 22:51:23 GMT

Hi Alan,

Looking at your example data there are no spaces that couldn't be used as delimiters. Is this the case for all of the data?

If so you should be able to do it in two parses.

First, load the file with no delimiters or quoting on the data. Parse this on the way in with the following, to make each multiple space into another delimiter:

replace(replace(replace(replace(replace(DataLine, ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' '), ' ', ',') as Delimited

You can then store this to a new text file. That text file you should be able to load in as delimited text.

I'm sure there is a much more elegant way of removing the multiple spaces to get them down to one space, but you get the idea.

Probably worth doing one file at a time in a for each vFile in FolderList('*.txt') rather than trying to do it with a load with a wildcard.

Hope that helps.

Steve

Re: Re: Transform an unstructured file?

Anonymous — Sat, 04 Oct 2014 11:03:46 GMT

How about something like this ?

Temp :

load

[Fld#] ,

[SQLWidth] ,

[MaxWidth] ,

[ERRORNAME] ,

if(len(Table)=0,Peek('Table'),Table) as Table

;

Load

where

not wildmatch ([Fld#], '---*' )

and

not [Fld#]= 'Fld#'

and

len(Fld#) > 0

;

LOAD

@1 as [Fld#] ,

@2 as [SQLWidth] ,

@3 as [MaxWidth] ,

@4 as [ERRORNAME] ,

@5 as [Table]

FROM

(txt, codepage is 1252, no labels, delimiter is spaces, msq)

;

Data :

NoConcatenate

load

Rowno() ,

resident Temp

where

not [Fld#]= 'Current'

;

drop table Temp ;

Re: Re: Transform an unstructured file?

rustyfishbones — Sun, 05 Oct 2014 10:52:48 GMT

Thanks Steve and Bill

Bill's method worked better for me and I also learned something new.

Regards

Alan

Re: Re: Transform an unstructured file?

rustyfishbones — Sun, 05 Oct 2014 10:53:23 GMT

Thanks Steve.

Surely a Qlik Luminary for 2015!

All the best

Re: Re: Transform an unstructured file?

rustyfishbones — Sun, 05 Oct 2014 11:17:01 GMT

Actually I am still missing the Tablename

But I will try figure it out based on what I have thanks

Regards

Alan

Re: Transform an unstructured file?

Anonymous — Sun, 05 Oct 2014 12:59:33 GMT

Alan

This bit should help with the Tablename

if(len(Table)=0,Peek('Table'),Table) as Table

Bill