Compile Logfiles in one excel sheet.

A

Angela

Hi,

I have placed a logsheet at location "http://www.filefactory.com/file/
b2bce33/n/uCd1554bv2800rcHj203.txt".

For above log file below is required n excel sheet:

Select files from folder. Browse and just select folder.

Read 1924716 & add "0000" at the end i.e. 19247160000 This is starting
serial.
Read 400 & stop at "19247160399" i.e. the 400th serial. This is the
end serial.

Read 1924717 & add "0000" at the end i.e. 19247170000 This is
starting serial.
Read 400 & stop at "19247170399" i.e. the 400th serial.This is the end
serial....... and so on

File name is 1554. File name for first serial batch should be 1554 +
1. Second batch should be + 1 to previous and so on.. like 1555, 1556,
1557 and so on.

Add zero to all the start & end serials.

StartSerial EndSerial Quantity FILE DATE
019247160000 019247160399 400 1555 14/02/2009
019247170000 019247170399 400 1556 14/02/2009
019247180000 019247180399 400 1557 14/02/2009
019247190000 019247190399 400 1558 14/02/2009
019247200000 019247200399 400 1559 14/02/2009
019247210000 019247210399 400 1560 14/02/2009
019247220000 019247220399 400 1561 14/02/2009

Thanks.
 
P

PY & Associates

Hi,

I have placed a logsheet at location "http://www.filefactory.com/file/
b2bce33/n/uCd1554bv2800rcHj203.txt".

For above log file below is required n excel sheet:

Select files from folder. Browse and just select folder.

Read 1924716 & add "0000" at the end i.e. 19247160000 This is starting
serial.
Read 400 & stop at "19247160399" i.e. the 400th serial. This is the
end serial.

Read 1924717  & add "0000" at the end i.e. 19247170000 This is
starting serial.
Read 400 & stop at "19247170399" i.e. the 400th serial.This is the end
serial....... and so on

File name is 1554. File name for first serial batch should be 1554 +
1. Second batch should be + 1 to previous and so on.. like 1555, 1556,
1557 and so on.

Add zero to all the start & end serials.

StartSerial     EndSerial       Quantity        FILE    DATE
019247160000    019247160399    400     1555    14/02/2009
019247170000    019247170399    400     1556    14/02/2009
019247180000    019247180399    400     1557    14/02/2009
019247190000    019247190399    400     1558    14/02/2009
019247200000    019247200399    400     1559    14/02/2009
019247210000    019247210399    400     1560    14/02/2009
019247220000    019247220399    400     1561    14/02/2009

Thanks.
Click to expand...

logsheet location not workable.
Does not seem a difficult task
 
P

PY & Associates

The link is:

http://www.filefactory.com/file/b2bce33/n/uCd1554bv2800rcHj203.txt
Click to expand...

Downloaded file does not make sense
Report: uCd1554bv2800rcHj203.txt
Start:14.02.2009 11:37 End:14.02.2009 11:37

Rech.Cat: 203 RC 250 90days
Denomination value: 250
Ru
Ord.Pak: 7 Not Ord.Pak: 0 Alr.Ord.Pak:
0
Ord.Vou: 2800 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924716
Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924717
Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924718
Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924719
Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924720
Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924721
Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924722
Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1554
Order: 7 PCK Gen.: 7 PCK 1924716 to 1924722 Gen.Vou:
2800
 
P

PY & Associates

Downloaded file does not make sense
Report: uCd1554bv2800rcHj203.txt
Start:14.02.2009 11:37 End:14.02.2009 11:37

Rech.Cat: 203 RC 250 90days
Denomination value: 250
Ru
Ord.Pak:     7 Not Ord.Pak:     0 Alr.Ord.Pak:
0
Ord.Vou:  2800 Not Ord.Vou:     0 Alr.Ord.Vou:     0
   1924716
Ord.Vou:   400 Not Ord.Vou:     0 Alr.Ord.Vou:     0
   1924717
Ord.Vou:   400 Not Ord.Vou:     0 Alr.Ord.Vou:     0
   1924718
Ord.Vou:   400 Not Ord.Vou:     0 Alr.Ord.Vou:     0
   1924719
Ord.Vou:   400 Not Ord.Vou:     0 Alr.Ord.Vou:     0
   1924720
Ord.Vou:   400 Not Ord.Vou:     0 Alr.Ord.Vou:     0
   1924721
Ord.Vou:   400 Not Ord.Vou:     0 Alr.Ord.Vou:     0
   1924722
Ord.Vou:   400 Not Ord.Vou:     0 Alr.Ord.Vou:     0
           1554
Order:     7 PCK Gen.:     7 PCK    1924716 to    1924722Gen.Vou:
2800
Click to expand...

I looked at the downloaded file again.
It appears to be one continuous long line of text.
If I split this line with "Alr.Ord.Vou:" as delimiter, I think I get:

Beginning part of the file
01924716 ......
01924717.......
..
..
01924722.......
0 1544 Order:....End--------

Trust it is then a routine to read the character position and do some
concatenation.
 
P

PY & Associates

Hi,

Well how??
Click to expand...

Open the text file in Excel, I get this
Report: uCd1554bv2800rcHj203.txt Start:14.02.2009 11:37 End:14.02.2009
11:37
Rech.Cat: 203 RC 250 90days
Denomination value: 250 Ru
Ord.Pak: 7 Not Ord.Pak: 0 Alr.Ord.Pak: 0
Ord.Vou: 2800 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924716 Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924717 Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924718 Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924719 Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924720 Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924721 Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1924722 Ord.Vou: 400 Not Ord.Vou: 0 Alr.Ord.Vou: 0
1554 Order: 7 PCK Gen.: 7 PCK 1924716 to
1924722 Gen.Vou: 2800
-------------- E N D --------------
No blank lines inbetween.

1924716 appears in row 6;
so in say I6, enter ="0" & MID(A6,4,7) & "0000";
K6, enter =MID(A6,23,3);
J6, enter=LEFT(I6,8) & K6-1;
L6, enter=+L5+1;
M6, enter=MID($A$1,LEN($A$1)-15,10) 'note absolute address

L5, enter 1554;
highlight I6:M6, pull down
format to suit
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Outlook crashes with Outlook connector 2002 0
Outlook connector crashes outlook 0
Excel sheet getting locked when opened in Ms-Access 0

Top