how to create csv file using shell script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting how to create csv file using shell script
# 1  
Old 03-16-2010
how to create csv file using shell script

I have a file in multiple directory which has some records in the following format

File: a/latest.txt , b/latest.txt, c/latest.txt ->

Name=Jhon
Age=27
Gender=M
Street=LA Road
Occupation=Service


I want to generate a csv file from the above file as follows

File: output.csv ->

27,M,Jhon,LA Road,Service


Can you please help me out to write a shell/perl script , as I want to upload this data into a database table.

Thanks.
# 2  
Old 03-16-2010
Something like this will do the csv part. You'll need a loop around your files and this code assumes one record per file - easy enough to change if that's not true

#!/usr/bin/perl
Code:
open(I, "t");
while (<I>) {
 chomp;
 @s=split /=/;
 $foo{$s[0]}=$s[1];
}
print "$foo{Age}, $foo{Gender}, $foo{Name}, etc.\n";
exit 0;

# 3  
Old 03-16-2010
Assuming all files have the same format
Code:
#!/bin/bash
for F in */latest.txt
do
    {
        read Name
        read Age
        read Gender
        read Street
        read Occupation
    } < $F
    echo "$Age,$Gender,$Name,$Street,$Occupation" >> outputfile
done

# 4  
Old 03-16-2010
Quote:
Originally Posted by frans
Assuming all files have the same format
Code:
#!/bin/bash
for F in */latest.txt
do
    {
        read Name
        read Age
        read Gender
        read Street
        read Occupation
    } < $F
    echo "$Age,$Gender,$Name,$Street,$Occupation" >> outputfile
done

Instead of "27,M,..." that will generate "Age=27,Gender=M,..."

A nitpick: There's nothing bash specific in that code. you can just use #!/bin/sh

Regards,
Alister

---------- Post updated at 03:04 PM ---------- Previous update was at 02:43 PM ----------

A tweaked version of frans' solution which outputs the desired format and can handle multiple records in a file (assuming each line in the file is part of a record, without any blank lines or any data not fitting the 5 line record pattern):
Code:
#!/bin/sh

IFS=''
for f in */latest.txt; do
    while read -r name; do 
        read -r age
        read -r gender
        read -r street
        read -r occupation
        echo "${age#*=},${gender#*=},${name#*=},${street#*=},${occupation#*=}" >> outputfile
    done < "$f"
done

# 5  
Old 03-16-2010
Hi.
Quote:
Originally Posted by frans
Assuming all files have the same format
Code:
#!/bin/bash
for F in */latest.txt
do
    {
        read Name
        read Age
        read Gender
        read Street
        read Occupation
    } < $F
    echo "$Age,$Gender,$Name,$Street,$Occupation" >> outputfile
done

The comment by alister is correct. Slightly changing your code:
Code:
#!/usr/bin/env bash

# @(#) s1	Demonstrate split name-value pairs, write csv-style.

# Infrastructure details, environment, commands for forum posts. 
set +o nounset
LC_ALL=C ; LANG=C ; export LC_ALL LANG
echo ; echo "Environment: LC_ALL = $LC_ALL, LANG = $LANG"
echo "(Versions displayed with local utility \"version\")"
c=$( ps | grep $$ | awk '{print $NF}' )
version >/dev/null 2>&1 && s=$(_eat $0 $1) || s=""
[ "$c" = "$s" ] && p="$s" || p="$c"
version >/dev/null 2>&1 && version "=o" $p
set -o nounset
echo

FILE=${1-data1}

# replace specimen with cat if not available.
specimen $FILE

echo " Results:"
oldifs="$IFS"
IFS="="
# for F in */latest.txt
for F in $FILE
do
  {
    read junk Name
    read junk Age
    read junk Gender
    read junk Street
    read junk Occupation
  } < $F
  # echo "$Age,$Gender,$Name,$Street,$Occupation" >> outputfile
  echo "$Age,$Gender,$Name,$Street,$Occupation" 
done

exit 0

producing:
Code:
% ./s1

Environment: LC_ALL = C, LANG = C
(Versions displayed with local utility "version")
OS, ker|rel, machine: Linux, 2.6.26-2-amd64, x86_64
Distribution        : Debian GNU/Linux 5.0 
GNU bash 3.2.39

Whole: 5 of 5 lines in data1
Name=Jhon
Age=27
Gender=M
Street=LA Road
Occupation=Service

 Results:
27,M,Jhon,LA Road,Service

See man bash for description of IFS.

cheers, drl
# 6  
Old 03-16-2010
Another approach:
Code:
for f in */latest.txt; do
    cut -d= -f2- "$f" | sed -n 'h;n;N;G;N;N;y/\n/,/;p' >> outputfile
done

# 7  
Old 03-16-2010
Code:
 awk -F "=" '/^Name/ {print ""; next} {printf "%s,", $2} ' */latest.txt

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk and sed script to create one output CSV file

Hi All , I would require your help to generate one output file after post processing of one CSV file as stated below This file is just a small cut from a big file . Big file is having 20000 lines PATTERN,pat0,pat1,pat2,pat3,pat4,pat5,pat6,pat7,pat8,pat9... (2 Replies)
Discussion started by: kshitij
2 Replies

2. Shell Programming and Scripting

Help with Shell Scrip in Masking particular columns in .csv file or .txt file using shell script

Hello Unix Shell Script Experts, I have a script that would mask the columns in .csv file or .txt file. First the script will untar the .zip files from Archive folder and processes into work folder and finally pushes the masked .csv files into Feed folder. Two parameters are passed ... (5 Replies)
Discussion started by: Mahesh G
5 Replies

3. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

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... (8 Replies)
Discussion started by: refrain
8 Replies

4. Shell Programming and Scripting

How to create or convert to pdf files from csv files using shell script?

Hi, Can anyone help me how to convert a .csv file to a .pdf file using shell script Thanks (2 Replies)
Discussion started by: ssk250
2 Replies

5. Shell Programming and Scripting

CSV File Creation Within Shell Script

Hi All, I am trying to create a CSV file within a shell script test.ksh and the code snippet is something like below: #!/usr/bin/ksh # Set required variables. . $HOME/.prof # Output file path Group1=/tmp/G1.csv Group2=/tmp/G2.csv Group3=/tmp/G3.csv $ORACLE_HOME/bin/sqlplus -s... (2 Replies)
Discussion started by: swasid
2 Replies

6. UNIX for Dummies Questions & Answers

How to Create excel file(.csv) using shell script?

Hi, i have shell script which compiles n number of test cases and execute them one by one. i want to create report in excel through script in which two columns namely "test id" and "release".second column have two subcolumns namely compiles and excutes. so i want first column should display test... (15 Replies)
Discussion started by: RamMore123
15 Replies

7. Shell Programming and Scripting

Script to create a CSV file

I created a script that will go out and so a "/sbin/chkconfig --list | egrep XXX" against a server list that would create an output file like the following example: ---------------------------------------------------------------------------------- SERVER1 RC_Script_1 0:off 1:off 2:off... (4 Replies)
Discussion started by: asnatlas
4 Replies

8. Shell Programming and Scripting

How to create multiline csv cell through shell script?

Hi, I have a text like the one given below status="Observation 1" read1="Source rows not load" read2="Score drop" I want to create a csv and mail it out in such a way that all three lines will be in a single cell but as three lines. For ex Col C1 ... (3 Replies)
Discussion started by: prasperl
3 Replies

9. Shell Programming and Scripting

ksh script to create a generic csv file from different source formats

Hi all, I have a requirement to create a "superset" file out of a number of different sources with some different and some same columns. We intend to have a manually updateable SuperSetCols.csv which would look like "ColA","ColB","ColC","ColD","ColE","ColF","ColG" so someday we may add... (3 Replies)
Discussion started by: Leedor
3 Replies

10. Shell Programming and Scripting

Help in parsing a CSV file with Shell script

I have a CSV file which contains number series as one of the fields. Some of the records of the field look like : 079661/3 I have to convert the above series as 079661 079662 079663 and store it as 3 different records. Looking for help on how to achieve this. Am a newbie at Shell... (10 Replies)
Discussion started by: mihirk
10 Replies
Login or Register to Ask a Question