Format output

Shell Programming and Scripting
1

Hello Team,

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

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

2

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?

3

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.

4

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

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.

5

Hello

What do you mean by that?

Regarding your added 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?

6

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:

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:

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:

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 .

1 Like
7

Thank you.

the 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 :

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

8

Hello

With the command, i am getting an output for a few entries as :

abc, xyz;2;8
pqr;2 3;     <---- Incorrect entry
egf, uvw a;4;2

Please help

Thanks,
Haider

9

And what input are you giving that generates that output? (IN CODE TAGS, PLEASE!)

10

Hello,

I am trying a different approach now.

I am generating the output as an XML file.

<?xml version="1.0"?>
<ROWSET>
<ROW>
<t1>aaaaa</t1>
<t2>bbbbb</t2>
<t3>ccccccc</t3>
</ROW>
</ROWSET>

I wish to convert this XML into an excel (.xls) file using linux shell scripting.

Could you please let me know how to do so.

Thanks,
Haider