Import Files to Excel File

Greetings,

I have a directory full of invidual GEN000*.Result files. Each one of
these files has some output which looks like this:

PDI Number: GEN000240
Finding Category: CAT I
Reference: UNIX STIG: 2.5.3.1
Description: A non-local/non-authoritative (Government) time-server is used.
Status: Not Reviewed

For example:
GEN000240: Please perform a manual review of the domain.net network time
server/client
using Checklist Section 3, GEN000240, to check the configuration.

SRR Script Version: UNIX_51-28Jan2011
UNIX SRR Checklist Page: 28


I want to parse over 300 of these into a single excel or csv file. I
wanted to put each line into a new column. How do I parse/convert
something like this?

I was thinking to do a forloop thru the files and then parse the files
via each field to the csv. Or is there a better way to do this? Should
I get an IDE type of program to help with this or is it pretty easy to
put together?

This is what I was able to put together so far:

#!/usr/bin/perl -w

@files = ;
foreach $file (@files) {
print $file . "\n";
}

-Overkill





--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/

Re: Import Files to Excel File

On 7/26/11 Tue Jul 26, 2011 10:45 AM, "Overkill"
scribbled:

> Greetings,
>
> I have a directory full of invidual GEN000*.Result files. Each one of
> these files has some output which looks like this:
>
> PDI Number: GEN000240
> Finding Category: CAT I
> Reference: UNIX STIG: 2.5.3.1
> Description: A non-local/non-authoritative (Government) time-server is used.
> Status: Not Reviewed
>
> For example:

Is the above line in the files? What is the difference between the lines
above and the lines below?

> GEN000240: Please perform a manual review of the domain.net network time
> server/client
> using Checklist Section 3, GEN000240, to check the configuration.
>
> SRR Script Version: UNIX_51-28Jan2011
> UNIX SRR Checklist Page: 28
>
>
> I want to parse over 300 of these into a single excel or csv file. I
> wanted to put each line into a new column. How do I parse/convert
> something like this?

You can use regular expressions if the lines are as consistent as you have
shown:

if( $line =~ m{ \A ([^:]+) : (.*) }x ) {
my( $key, $val ) = ($1,$2);
# store key and value somewhere
}

>
> I was thinking to do a forloop thru the files and then parse the files
> via each field to the csv. Or is there a better way to do this? Should
> I get an IDE type of program to help with this or is it pretty easy to
> put together?
>
> This is what I was able to put together so far:
>
> #!/usr/bin/perl -w
>
> @files = ;
> foreach $file (@files) {
> print $file . "\n";
> }

That just prints the name of each file. You want to open the files and read
the lines in the files, parse each line, and do something with those lines
in which you are interested.

You should also:

1. Have 'use strict;' and 'use warnings;' at the top of your program (which
makes the '-w' unnecessary and redundant).

2. Declare all local variables with 'my' so that they are lexically scoped
and not global.

3. Consider using the Spreadsheet::WriteExcel module for creating
spreadsheets (it is really cool).

4. Consider using the File::Find module for finding all of your report
files.

5. Store the data extracted from the files in a hash or hash of hashes. Read
all of the files, parse each line, and store the data from each line of
interest in the hash. Then, when all files have been read, write the saved
data to a spreadsheet document.

Let us know if you have any problems with any of this.

Good luck!



--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/