process text data and convert into cvc/excel format

Question

I got one requirement to process text file and collect two fields and convert those fields from column to row , finally convert to spread sheet. I tried to follow below URL An efficient way to transpose a file in Bash. Unfortunately i was not able understand code properly.

Here is my text file

cat oralinux1_20191015.log
date collected          ||11-11-2019 03:58:54 GMT   ||
host name               || oralinux1                ||
kernel version          || 2.6.32-754.23.1.el6      || Good 
kernel architecture     || x86_64                   || Good 
hardware type           || virtual                  || Good
No.of CPU               || 4                        || Good
Installed Mem           || 16 Good                  || Good
/u01 file pct free      || 28 %                     || Good

my Requirement is

i need to pick 1st column and last column from the log and convert row as columns as csv/spread sheet 

Date collected,host_name,kernal version , kernel architecture , hardware type , No.of.cpu, installed_mem, 
                         Good,             good,                  good,          good,      good

So technically 1st filed of the file will become 1st row and corresponding value in last column shoud be second row

Kindly help me on this issue. To be honest, I am not a extreme shell script developer and started my career recently. Kindly help me on this. I am ok with shell script or python code, since i have started learning.

Thank you,

Durga

So, what did you try based on the info you found in the link you added? — Luuk, Sep 17 '20 at 17:05
```date collected || host name || kernel version || kerneal architecture || hardware type || No.of CPU || Good Installed Mem || Good /u01 file pct free || Good BEGIN { FS="||" OFS="," } { printf "%s%s", (FNR>1 ? OFS : ""), $ARGIND } ENDFILE { print "" if (ARGIND < NF) { ARGV[ARGC] = FILENAME ARGC++ } } ``` created temp file with required fields and used the above code. Looks working . But i need to insure that code is correct — Durga, Sep 17 '20 at 17:44
Your sample output is not the cvs output you want. The two first rows have no value "Good", if you really want what you describe your second line should be `,,Good, Good etc` — thanasisp, Sep 17 '20 at 19:40

score 0 · Answer 1 · answered Sep 17 '20 at 18:36

0

Simplify your inpout and your output becomes a lot easier.

while IFS='|' read -a line;   # the sed eliminates redundant space and pipes
do line1="$line1${line[0]},"; # accumulate & comma-delimit headers
   line2="$line2${line[2]},"; # accumulate & comma-delimit col3 values
done< <( sed -E 's, *\|+ *,\|,g' oralinux1_20191015.log ) # clean input
printf "%s\n" "$line1" "$line2" > relevant.csv # add newlines

answered Sep 17 '20 at 18:36

Paul Hodges

13,382
1
17
36

Thank you, Let me open new request. – Durga Sep 21 '20 at 19:27

score 0 · Answer 2 · answered Sep 18 '20 at 14:39

Assumptions:

OPs sample output has several typos (eg, capitalization, underscore in 2nd column name, extra spaces, etc)
leading/trailing spaces to be removed from fields before writing to csv
first 2 columns have empty values but should be written to csv as ,,
last line of the input file is to be ignored (ie, it doesn't show up in OPs sample output)

One awk solution:

awk -F "|" '
BEGIN               { sep="" }                 # initial output field separator is empty string

/u01 file pct free/ { next }                   # ignore
                    {
                      gsub(/^[ \t]+/,"",$1)    # strip leading  whitespace from field 1
                      gsub(/[ \t]+$/,"",$1)    # strip training whitespace from field 1

                      gsub(/^[ \t]+/,"",$5)    # strip leading  whitespace from field 5
                      gsub(/[ \t]+$/,"",$5)    # strip training whitespace from field 5

                      labels=labels""sep""$1   # concatenate current field 1 onto variable
                      values=values""sep""$5   # concatenate current field 5 onto variable
                      sep=","                  # redefined separator as "," for rest of processing
                    }
END                 { printf "%s\n%s\n",       # generate output
                          labels, values
                    }
' oralinux1_20191015.log

Running the above against the OPs sample input file generates:

date collected,host name,kernel version,kernel architecture,hardware type,No.of CPU,Installed Mem
,,Good,Good,Good,Good,Good

Hello ALL, Thank you so much for code help. Here problem is I have thousands of files to process. I need common heading and process all the files and load in CSV as one lines for each file. — Durga, Sep 21 '20 at 19:11
sounds like you're changing your initial requirements; I suggest you either a) start a new question - making sure to provide (more) details or b) consider reworking/editing this question (above) - keeping in mind that the answers you've received so far will likely need to be deleted and/or heavily edited/rewritten — markp-fuso, Sep 21 '20 at 19:14

process text data and convert into cvc/excel format

2 Answers2