Reading line by line to get the size of each file with full path

[qlikviewforum]

Hi,

I am trying to read the size of a file line by line from the below text file(Please see the attachment). As the below format can't be read as it is could you please help me to read through QV. Data should be loaded something like below after loaded into QV. Please help!

123,904 E:\QlikView\Weekly Data - Copy.qvw

143,872 E:\QlikView\Weekly Data.qvw

.

.

114,432 E:\QlikView\Data\DIR Space.qvw

1,551     E:\QlikView\Data\EMPLOYEE.2013_34.qvd

.

.

[#2]

Please find the attached file for reference.

TEMP:

LOAD DATE#(@1,'DD-MM-YYYY') AS @1,

     @2,

     @3,

     @4,

     @5,

     @6,

     @7

FROM

aaa.txt

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

Where isnum(@3);

DATA:

LOAD DATE_C,

       TIME_C,

       SIZE,

       IF(UPPER(SubField(FILE_NAME,'.',2))='QVW','E:\QlikView\'&FILE_NAME,'E:\QlikView\Data\'&FILE_NAME) AS FILE_NAME

;     

LOAD @1 AS DATE_C,

       @2 AS TIME_C,

       @3 AS SIZE,

       IF(LEN(TRIM(@5)),TRIM(@5&@6&@7),@4) AS FILE_NAME

Resident TEMP Where @3 <> 0 AND Year(@1) > 2000;

DROP Table TEMP;

[qlikviewforum]

Thanks but I need the file name with complete path and the size only as shown in above post. I couldn't see path along with the file name.

[#4]

recheck my file again

[qlikviewforum]

Thank you. Got the expected result. But the problem is path may not remain same. This is just the sample data which I have given. This will vary according to the path and also file may be of any format. We are planning to read all the directories from all the environment. I need a common code which can read any path and give the required out put. That's where I am stuck...

[#6]

Please find the attached file and I am calculating the Directory based on the length of the string.

[qlikviewforum]

Thank you. Let me check and come back.

May I know the reason behind calculating the size for QVW and other files? It would be great if you can provide me line by comments in your code for my better understanding.  In the mean time I will check and comeback.

Also I guess you are considering only two directories? If yes, it is cumbersome to hardcode one by one directory. Please advice on this

LET v1 = Peek('F',0);

LET v2 = Peek('F',1);

Please correct me if my understanding is wrong! 😞

[qlikviewforum]

Any updates from anyone on this? Please help! It is little urgent...

[#9]

I would do differently.

First, it is a TEXT file. So I would use @2:25 to read the columns from 2 to 25

I would populate a temp table and test after that the files.

Temp1:
LOAD rowno() as LineNr,
if(lower(@2:13) = 'directory of', @15:80, peek('Directory')) as Directory,
@36:80 as LightFileName

FROM
[aaa[1]].txt](ansi, fix, no labels, header is 2 lines)
WHERE

@23:25 <> 'DIR';


TableFile:
LOAD Directory & '\' & LightFileName as CompleteFileName

RESIDENT Temp1

WHERE LightFileName <> '' AND Filesize(Directory & '\' & LightFileName) > 0

@n:m : returns the value of a TEXT file between n and m columns

peek function return the value of a previous line

Filesize returns the size of the file: if it does not exists, it returns 0

Fabrice

[#10]

Generally, I am loading your file like general text file and I put some filters to eliminate the unwanted rows.

Where isnum(@3) --> this filter verifies file size field and its always no.

For the Directory calculations: I assumed there should be only 2 directories and I am calculated based on length of the string. The Peek function is used to convert field values into variables and these variables are used to attach the path to filenames.