topic Re: Reading line by line to get the size of each file with full path in QlikView

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

qlikviewforum — Wed, 20 Nov 2013 02:49:21 GMT

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

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

Wed, 20 Nov 2013 04:25:16 GMT

Please find the attached file for reference.

TEMP:

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

@2,

@3,

@4,

@5,

@6,

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;

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

qlikviewforum — Wed, 20 Nov 2013 04:31:30 GMT

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.

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

Wed, 20 Nov 2013 04:32:12 GMT

recheck my file again

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

qlikviewforum — Wed, 20 Nov 2013 04:42:47 GMT

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...

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

Wed, 20 Nov 2013 04:58:27 GMT

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

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

qlikviewforum — Wed, 20 Nov 2013 06:32:54 GMT

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! 😞

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

qlikviewforum — Wed, 20 Nov 2013 09:33:39 GMT

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

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

Wed, 20 Nov 2013 10:28:02 GMT

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

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

Wed, 20 Nov 2013 12:17:58 GMT

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.

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

Wed, 20 Nov 2013 13:42:05 GMT

I modified the script and find the attached file

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

Wed, 20 Nov 2013 13:46:01 GMT

Anything working with all the answers ?

Fabrice

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

qlikviewforum — Wed, 20 Nov 2013 13:53:38 GMT

Hi Dathu,

Thanks, I will try and let you know.

Hi Fabrice

Got stuck with few things. Will keep you updated on this.

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

qlikviewforum — Wed, 20 Nov 2013 14:34:17 GMT

Hi Dathu,

It is working with the same file which I have provided. But the same is not working when I am using with my data . As I am not able to understand the logic of your code. I couldn't implement the same in mine . Let me understand it and will try to do something.

Thanks

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

qlikviewforum — Wed, 20 Nov 2013 18:00:52 GMT

Failed to implement with my data. Still couldn't get the desired out put. Please see if you can do any changes which was work anywhere.

Any suggestions on how to achieve with my data. May be the fields which you are taking is per the sample which I have given.(i.e @15:36,@37:80 is per the sample which I have given. I don't know what should I consider with my data ). Please help me...

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

DavidFoster1 — Fri, 22 Nov 2013 12:36:43 GMT

I have had a quick play.

I think the following approach will work:

1) Use this script in a batch file.

forfiles /s /c "cmd /c if @isdir==FALSE echo @path @fsize" > filedata.txt

2) read the text file into qlikview.

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

qlikviewforum — Fri, 22 Nov 2013 13:29:13 GMT

Thank You. I am very bad in batch programming. So please tell me where I should be passing the DIR which I want to scan it here.

For example, If I want to scan C:\Program Files\Common Files where I should be passing that in the batch file. Please help!

If this works that would be great. But if this works as per it was expected then my question will be how pass the multiple directories at the same time as we would like to scan it at a time and have the output saved in a single file.

Glad that you have helped me on this.