Shell Programming and Scripting

Hello Team,

I have the following details in a txt file (please note the spaces and tabs)

Code:

C1                           C2                 C3
------------------ --------------- -------------
abc, xyz                    2                   8
pqr                           2                   3
egf, uvw a                 4                   2

I wish to convert this file into a .xls file with C1 (all the text under it, included) as Column A, C2 as Column B and C3 as Column C, in the spreadsheet

Example : In Column A, we must have abc,xyx ; pqr ; egf,uvw a in Row 1,2,3 respectively alongwith their respective values in for C2 and C3 in columns B and C respectively

Could you please suggest / help me in this.

Thanks,
Haider

Please use code tags as required by forum rules!

I can't see <TAB>s, just spaces; should there be <TAB> field delimiters, why don't you replace those by semicolons and read that file in as a .csv file?

I tried using code. However, i got a message mentioning about restricted visibility

Adding the code for better understanding

---------- Post updated at 12:57 PM ---------- Previous update was at 12:56 PM ----------

Could you please elaborate your thought with an example.

Assuming those wide spaces in post#1 to be <TAB>s, try e.g.

Code:

tr '\t' ';' < file
C1;C2;C3
------------------;---------------;-------------
abc, xyz;2;8
pqr;2;3
egf, uvw a;4;2

and read in the result as a .csv file.

Hello

What do you mean by that?

Regarding your added code:

Code:

Last edited by Don Cragun; 22 Minutes Ago at 19:55.. Reason: Add CODE tags for the 2nd time in this post~

It still has no tabs, just space chars.

And sharing thoughts, what have you tried so far?

You ask us to note spaces and tabs, but you didn't use CODE tags (which are required for us to be able to distinguish tabs and multiple spaces from single spaces). You got an email message and an infraction explaining how to use CODE tags, and Corona688 edited your message to add CODE tags for you. You then edited your post again throwing away the CODE tags Corona688 was nice enough to add for you. I have added them for you again. Please review the following before editing any more of your existing posts or creating any new posts:

Moderator's Comments:

To keep the forums high quality for all users, please take the time to format your posts correctly. First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags [code] and [/code] by hand.) Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red. Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Then note that there are no tab characters at all in the first post in this thread and note that the spacing in the 1st column of the input you say you have does not match the spacing in your description of the output that follows (is removing the space after the comma accidental or intentional).

You haven't told us what operating system or shell you're using, and some of the things you're trying to do might be easier on some operating systems. Some versions of perl with some add-on packages can produce Microsoft proprietary Excel files, but, as RudiC suggested, you can produce a character separated values file using a semicolon as the field separator and Excel will be able to load your spreadsheet from that file. For example, you could try something like:

Code:

awk -F'[[:space:]][[:space:]]+' 'NR > 2 {print $1, $2, $3}' OFS=";" input.txt > output.csv

or, if you want to get rid of a space after a comma in the 1st field:

Code:

awk -F'[[:space:]][[:space:]]+' 'NR > 2 {gsub(", ", ",", $1); print $1, $2, $3}' OFS=";" input.txt > output.csv

If you want to try either of the above on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.

Thank you.

the code :

Code:

awk -F'[[:space:]][[:space:]]+' 'NR > 2 {print $1, $2, $3}' OFS=";" input.txt

has worked for me

Now I wish to have the ";" de-limited output converted into a .xls file with the following columns :

Code:

C1      C2      C3

where data from the output ";" de-limited file would be filled in the respective columns.

I wish to send this excel as an attachment in an e-mail.

OS details : SUSE Linux Enterprise Server 11 SP1 (x86_64)
Shell details : /bin/bash