Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Re: Parsing Data in xlsx file

by Kenosis (Priest)
on Aug 02, 2012 at 16:34 UTC ( [id://985071]=note: print w/replies, xml ) Need Help??


in reply to Parsing Data in xlsx file

Perhaps the following will work for you:

use Modern::Perl; use Excel::Writer::XLSX; my ( $rowNumber, %headingsSeen ) = 1; my $workbook = Excel::Writer::XLSX->new('data.xlsx'); my $worksheet = $workbook->add_worksheet(); while (<DATA>) { next if /^[-#+]/ or ( /S.No/ and $headingsSeen{/S.No/}++ ); $worksheet->write( 'A' . $rowNumber++ , [ grep s/\A\s*(.*?)\s*\z/$1/, ( split /\|/ )[ 0 .. 11 ] ] ); } __DATA__ ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| S.No | TimeStamp| UE-Name| Test-Config | UL/DL | Status +| Jitter ms | Throughput Mb/s | lost % | Bandwidth Mb/ +s | Duration Sec | Fail-Reason | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 1 | 18:24:38 | -NA- | Sanity_001_0001_10_00 | System | Passed +| -NA- | -NA- | -NA- | -NA- + | -NA- | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 2 | 18:31:34 | ueh1 | Sanity_002_0002_00_11 | UL | Passed +| 0.14 of 10.00 | 4.38 of 3.97 | 0.00 of 10.00 | 4.38 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 3 | 18:23:48 | ueh1 | Sanity_003_0003_06_09 | UL | Passed +| 0.16 of 10.00 | 4.00 of 3.62 | 0.00 of 10.00 | 4.00 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 3 | 18:23:48 | ueh1 | Sanity_003_0003_06_09 | DL | Passed +| 3.37 of 10.00 | 5.18 of 4.67 | 0.00 of 10.00 | 5.17 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 4 | 18:38:30 | ueh1 | Sanity_004_0004_24_00 | DL | Passed +| 1.34 of 10.00 | 13.72 of 12.40 | 0.00 of 10.00 | 13.70 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 5 | -NA- | -NA- | Sanity_001_0001_10_00 | System | -NA- +| -NA- | -NA- | -NA- | -NA- + | -NA- | Micro-Step | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 6 | 18:31:34 | ueh2 | Sanity_002_0002_00_11 | UL | Passed +| 0.15 of 10.00 | 4.38 of 3.97 | 0.00 of 10.00 | 4.38 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 7 | 18:23:49 | ueh2 | Sanity_003_0003_06_09 | UL | Passed +| 0.16 of 10.00 | 4.00 of 3.62 | 0.00 of 10.00 | 4.00 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 7 | 18:23:48 | ueh2 | Sanity_003_0003_06_09 | DL | Passed +| 3.38 of 10.00 | 5.18 of 4.67 | 0.00 of 10.00 | 5.17 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 8 | 18:38:30 | ueh2 | Sanity_004_0004_24_00 | DL | Passed +| 1.32 of 10.00 | 13.72 of 12.40 | 0.00 of 10.00 | 13.70 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| # Total Test-Cases = '4' # Total Passed-Cases = '4' # Total Failed-Cases = '0' ++++++ SUMMARY ++++++ ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| S.No | TimeStamp| UE-Name| Test-Config | UL/DL | Status +| Jitter ms | Throughput Mb/s | lost % | Bandwidth Mb/ +s | Duration Sec | Fail-Reason | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 1 | 18:24:38 | -NA- | Sanity_001_0001_10_00 | System | Passed +| -NA- | -NA- | -NA- | -NA- + | -NA- | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 2 | 18:31:34 | ueh1 | Sanity_002_0002_00_11 | UL | Passed +| 0.14 of 10.00 | 4.38 of 3.97 | 0.00 of 10.00 | 4.38 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 3 | 18:23:48 | ueh1 | Sanity_003_0003_06_09 | UL | Passed +| 0.16 of 10.00 | 4.00 of 3.62 | 0.00 of 10.00 | 4.00 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 3 | 18:23:48 | ueh1 | Sanity_003_0003_06_09 | DL | Passed +| 3.37 of 10.00 | 5.18 of 4.67 | 0.00 of 10.00 | 5.17 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 4 | 18:38:30 | ueh1 | Sanity_004_0004_24_00 | DL | Passed +| 1.34 of 10.00 | 13.72 of 12.40 | 0.00 of 10.00 | 13.70 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 5 | -NA- | -NA- | Sanity_001_0001_10_00 | System | -NA- +| -NA- | -NA- | -NA- | -NA- + | -NA- | Micro-Step | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 6 | 18:31:34 | ueh2 | Sanity_002_0002_00_11 | UL | Passed +| 0.15 of 10.00 | 4.38 of 3.97 | 0.00 of 10.00 | 4.38 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 7 | 18:23:49 | ueh2 | Sanity_003_0003_06_09 | UL | Passed +| 0.16 of 10.00 | 4.00 of 3.62 | 0.00 of 10.00 | 4.00 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 7 | 18:23:48 | ueh2 | Sanity_003_0003_06_09 | DL | Passed +| 3.38 of 10.00 | 5.18 of 4.67 | 0.00 of 10.00 | 5.17 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 8 | 18:38:30 | ueh2 | Sanity_004_0004_24_00 | DL | Passed +| 1.32 of 10.00 | 13.72 of 12.40 | 0.00 of 10.00 | 13.70 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| # Total Test-Cases = '4' # Total Passed-Cases = '4' # Total Failed-Cases = '0'

Output (copied from generated data.xlsx spreadsheet):

S.No TimeStamp UE-Name Test-Config UL/DL Status Jitt +er ms Throughput Mb/s lost % Bandwidth Mb/s Duration Sec + Fail-Reason 1 18:24:38 -NA- Sanity_001_0001_10_00 System Passed +-NA- -NA- -NA- -NA- -NA- -NA- 2 18:31:34 ueh1 Sanity_002_0002_00_11 UL Passed 0.14 + of 10.00 4.38 of 3.97 0.00 of 10.00 4.38 360 -NA- 3 18:23:48 ueh1 Sanity_003_0003_06_09 UL Passed 0.16 + of 10.00 4.00 of 3.62 0.00 of 10.00 4 360 -NA- 3 18:23:48 ueh1 Sanity_003_0003_06_09 DL Passed 3.37 + of 10.00 5.18 of 4.67 0.00 of 10.00 5.17 360 -NA- 4 18:38:30 ueh1 Sanity_004_0004_24_00 DL Passed 1.34 + of 10.00 13.72 of 12.40 0.00 of 10.00 13.7 360 -NA- 5 -NA- -NA- Sanity_001_0001_10_00 System -NA- -NA- + -NA- -NA- -NA- -NA- Micro-Step 6 18:31:34 ueh2 Sanity_002_0002_00_11 UL Passed 0.15 + of 10.00 4.38 of 3.97 0.00 of 10.00 4.38 360 -NA- 7 18:23:49 ueh2 Sanity_003_0003_06_09 UL Passed 0.16 + of 10.00 4.00 of 3.62 0.00 of 10.00 4 360 -NA- 7 18:23:48 ueh2 Sanity_003_0003_06_09 DL Passed 3.38 + of 10.00 5.18 of 4.67 0.00 of 10.00 5.17 360 -NA- 8 18:38:30 ueh2 Sanity_004_0004_24_00 DL Passed 1.32 + of 10.00 13.72 of 12.40 0.00 of 10.00 13.7 360 -NA- 1 18:24:38 -NA- Sanity_001_0001_10_00 System Passed +-NA- -NA- -NA- -NA- -NA- -NA- 2 18:31:34 ueh1 Sanity_002_0002_00_11 UL Passed 0.14 + of 10.00 4.38 of 3.97 0.00 of 10.00 4.38 360 -NA- 3 18:23:48 ueh1 Sanity_003_0003_06_09 UL Passed 0.16 + of 10.00 4.00 of 3.62 0.00 of 10.00 4 360 -NA- 3 18:23:48 ueh1 Sanity_003_0003_06_09 DL Passed 3.37 + of 10.00 5.18 of 4.67 0.00 of 10.00 5.17 360 -NA- 4 18:38:30 ueh1 Sanity_004_0004_24_00 DL Passed 1.34 + of 10.00 13.72 of 12.40 0.00 of 10.00 13.7 360 -NA- 5 -NA- -NA- Sanity_001_0001_10_00 System -NA- -NA- + -NA- -NA- -NA- -NA- Micro-Step 6 18:31:34 ueh2 Sanity_002_0002_00_11 UL Passed 0.15 + of 10.00 4.38 of 3.97 0.00 of 10.00 4.38 360 -NA- 7 18:23:49 ueh2 Sanity_003_0003_06_09 UL Passed 0.16 + of 10.00 4.00 of 3.62 0.00 of 10.00 4 360 -NA- 7 18:23:48 ueh2 Sanity_003_0003_06_09 DL Passed 3.38 + of 10.00 5.18 of 4.67 0.00 of 10.00 5.17 360 -NA- 8 18:38:30 ueh2 Sanity_004_0004_24_00 DL Passed 1.32 + of 10.00 13.72 of 12.40 0.00 of 10.00 13.7 360 -NA-

Hope this helps!

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://985071]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (4)
As of 2024-03-29 00:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found