I'm pulling down some LUN usage data once per day. I store the data in a file name that matches the name of the LUN. Then I just append new usage amounts to the same file each day.
Filename might be serv01_luna, serv01_lunb, serv01_lunc, etc, etc.
Inside the file it would like the following to put into Excel and chart. Sizes may or may not increase:
What I want for Excel is something like
I've tried paste, but it didn't give the correct output, way too many commas and they just roll on and on.
Moderator's Comments:
Please view this code tag video for how to use code tags when posting code and data.
Last edited by dwcasey; 08-24-2012 at 01:03 PM..
Reason: added info
It is pretty easy to import data into Excel, by putting it in tab-separated-text or Comma-Separated-Value (csv). Note that the rules for csv include " to hide ',' and even newline inside columns, "" to encode a literal " and lines ending in cr-lf. Old versions of MS Access ignored these rules, so tab-separated is also great unless you need tabs in your columns!
If your data is not going to stay within the span of a worksheet, 256 x 4096 cells, send it to a database. You can still attach the database to Excel with a data fetch query.
There are PERL and other libraries to write code that writes Excel, and the new XML xlsx format must make that a cinch. Then, you can chop big data into many worksheets.
Still, it is nice to tuck it away in a RDBMS in the middle. Many free fine ones are out there, many with ODBC and JDBC access so you can work them with other free tools like IDE plugins and SQuirreL, the universal JDBC RDBMS GUI.
At the expense of coming across as pedantic, isn't the data you are storing going to be used for something else, such as a report or a graph? The data structure example you provide would not seem the most efficient way to build a report or graph off of. I dont even think Excel would be the optimal solution for this.
I concur with DGPickett that your first choice should be to write a script in for example Perl that transforms your data that is better suited for a database and subsequently analyse your data from there. Then Excel is probably not going to be part of the solution.
If you put it into a database daily, there is much less to format. If you format as you collect, in distributed collection scripts, bash could do all the parsing necessary to make each row. A cron on each host could ensure the data is collected same time every day, or even hourly, even if the network or report host is down. The report host could collect the files via ssh, rsync, rsh, ftp or they could NFS to the report host from local on the end host (not the other way around, see prior sentence).
---------- Post updated at 03:03 PM ---------- Previous update was at 02:56 PM ----------
Quote:
Originally Posted by DGPickett
If you put it into a database daily, there is much less to format. If you format as you collect, in distributed collection scripts, bash could do all the parsing necessary to make each row. A cron on each host could ensure the data is collected same time every day, or even hourly, even if the network or report host is down. The report host could collect the files via ssh, rsync, rsh, ftp or they could NFS to the report host from local on the end host (not the other way around, see prior sentence).
I have a perl script I use to pull down Cisco switch info and use a perl DB mod to shove it into a DB. Might do that with data as well.
At present, wanted a quick way to put it into Excel, next is to put it on a webpage/dashboard, then I'll look at the DB option.
---------- Post updated at 03:16 PM ---------- Previous update was at 03:03 PM ----------
Now that I'm reading the comments, I see what you guys mean. This actually might be a better way overall to report on the data. Create something like:
Can the awk script be easily modified to do something like this instead?
Dunno...now that I'm thinking more about DB stuff, wondering how my columns might look. the LUN name will always be the same. The date and sizes are what are changing.
Can the awk script be easily modified to do something like this instead?
It certainly can. But - at least for the relational DBs that I know - if the data go into one table, you would need a foreign key in there to reference the server/lun the data belong to. Thus you could use the script as is to populate the DB. If it goes to different tables, you might be able to use your original files.
Hi
I can't seem to figure out why i keep getting this error. the Spreadsheet::PhaseExcel is installed on 3 servers and is working on only 2... the last one it is installed exactly the same way but I keep getting this error.
This is whats odd.
If i run this script as a normal user i get... (2 Replies)
Very new to UNIX, so still getting used to all this.
I made a Perl script where I want to create a spreadsheet file when extracting "data" from a text file.
Now, this works perfectly fine on my Windows OS since I'm using the Win32 libraries with Microsoft Excel, but when I want to try it out on... (0 Replies)
I have a spreadsheet in a folder that looks like this:
Sector1...Sector2...Sector3...Sector4...Sector5...Sector6...Sector7
SomeID....Title.......SomeID...SomeID....SomeID...SomeID....SomeID
OtherID...MyTitle....SomeID...SomeID....SomeID...SomeID....SomeID... (7 Replies)
And I have to modify this part of the script, I'm not sure what to change the content-type to for this to work, or what else I would need to change here:
# This script sends Email acoording to command line arguments.
# $1 - file to be attached (full path with name)
# $2 - file name as it... (1 Reply)
I may have posted in the wrong section, either way I need some help.
And I have to modify this part of the script, I'm not sure what to change the content-type to for this to work, or what else I would need to change here:
# This script sends Email acoording to command line arguments.
#... (1 Reply)
Hi,
I have a perl script that prints some data in the form of a table (HTML table) Now, I want to be able to convert this data into a report on an Excel sheet. How can I do this?
Regards,
Garric (4 Replies)