LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)
-   -   Text file to Excel adding column (https://www.linuxquestions.org/questions/linux-newbie-8/text-file-to-excel-adding-column-922188/)

arjun_b 01-05-2012 06:08 AM

Text file to Excel adding column
 
Hi,
I am new to linux.
I am currently doing a script to write text file to excel file.I have may text files with 2 columns each.When i write excel file from one text file and try to append the next text file's data in excel , i am getting the datas in rows.. but i need them in column.

One thing is the first column in all text files are same.
so i would like to have an excel with first column once and the remaining columns(only 2nd column from remaining files).

i used the below script.
i had the text files - log1, log2, log3, log4
( echo "count, time1"; sed 's/\s\+/,/g' log1) >Log.csv
( echo "time2"; awk -F' ' '{ print $2 }' log2) >> Log.csv
( echo "time3"; awk -F' ' '{ print $2 }' log3) >> Log.csv
( echo "time4"; awk -F' ' '{ print $2 }' log3) >> Log.csv

allend 01-05-2012 07:14 AM

Welcome to LQ!

I think that the 'paste' command should achieve your desired result.
i.e.'paste log1 log2 log3 log4 > log.csv'

You may want to further format the file using 'colrm' to delete columns and 'sed' to change tab characters to comma space delimiters. This could also be handled within Excel.

It is a little difficult to be more precise without knowing the exact data format.

michaelk 01-05-2012 07:30 AM

The -n option will not output a newline.

http://linux.die.net/man/1/echo

colucix 01-05-2012 07:53 AM

Maybe a bit advanced for a novice (?), but let's unleash the power of awk:
Code:

awk '
BEGIN {
  _[0,0] = "count"
}

{
  _[0,ARGIND] = ("time" ARGIND)
  _[FNR,0] = $1
  _[FNR,ARGIND] = $2
}

END {
  for ( i = 0; i <= FNR; i++ ) {
    printf "%s", _[i,0]
    for ( j = 1; j <= ARGC-1; j++ )
      printf ",%s", _[i,j]
    printf "\n"
  }
}' log1 log2 log3 log4

A must-read, if you want to learn awk and understand the code, http://www.gnu.org/software/gawk/manual/gawk.html. :cool:


All times are GMT -5. The time now is 10:42 AM.