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 = <data/GEN*.Result>;
  foreach $file (@files) {
    print $file . "\n";
  }

-Overkill




0
overkill
7/26/2011 5:45:17 PM
perl.beginners 29335 articles. 3 followers. Follow

1 Replies
477 Views

Similar Articles

[PageSpeed] 49

On 7/26/11 Tue  Jul 26, 2011  10:45 AM, "Overkill" <overkill@sadiqs.net>
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 = <data/GEN*.Result>;
>   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!


0
jimsgibson
7/26/2011 6:53:42 PM
Reply:

Similar Artilces:

Importing data from either Excel Files or CSV files
Hi, I am having trouble uploading data from Excel/CSV files.  Any idea how I can get my ASP.NET application to do this?  Also, I need to transfer the data into SQL Server tables.  Any suggestion is greatly appreciated.  Thank you, Jules  this link help youhttp://www.codeproject.com/cs/database/CsvReader.asp  Try this one... its a good option http://www.codeproject.com/cs/database/FinalCSVReader.asp Thank you Bupender Thanks chrisjob012!  Mark as answer if reply helped you  this gives us the confident to reply further I am new to progra...

how to import or transform an excel file to sunbird/ics-file?
I often get some list of appointment-data by way of excel-files an want to include them into my sunbird 0.2 calendar. Are there any ways of transforming/creating an ics-file out of an excel-file? Thanks in advance Patrick I suppose Excel can export a CSV file for Sunbird to import, but I have not tried it. -- Rod Whiteley ...

to file:// or not to file://
(Firefox 1.5.0.6) I'm having trouble with HTML Anchors (links) using the file: protocol. The URL I'm trying to get at looks something like this file://///Host.Domain/Share/Dir1/Dir2/File.Ext If I click on the link, /nothing/ happens but, if I "Copy Link Location" and paste exactly the same thing into the "address bar" in FireFox, FireFox is perfectly happy to show me the file. Why the difference? Am I tripping over some security restriction here? Any suggestions? TIA, Phill W. Phill W. wrote: > (Firefox 1.5.0.6) > I'm ...

Components for importing/exporting datasets from/to Excel files or csv file [Edit]
Could you please recommend free and/or commercial components (source code available) for importing/exporting datasets data from/to Excel and csv files? I read about EMS Advanced import/export VCL components but I want to use something lighter and preferably free. My other requirement is that the components don't need Excel, i.e. they don't use excel automation to read or write excel files. Most of the components that I've seen seem to be dead or they don't exist anymore. Thanks Get Flexcel - it's one of the most impressive components I've ever used http://...

Excel experts : Filling an complex Excel file and parsing a complex Excel file
I am stuck with below mentioned problem The requirement is that, we have Excel file template(.xls) which has Macros and most of the Worksheets are either hidden OR locked. When the end user clicks on a button to download it, follows modifications has to be done to the Excel sheet. Unlock the Excel worksheet, fill some data at certain cells and then lock it back Write some data into a hidden worksheet There are two options as far as I know Excel Interop/MICROSOFT Excel 11.0 Object Library  OLEDB Driver I cannot go with option 1 becaus...

Importing text file (possibly a excel file) via a web-form to a database.
Hey there - I'm sort of new to this and have a question - which in itself is probably easy, I'm just having trouble finding it. I need to import a text file - possibly later an excel file - into a SQL database via a web-form upload function. Can anyone point me in the right direction? A tutorial page that you know of or sample code, possibly? Any help would be appreciated. Thanks Hi, Take a look @ : - Reading a Delimited File Using ASP.Net and VB.Net - HTHK B ShajudeenAreYaar.comDubai, United Arab Emirates. That was perfect - thank you!...

How to copy data from one excel file to other excel file
Hi I have two excel file (Ex1 and Ex2), both have same template (headers) and data will be populated from the dataset. Upto this step its works fine. My question : How to copy the data  form 6th row to last row of "Ex2" and paste it into the "Ex1" after the last row in Ex1.   Advance thanks Have a look here: For VB - http://support.microsoft.com/kb/306022 For C# - http://support.microsoft.com/kb/306023/ -Damien  Visoft, Inc - Home | Blogs Latest Blog Post: ASP.NET 4.0 AJAX Preview 4 - Client Templates...

superreview denied: [Bug 242953] an Imported eudora attached file is always named "Attached File" instead of its real name : [Attachment 148287] Set the real name to an eudora imported attached file.
Scott MacGregor <mscott@mozilla.org> has denied Scott MacGregor <mscott@mozilla.org>'s request for superreview: Bug 242953: an Imported eudora attached file is always named "Attached File" instead of its real name http://bugzilla.mozilla.org/show_bug.cgi?id=242953 Attachment 148287: Set the real name to an eudora imported attached file. http://bugzilla.mozilla.org/attachment.cgi?id=148287&action=edit ------- Additional Comments from Scott MacGregor <mscott@mozilla.org> Yann, is the fileName argument that gets passed into AddAttachment already...

How to copy one existing excel file contents to a new excel file?
Hi All,    I need VB code to copy sheet contents of one exisiting excel file to another new excel file.   For example: I have abc.xls file with sheet name abcd.   I want to copy the contents to xyz sheet of new excel file xyz.xls. Pls help me regarding this. Regards Abdul      Pls help me.        Which version of Excel are you using? The latest uses an XML format which can be manipulated programatically. Don't forget to click "Mark as Answer" on the post that helped you.This credits that member, ear...

How to convert the html content in Excel file to csv fomat in Excel file
How to convert the html content in Excel file to csv fomat in Excel file  Hello Tapan,Can you explain what are you trying to do? Do you want to export the html content in Excel or CSV File ?Thanks,Deepesh Verma Dont forget to click “Mark as Answer” on the post that helped you.This credits that member, earns you a point and mark your thread as Resolved for the sake of Future Readers....

Got error after open excel file from server..During Export Excel File
this error appears if you have tried to save a workbook on a drive and you do not have Modify and Delete permissions to the location where the file is being saved. It is also possible that another user has a read lock on the file.Excel saves a file using three steps (if the file already exists in the target location):Creates a temporary file with a randomly generated name, such as 4CCA4100.Deletes the original file.Renames the temporary file to the original file name.If Excel cannot delete the original file or cannot rename the temporary file, this error message appears.To temporarily work a...

file administration, uploading excel files...
I have a project that I want to work on that involves usertypes and uploading excel spreadsheets into certain folders. Depending on the usertype you may not be able to see certain folders. I have looked at building a node tree structure, but it seems that it would build slow. Any thoughts on a approach to this matter?- km______________________________________"always here and always forever" Is it within your scope to create roles, assign folders to roles, and just let the user upload their excel file to the folder specified in the role they are in?Bryan SampicaAuction Site Starte...

Importing a .js file into a .js file
I have a javascript file that tries to call a function in another javascript file and it doesn't seem to be working.  Is there an 'import' or 'include' statement that I need to have.  The reason I'm not just putting the function in the file is that I want it to be available to a bunch of different javascript files. Thanks just call the function in the external javascript file as usual..the only condition is that both files must be included in the page.Home Is Where the Wind Blowshttp://pushpontech.blogspot.com...

upload data from an excel file directly into db without saving the excel file
Hi, I want to give users the ability to upload an excel file and then take the data out of the first column in that excel file and insert that into my db.  NB The users will be told to only put data into the first column. I know how to put the input file on my webform and have them upload the file to the server - but i think there must be a better way as i dont want to keep the excel file - just take the info out of the column and discard the excel file. Does anyone have any ideas/tutorials on best way to do this. Thanks...   I have done similar thing, but you can not j...

Web resources about - Import Files to Excel File - perl.beginners

Gaza imports - Wikipedia, the free encyclopedia
Import of goods into the Gaza Strip is limited as part of the 2007–present blockade of the Gaza Strip imposed by Israel and Egypt . Israel allows ...

Instagram 4.1 Adds Ability To Import Video, Automatic Straightening
Facebook-owned photo-sharing network Instagram released Instagram 4.1 last week, which added the ability for users to import video from their ...

Oakland Opens the Wallet for Cuban Import
Looks like Billy Beane really is sticking to that Window. After spending the offseason trading away two of the better young starting pitchers ...

Import and Publish YouTube Videos to Your Facebook Page with Cueler YouTube
Cueler is a useful and easy Facebook application that allows Page administrators to automatically synchronize their YouTube videos to their Facebook ...

Export Import Business Group
Here we are, people in the business of export and import from all over the world. I invite you people also to join for more info of the business ...

HelloSign - Scan or Import Documents to Sign for iPhone 3GS, iPhone 4, iPhone 4S, iPhone 5, iPod touch ...
Get HelloSign - Scan or Import Documents to Sign on the App Store. See screenshots and ratings, and read customer reviews.

Secretary Kerry Delivers Remarks at the Export-Import Bank Annual Conference - Flickr - Photo Sharing ...
U.S. Secretary of State John Kerry delivers remarks at the Export-Import Bank Annual Conference in Washington, D.C., on April 24, 2014. [State ...

The Kronies: Laughing All The Way to the Export-Import Bank - YouTube
Get Konnected at http://thekronies.com/ In this very special episode of "The Less You Know", Johnny and Bobby learn a valuable lesson about campaign ...

Kayla Standish cycle complete as US forward follows sister to Adelaide Lightning as WNBL import - AdelaideNow ...
ADELAIDE Lightning is racing the clock to have new American forward Kayla Standish cleared in time to join the team in its WNBL clash with the ...

Four caught in alleged Syd cocaine import - The Courier-Mail Search Search
THREE men have been arrested after allegedly trying to import 20 kilograms of cocaine into Sydney on a cruise ship.

Resources last updated: 11/24/2015 9:03:37 AM