I can't import a file in a spreadsheet (Excel file)?!
Hi all;
I have a file with ".tgff" format that a piece of it, is as below (this file is for describing a benchmark in hardware design) Code:
# AMD ElanSC520-133 MHz Informations like this line: # type version valid task_time preempt_time code_bits task_power to be shown in columns, I mean each item (like type, version, ...) in one column and the values below them in the related column. But I want that the first column to be intended for the "comment" lines like: # Angle to Time Conversion # Basic floating point # Bit Manipulation ... So that the related value of each one, to be shown in front of each comment: in 2th column, 3th column, ... I can do this work with copy them and paste in a spreadsheet file one by one, but I want it to be done completely Automatic! One solution is: saving this file (.tgff) in ".csv" format in linux and select the "space" character as a separator. but again, after opening it in the openoffice spreadsheet, we have to change some parts manually! Anyway, is there a complete solution to do this? Or is it required to have a program (some codes) to parse for example "#" items as "comment" in first column in linux and ...? Or is there a Tool to do this in windows or specially in linux? Or any Tip&Trick to do so?! BTW, I've uploaded a sample ".xls" file in the following links to show the desired output in a spreadsheet together with ".tgff" file: Code:
http://www.mediafire.com/?z2b6860ka40avdp TIA. |
Looks like a suitable task for either awk or perl to me.
If the format of the text file is always the same - some header info, #----, column headings ... - you can use awk with NF and and then pattern matching to easily mangle text. Cheers, Tink |
Do it Table by Table
In Open Office, or even in excel, you can only do it table by table. I have observed that your data is not a one big table but a series of smaller ones.
If you're good at macros maybe you can code this. The Things that you have to do for a single table are this. This is in openoffice. 1. Copy a single table into spreadsheet, a text import dialog box will appear. Just ok it. This will make the data just dump in one column (assume at C1). 2. Assuming the column headers are in C5, put this formula in A7 : =IF(LEFT(C6,1)="#",C6,"") and in B6: =IF(LEFT(C6,1)="#","",C6) drag both formulas using handle button till the end Now, select all the formulaed cells, Ctrl+C, Ctrl+Shift+V and select only text option. I hope you got the comments beside the data. 3. Delete the copied data in C column, select from B6 to end, type Alt+d,x. You will get Text import dialog, Select Fixed Width, and put the separators in the gap between the items. Now you get the data in proper columns. 4. If you want to delete the unnecessary columns now, Select the table and put filter, from the dropdown list, select -empty- and select all the empty rows (shift +space) and ctrl + - them. Again select All from the dropdown list and remove the filter. Post ur reply. |
It might be possible to convert it to a CSV (comma-separated values, it's one of the simplest formats readable by a spreadsheet) file using perl or awk.
|
Basically, you are asking us to write, and give you a script to convert this file for you.
As you have probably realised, this isn't a trivial task, and we are unpaid (and frequently also unthanked) volunteers. I suggest you read up on perl and awk, as suggested above, make a good attempt to write your own script, and then post it here if it isn't working for you, with the details of why it is failing. Then you'll certainly get the help you need. Good luck :) |
Not elegant, but it produces a usable result with the data
in the original post: Code:
awk 'BEGIN{OFS=","}NR==6{$1="\"\"";print $0}NR>6&&/^#/&&$0!=""{printf "\"%s\",",gensub(/# /, "","g",$0)}NR>6&&!/^#/&&$0!=""{$1=$1;print $0}' snippet |head -n 10 should look good enough. Cheers, Tink |
All times are GMT -5. The time now is 03:49 AM. |