Converting text files to xls through awk script for specific data format
Dear Friends,
I am in urgent need for awk/sed/sh script for converting a specific data format (.txt) to .xls.
The input is as follows:
The desired output EXCEL table will the contain the headers and data in the following order:
I have to iteratively perform this conversion in several (1000+ ) .txt files.
I was trying with the preliminary awk one-liners and trying to use "CAT" for concatenating all 3 .xls output which did not fulfiling the purpose.
I need urgent help!!!!!!!!!, looking forward for your responses (new script/ modified version of the one-liners).
P.S: Input file and output file is attached for more clarity.
Best regards,
Amit
Moderator's Comments:
Although attaching files sometimes helps, using CODE tags (as required by forum rules) makes it much easier for those of us trying to help you see spacing between fields and to be able to see field alignments. Please use CODE tags when including sample code, sample input file data, and sample output file data.
Note also that gsub(ERE, replacement, string) returns the number of substitutions performed, not the modified string. And, since there are multiple commas on the Score line, it might make more sense to get rid of all of the commas on the input line in one shot before splitting out the fields than to perform gsub() calls on the split fields. In fact, if we set the field separator differently, we can avoid the need to perform any substitutions. The following creates a comma separated values file that can easily be loaded into Excel:
If you save this output in a file named input.csv and open it with Excel, you'll get your desired spreadsheet. Then, if .csv format isn't suitable, you can have Excel save it to a file in .xlsx format.
Note that you can give this script multiple input files which will still produce a single .csv output file. You might, however, run into ARG_MAX limitations on your system if you replace the last line of the script with:
Code:
}' *.txt
with over a thousand .txt files. I'm sure you can figure out how to group sets of .txt files into suitable chunks and combine the resulting output files (stripping off the header in all output files except the first one) to get a single, large .csv file to feed to Excel.
Last edited by Don Cragun; 01-12-2014 at 01:48 PM..
Reason: Fix typos, add note.
This User Gave Thanks to Don Cragun For This Post:
Hi All,
Am needing advise on how to convert xls file to xlsx format on Solaris unix command line or scripting. I tried searching online but it looks like I need to either use Perl packages of Excel or Python packages or some other 3rd party tool. Problem is to install any of these will require... (2 Replies)
Hi Team,
I have created a script to output DB Query data to a text file. It displays output as follows for 2 different queries appended to same file.
I want help to convert this data to xls format so that the output of first query is in 1 tab and the other in second tab. Please help.
... (14 Replies)
i have installed CENTOS in VMware. I want the linux command to run in CENTOS.
The command is to select data's from Event table(our table name) convert into .xls and mail every 2hrs. Can you please help me in writing this script.
Thanks,
Shobana (1 Reply)
Hi All,
I need to convert an exel spreadsheet into a SAS dataset,
and the following format change is needed. Please help, this is too complex
for a biologist.
Let me describe the input.
1st row is generation.1st column in keyword 'generation', starting 2nd column there are 5... (9 Replies)
Hi,
I have two text files. Need to create a third text file extracting specific data from first two existing files..
Text File 1: Format contains:
SQL*Loader: Release 10.2.0.1.0 - Production on Wed Aug 4 21:06:34 2010
some text ............so on...and somwhere text like:
Record 1:... (1 Reply)
Hi,
I have the following text file:
8 T1mapping_flip02 ok 128 108 30 1 665000-000008-000001.dcm
9 T1mapping_flip05 ok 128 108 30 1 665000-000009-000001.dcm
10 T1mapping_flip10 ok 128 108 30 1 665000-000010-000001.dcm
11 T1mapping_flip15 ok 128 108 30... (2 Replies)
I think I know the answer to this :rolleyes: but thought I'd ask anyway. You never know.
Does anyone know of a program or utility that will run on any unix platform and convert Microsoft Excel files to ascii/plain text files that unix can understand ?
Thanks in advance.
Floyd (3 Replies)
Hi,
Could anyone help me in changing a tabular format output to comma seperated file pls in K-sh. Its very urgent.
E.g : username empid
------------------------
sri 123
to
username,empid
sri,123
Thanks,
Hema:confused: (2 Replies)