Ok, it's almost 10pm, and I'm still at work. I'm going to take a break from my tasks and bitch a bit about Microsoft Excel. For some reason, Excel just loves hard coded maximums.
Here are the top 3 things about Excel that piss me off. Maybe I'm just a moron and don't know how to get Excel to do this, but here's my current Top 3 list of annoying Excel properties. If anyone knows how to get around some of these limitations, please share.
1) 64K Row and 256 Column Limit - I mean seriously... this is f'ing annoying.
2) The inability to set more than a single custom delimiter when opening up a text file. For example, I have data output files that are delimited by commas, and #'s. But, I can choose to turn on the built-in comma-delimiter, but then I am only allowed to specify either the # or the $ as my custom delimiter. So, now I have to do a search and replace and replace $'s with #'s and specify the # as the customized one. Incredibly lame.
3) Sorting data only allows me to choose 3 fields to sort based on. Unfortunately, I have to deal with data that has as many as 20 fields, and needs to be sorted using all the fields. It is ridiculous for me to have to come up with an encoding of the 20 fields in order to get Excel to sort it the way I need.
Argh. F* Excel.s, and #'s. But, I can choose to turn on the built-in comma-delimiter, but then I am only allowed to specify either the # or the $ as my custom delimiter. So, now I have to do a search and replace and replace $'s with #'s and specify the # as the customized one. Incredibly lame.
3) Sorting data only allows me to choose 3 fields to sort based on. Unfortunately, I have to deal with data that has as many as 20 fields, and needs to be sorted using all the fields. It is ridiculous for me to have to come up with an encoding of the 20 fields in order to get Excel to sort it the way I need.
Argh. F* Excel.
5 comments:
Use cygwin.
sed -e 's/[#\$]/,/g' filename > outfile
That should turn them all into commas for you, or turn them all into whatever you want. They'll need to be the same for the next step.
Then, throw it through sort as many times as you need to.
man sort
That will give you the info you need to sort on whatever column you want, however you want. -s will stabilize it (which will be important).
Why did you need Excel again?
Well, I needed to be able to sort and re-sort data over and over again in different ways in order to visualize it better.
Ya, the replacing delimiter thing wasn't as big a deal, but it's just annoying to have an extra step.
I'm using cygwin, but I really don't use it as often as I should.
Would storing your data in a database table do the job? Let’s say you use SQL Server. Clearly you would not have to deal with the annoying 64K row and 256 column limits. Now as far as data import, data manipulation and sorting you’ll have a lot of options in those departments. Starting with data import, there are several methods and you can implement your options via scripting such as looking for specific delimiters and you are not limited to one. Data manipulation is also a breeze since you can write query logic to update data based on specific conditions. Finally, when it comes to sorting, you can use ORDER BY SQL command when querying your data. I know with ORDER BY you can specify multiple columns in order of priority and then you can also specify ascending or descending by the SQL add on ASC DESC.
No, database is out of the question, since I need to be able to visualize and manipulate the data quickly over and over again (i.e. graphing, formula-based color coding of cells, etc).
For the large datasets, I can use Matlab, but I'm much more comfortable with Excel, but now I am regretting it.
Actually Matlab connects to your database. So for larger datasets you can have Matlab get data from the database. Additionally, you can have Matlab store results set in your database. There are several reporting tools such as Crystal Reports that will help you visualize your data in different formats easily. Crystal Reports will also connect to your database.
Post a Comment