Create csv from four disparate files


Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
Create csv from four disparate files

Hey everyone,
So newbie when it comes to scripting and lurked on these pages for some time as I slowly learn. I'd like to thank everyone for taking the time to share their knowledge, and would like to think I've picked up a something from these forums.
What I'm trying to do, is create a csv from four disparate files containing data.


Here's my current script. Its meant to have multiple files from multiple hosts, hence the loop.
I am limited to the tool set on AIX, and cannot use any GNU tools.

Code:
#!/bin/ksh
set -A allHosts $(ls -1 *.out | awk -F\. '{print $1}' | sort -u | uniq)
echo "host,accounts,max,pwd,standard"
for h in ${allHosts[@]}; do
        col1=$(cat ./${h}.account.out)
        col2=$(cat ./${h}.max.out)
        col3=$(cat ./${h}.pwd.out)
        col4=$(cat ./${h}.standard.out |  sed -e 's/^[ \t]*//')
        echo "${h}","${col1}","${col2}","${col3}","${col4}"
        unset col1
        unset names
        unset col2
        unset col3
        unset col4
done

File are setup like:
host1.max.out
Code:
9

host1.account.out
Code:
root
user1
user2

host1.pwd.out
Code:
abc pwd=

host1.standard.out
Code:
standard:
        user = true
        expires = 0
        core_path = on
        default_roles =

Results I'm current receiving:
Code:
host,accounts,max,pwd,standard
host1,root
user1
user2,9,abc pwd=,standard:
user = true
expires = 0
core_path = on
default_roles =

Desired output:
Code:
host,account,max,pwd,standard
host1,root,9,abc pwd=,standard:
,user1,,,user = true
,user2,,,expires = 0
,,,,core_path = on
 ,,,,default_roles =


Thank you for your time and my apologies if this has been asked before, but my searching abilities has failed me.
# 2  
Try the below codes and integrate into your script.
Code:
#!/bin/sh
#By default array string tokenizer considered as space, But we need entire line. 
# So whitespace replaced as _SPACE_
F1=( `cat host1.account.out | sed 's/ /_SPACE_/g'  ` )
F2=( `cat host1.max.out | sed  's/ /_SPACE_/g'  ` )
F3=( `cat host1.pwd.out  | sed  's/ /_SPACE_/g'  ` )
F4=( `cat host1.standard.out | sed  's/ /_SPACE_/g'  ` )
h='host1'
i=0
# get maximum number of lines from the above files and iterate here
while [ $i -lt ${#F4[@]} ]
do
        echo "$h",${F1[$i]},${F2[$i]},${F3[$i]},${F4[$i]} |  tr -d '\r' | sed -E 's/(_SPACE_)+/ /g'
        i=`expr $i + 1`
done

This User Gave Thanks to k_manimuthu For This Post:
# 3  
How about
Code:
IFS=. read H REST <<EOF
$(echo *.out)
EOF
echo "host,accounts,max,pwd,standard"; echo $H | paste -d, - *.out
host,accounts,max,pwd,standard
host1,root,9,abc pwd=,standard:
,user1,,,        user = true
,user2,,,        expires = 0
,,,,        core_path = on
,,,,        default_roles =

Pipe result through sed 's/, \+/,/' to get rid of the residual spaces from *standard*.out.
This User Gave Thanks to RudiC For This Post:
# 4  
Quote:
Originally Posted by k_manimuthu
Try the below codes and integrate into your script.
Code:
#!/bin/sh
#By default array string tokenizer considered as space, But we need entire line. 
# So whitespace replaced as _SPACE_
F1=( `cat host1.account.out | sed 's/ /_SPACE_/g'  ` )
F2=( `cat host1.max.out | sed  's/ /_SPACE_/g'  ` )
F3=( `cat host1.pwd.out  | sed  's/ /_SPACE_/g'  ` )
F4=( `cat host1.standard.out | sed  's/ /_SPACE_/g'  ` )
h='host1'
i=0
# get maximum number of lines from the above files and iterate here
while [ $i -lt ${#F4[@]} ]
do
        echo "$h",${F1[$i]},${F2[$i]},${F3[$i]},${F4[$i]} |  tr -d '\r' | sed -E 's/(_SPACE_)+/ /g'
        i=`expr $i + 1`
done

OH, et max lines and iterate! I like! YES!!!!





Quote:
Originally Posted by RudiC
How about
Code:
IFS=. read H REST <<EOF
$(echo *.out)
EOF
echo "host,accounts,max,pwd,standard"; echo $H | paste -d, - *.out
host,accounts,max,pwd,standard
host1,root,9,abc pwd=,standard:
,user1,,,        user = true
,user2,,,        expires = 0
,,,,        core_path = on
 ,,,,        default_roles =

Pipe result through sed 's/, \+/,/' to get rid of the residual spaces from *standard*.out.

I seem to be having a hard time getting this to process multiple files from multiple host. If there's only host1.*.out files its fine, as soon as host2.*.out files exist it gets all funky.



Example of there's host2.*.out files:
Code:
host,accounts,max,pwd,standard
host1,root,9,abc pwd=,standard:,root,9,abc pwd=,standard:
,user1,,,        user = true,user1,,,        user = true
,user2,,,       expires = 0,user2,,,    expires = 0
,,,,    core_path = on,,,,      core_path = on
,,,,    default_roles =,,,,     default_roles =


Thank you both!
# 5  
Combining your original script with k_manimuthu's solution you might end up with something like this:

Code:
#!/bin/ksh
set -A allHosts $(ls -1 *.out | awk -F\. '{print $1}' | sort -u | uniq)
echo "host,accounts,max,pwd,standard"
for h in ${allHosts[@]}; do
    OLDIFS="$IFS"
    IFS="
"   # Actual newline in quotes
    set -A F1 `cat ${h}.account.out`
    set -A F2 `cat ${h}.max.out`
    set -A F3 `cat ${h}.pwd.out`
    set -A F4 `sed -e 's/^ *//' -e 's/  */ /' ${h}.standard.out`
    IFS="$OLDIFS"
    i=0
    # get maximum number of lines from the above files and iterate here
    while [ $i -lt ${#F4[@]} ]
    do
        echo "$h",${F1[$i]},${F2[$i]},${F3[$i]},${F4[$i]}
        let i=i+1
        h="" # Blank host for 2nd and subsequent lines
    done
done

This User Gave Thanks to Chubler_XL For This Post:
# 6  
OK, multiple hostn files. Try
Code:
for FN in *.out
  do    H=${FN%%.*}
        if [ ! "$H" = "$OH" ]
          then  OH=$H
                {
                echo "host,accounts,max,pwd,standard"
                echo $H | paste -d, - $H*.out | sed 's/, \+/,/'
                } > $H.result
        fi
  done
---------- host1.result: ----------

host,accounts,max,pwd,standard
host1,root,9,abc pwd=,standard:
,user1,,,user = true
,user2,,,expires = 0
,,,,core_path = on
,,,,default_roles =

---------- host2.result: ----------

host,accounts,max,pwd,standard
host2,root,9,abc pwd=,standard:
,user1,,,user = true
,user2,,,expires = 0
,,,,core_path = on
,,,,default_roles =

This User Gave Thanks to RudiC For This Post:
# 7  
Quote:
Originally Posted by Chubler_XL
Combining your original script with k_manimuthu's solution you might end up with something like this:

Code:
#!/bin/ksh
set -A allHosts $(ls -1 *.out | awk -F\. '{print $1}' | sort -u | uniq)
echo "host,accounts,max,pwd,standard"
for h in ${allHosts[@]}; do
    OLDIFS="$IFS"
    IFS="
"   # Actual newline in quotes
    set -A F1 `cat ${h}.account.out`
    set -A F2 `cat ${h}.max.out`
    set -A F3 `cat ${h}.pwd.out`
    set -A F4 `sed -e 's/^ *//' -e 's/  */ /' ${h}.standard.out`
    IFS="$OLDIFS"
    i=0
    # get maximum number of lines from the above files and iterate here
    while [ $i -lt ${#F4[@]} ]
    do
        echo "$h",${F1[$i]},${F2[$i]},${F3[$i]},${F4[$i]}
        let i=i+1
        h="" # Blank host for 2nd and subsequent lines
    done
 done

Chubler_XL,
Thank you for taking the time to reply. Think I may have kludged something together like this? Eh, I have a lot of temp files floating about at the moment! Ok, way too many temp files floating about as I tried out diff trains of thought. But not as neat as your solution.


Quote:
Originally Posted by RudiC
OK, multiple hostn files. Try
Code:
for FN in *.out
  do    H=${FN%%.*}
        if [ ! "$H" = "$OH" ]
          then  OH=$H
                {
                echo "host,accounts,max,pwd,standard"
                echo $H | paste -d, - $H*.out | sed 's/, \+/,/'
                } > $H.result
        fi
  done

RudiC,
Could you please break it down for me what is going on here:
Code:
for FN in *.out
  do    H=${FN%%.*}
        if [ ! "$H" = "$OH" ]
          then  OH=$H

I sorta understand it, Assign H to FN and strip off everything until the .out ?
And I'm sorry, my mind is failing at the if statement there. Trying to pick up what I can Smilie
Thank you.
Login or Register to Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Compare 2 files of csv file and match column data and create a new csv file of them
refrain
Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07...... Shell Programming and Scripting
8
Shell Programming and Scripting
How to create or convert to pdf files from csv files using shell script?
ssk250
Hi, Can anyone help me how to convert a .csv file to a .pdf file using shell script Thanks... Shell Programming and Scripting
2
Shell Programming and Scripting
How to create a .csv file from 2 different .txt files?
alisrpp
Hi, I need to create a .csv file from information that i have in two different tab delimited .txt file. I just want to select some of the columns of each .txt file and paste them into a .cvs file. My files look like: File 1 transcript_id Seq. Description Seq. Length ...... UNIX for Dummies Questions & Answers
2
UNIX for Dummies Questions & Answers
How to create a CSV File by reading fields from separate files
mayanksargoch
SHELL SCRIPT Hi, I have 3 separate files within a folder. Every File contains data in a single column like File1 contains data mayank sushant dheeraj File2 contains DSA_AT MG_AT FLAT_09 File3 contains data 123123 232323... Shell Programming and Scripting
2
Shell Programming and Scripting
Merging files to create CSV file
Ravendark
Hi, I have different files of the same type, as: Time: 100 snr: 88 perf: 10 other: 222 Each of these files are created periodically. What I need to do is to merge all of them into one but having the following form:... Shell Programming and Scripting
2
Shell Programming and Scripting