FORMAT OF CSV FILE under unix environment.

Shell Programming and Scripting
1

hi
I unload the table results from oracle to csv file foramt.
i need increse the width of each column using unix commands
could you pl tell me how to increase the width of each column to spefic width uisng sed unix command or na other unix commands

i have file name called report.csv
inside this file
here is the contents
here are record of csv file
pl tell me how to increase the column width from this.
aaaaaaaaaaa,bbbb,cccccccccc,dddd,eeeeeeeeeeee
bbbbb,ccccc,zzzzzzzzzzz,vvvvvvvvv,mmmm

pl tell me what is script for sovle this problem.

thx,N.

2

Raosurya,
Your requirement:

leaves us with questions:

  • Which columns do you want to increase.
  • How many positions do you want to increase.
  • What justification do you want:
    . Left.
    . Right.
    . Center.
  • What filling character do you want.
3 
awk -F"," '{printf "%-20s %-20s %-20s %-20s %-20s\n",$1,$2,$3,$4,$5}' <file-name>
4

Thanks for your quick reply.
i am sorry about that.
i want to incrase all the columns and all the records in that file
the result should be in new file.
i want increase right of each column around 15 positions on the first column,
5 postions on 2nd column, 4 positions on 3rd column, 4th and 5th sould be 2 postions.

the result should be stored in new file

the filling character will be space.

pl reply fast. thanks,N.

5

Raosurya,
If you have a csv file, what is the need for padding the fields?
Is this a homework?

6

can you paste some sample from your file and the way you want it to be formatted?

7

NO.
what is my problem is i sent this CSV FILE to excel spread sheet.
when open the csv file, i could not see whole column width. it showing excel default column width. so, i cannot see whole column. i need to manually darg he column to see column result. so, i want to increase the column width before sening to e-mail attachment.
that is my problem.

thx for your help. pl write the script

thx.N.

8

If this is all you need, then add a new line to the beginning
of the file with your specification.

9

Dear raosurya, (as someone used to say)
Pls do a little experiment:

  1. take your .csv file
  2. hand edit one of the fields - increase the 'padding' to the desired length
  3. save the .csv file
  4. bring the .csv file in Excell
  5. see if your columns have adjusted their 'width'
  6. if they did - it's worth writing a 'script'. if they ain't - it ain't. It's got nothing to do with padding, but rather the Excell way of adjusting the width.

my $.02

10

I am desparate to fix the problem.
Need help.

replied:
awk -F"," '{printf "%-20s %-20s %-20s %-20s %-20s\n",$1,$2,$3,$4,$5}' <file-name>

how to add spaces for the above script towards right of each column about 15 position and aslo save into new file

i think it will work. i need to test it now.help me.
thx. N.

11

Raosurya,
As you know, we are all unix volunteers here and we are here
to help people solve their problems.
But under no circunstances we work under pressure.
You have to give members a chance to work on your issue keeping
in mind the following:
"Lack of planing on your side does not constitute emergency on our side"

In any event, here is your solution:

while read mLine
do
  set -- `echo $mLine | sed 's/,/ /g'`
  printf "%-15s %-5s %-4s %-2s %-2s\n" $1 $2 $3 $4 $5
done < input_file
12

I am sorry about that.

Thanks for your reply.
N.

13

I don't think that padding with spaces will help, since Excel will still use a default column width when opening a csv file. You could convert your csv file to html, which Excel will try to render properly, e.g....

awk 'BEGIN{
        FS=","
        print "<HTML><BODY><TABLE>"
     }
     {
        printf "<TR>"
        for(i=1;i<=NF;i++)
          printf "<TD>%s</TD>", $i
        print "</TR>"
     }
     END{
        print "</TABLE></BODY></HTML>"
     }
     ' file1.csv > file2.xls
14

In Excel click the gray box at the top left hand corner to highlight the whole table, then double click one of the divider bars.

Excel will then magically readjust all the column widths to fit the data.

15

I know that.
I am doing automation.There should not be any manual intervention.
Thanks,N.

16

Hi Ygor,

i used this code:

awk 'BEGIN{
FS=","
print "<HTML><BODY><TABLE>"
}
{
printf "<TR>"
for(i=1;i<=NF;i++)
printf "<TD>%s</TD>", $i
print "</TR>"
}
END{
print "</TABLE></BODY></HTML>"
}
' file1.csv > file2.xls

after i ran the script,
i got no attchment of any file.
what could be the problem.
here is my code:

spool /test/out/report.csv
select name ||','||
status ||','||
begin||','||
end||','||
from emptab
where name
a.name =b.name
order by name;
spool off
i got this resulst into csv file
then i code

/usr/bin/uuencode report.csv report.csv > attachment_report.csv

then i put your code right here

awk 'BEGIN{
FS=","
print "<HTML><BODY><TABLE>"
}
{
printf "<TR>"
for(i=1;i<=NF;i++)
printf "<TD>%s</TD>", $i
print "</TR>"
}
END{
print "</TABLE></BODY></HTML>"
}
' attachment_report.csv > attachment_report.xls

then
mailx -S report 'firstname.lastname@gmail.com' < attachment_report.xls

then i got e-mail with no attchment.

what could be wrong.
help
thx.N.

17

Then write an Excel macro to do it.

18

Hi shell life
here is you written.
while read mLine
do
set -- `echo $mLine | sed 's/,/ /g'`
printf "%-15s %-5s %-4s %-2s %-2s\n" $1 $2 $3 $4 $5
done < input_file

but, i need output in to another file,
what is use of set --( two dashes here ). pl explain.
thx. N.

19

i do not know excel marco.
Please let me know how to write excel macro for this problem.
i really appreciate for this.
thx.
N.

20

Try this:

Columns("A:E").EntireColumn.AutoFit

Regards