Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help with pulling / filtering data from a .csv

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help with pulling / filtering data from a .csv
# 1  
Old 09-09-2010
Question Help with pulling / filtering data from a .csv
Good day Gurus,

I have a csv file that contains an inventory of active servers. This csv file contains a well over a hundred systems (IBM, SUN, HP). It also contains those systems details. See below for an example

Code:
hostA,invver,1.02,20100430
hostA,date,08/30/2010,06:18
hostA,use,"Unknown Server Use"
hostA,os,"5.2.0.0","5200-10-04-0750","IBM,7029-6C3","0004DBAC4C00"
hostA,obp,Sat Aug 28 20:34:22 2010
hostA,mem,2048 MB
hostA,platform,IBM,7029-6C3
hostA,serial,"104DBAC"
hostA,hostid,"0xcfc0213b"
hostB,invver,1.05,20100526
hostB,date,08/30/2010,06:18
hostB,use,"unknown-server"
hostB,os,"5.10","142900-12","sun4u","SUNW,SPARC-Enterprise"
hostB,obp,"4.24.11"
hostB,mem,65536
hostB,platform,"Sun Microsystems sun4u Sun SPARC Enterprise M5000 Server"
hostB,serial,"BEF0949C7D"
hostB,hostid,"854574e2"

All I really need is the data from hostname, server platform, serial, and hostid fields. I have a script that will do that.

Code:
#!/usr/bin/bash

nawk -F'[,]' '/platform/ {print $1":", $3}; /hostid/ {print $0};
                 /serial/ {print $0}
        ' inventory.csv | sed -e 's/\"//g' > host.txt

The output from my code:
Code:
hostA: IBM
hostA,serial,104DBAC
hostA,hostid,0xcfc0213b
hostB: Sun Microsystems sun4u Sun SPARC Enterprise M5000 Server
hostB,serial,BEF0949C7D
hostB,hostid,854574e2

This code works great. But what I stumped on is, how to pull the data only on the SUN inventory? If anyone have any ideas let me know. I really don't care code I use.

Thanks,
Luffy Smilie
# 2  
Old 09-09-2010
As you have the host as a key, you can:
Code:
# cat TestFile.txt
hostA: IBM
hostA,serial,104DBAC
hostA,hostid,0xcfc0213b
hostB: Sun Microsystems sun4u Sun SPARC Enterprise M5000 Server
hostB,serial,BEF0949C7D
hostB,hostid,854574e2

# cat HostInv.sh
hostInvFile="TestFile.txt"
egrep -i 'sun' "${hostInvFile}" | awk -F":" '{print $1}' | \
while read hostKey
do
	egrep '^'"${hostKey}"'' "${hostInvFile}"
done

Of course, there are several ways to do the same thing!

I hope it helps.

Regads.

---------- Post updated at 13:38 ---------- Previous update was at 13:34 ----------
Quote:
Originally Posted by felipe.vinturin
As you have the host as a key, you can:
Code:
# cat TestFile.txt
hostA: IBM
hostA,serial,104DBAC
hostA,hostid,0xcfc0213b
hostB: Sun Microsystems sun4u Sun SPARC Enterprise M5000 Server
hostB,serial,BEF0949C7D
hostB,hostid,854574e2

# cat HostInv.sh
hostInvFile="TestFile.txt"
egrep -i 'sun' "${hostInvFile}" | awk -F":" '{print $1}' | \
while read hostKey
do
	egrep '^'"${hostKey}"'' "${hostInvFile}"
done

Of course, there are several ways to do the same thing!

I hope it helps.

Regads.
Maybe remove the egrep by changing the awk:
Code:
awk -F":" '/[S|s]un/ {print $1}' "${hostInvFile}"

# 3  
Old 09-09-2010
Code:
$
$
$ cat f12
hostA,invver,1.02,20100430
hostA,date,08/30/2010,06:18
hostA,use,"Unknown Server Use"
hostA,os,"5.2.0.0","5200-10-04-0750","IBM,7029-6C3","0004DBAC4C00"
hostA,obp,Sat Aug 28 20:34:22 2010
hostA,mem,2048 MB
hostA,platform,IBM,7029-6C3
hostA,serial,"104DBAC"
hostA,hostid,"0xcfc0213b"
hostB,invver,1.05,20100526
hostB,date,08/30/2010,06:18
hostB,use,"unknown-server"
hostB,os,"5.10","142900-12","sun4u","SUNW,SPARC-Enterprise"
hostB,obp,"4.24.11"
hostB,mem,65536
hostB,platform,"Sun Microsystems sun4u Sun SPARC Enterprise M5000 Server"
hostB,serial,"BEF0949C7D"
hostB,hostid,"854574e2"
hostC,platform,"Solaris Server"
hostC,serial,"XXX9999Y9Z"
hostC,hostid,"123456x7"
hostD,platform,"sun microsystems server"
hostD,serial,"YYY8888Y8Z"
hostD,hostid,"555555x5"
$
$ # I've made the assumption that "platform", "serial" and "hostid" occur in that order (in different lines) for Sun servers in your file.
$ # They do not have to be on consecutive lines for this script to work.
$
$ awk -F, '/platform.*[Ss]un/{print $1":", $3; x=1}; /serial/ && x==1{print $0}; /hostid/ && x==1{print $0; x=0}' f12
hostB: "Sun Microsystems sun4u Sun SPARC Enterprise M5000 Server"
hostB,serial,"BEF0949C7D"
hostB,hostid,"854574e2"
hostD: "sun microsystems server"
hostD,serial,"YYY8888Y8Z"
hostD,hostid,"555555x5"
$
$

tyler_durden
Last edited by durden_tyler; 09-09-2010 at 02:21 PM..
# 4  
Old 09-09-2010
Another approach:
Code:
awk -F, '/platform/ && /[sS]un/{$0=$1 ":" $3;c=3}c-->0{gsub("\"","");print}' file

# 5  
Old 09-09-2010
Great thanks guys all of your solutions work. Thanks for the time and effort...
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need help with csv filtering

Hello everyone, i am stuck with a task i was meant to do so i came here. So i have a .csv file which structure is : year;temperature;precipitation 2012;32;483 2006;28;517 ... I want to note that it is in fact ";" not a space, which a new file named <old-name>-new.txt, the first line must ... (2 Replies)
Discussion started by: Needhelp123
2 Replies

2. Shell Programming and Scripting

Pulling Data, Then Moving to the Next File

I'm scanning a list of emails- I need to pull 2 pieces of data, then move to the next file: Sender's Email Address Email Date I need these to be outputted into a single column- separated by a ",". Like this: Email1's Address, Email1's Date Stamp Email2's Address, Email2's Date Stamp... (4 Replies)
Discussion started by: sudo
4 Replies

3. Shell Programming and Scripting

BASH- Need help pulling data from .emlx

Hello, fellow computer junkies. First time poster! My boss wrote an application (Mavericks 10.9, Mountain Lion 10.8) that checks a user's security settings. The user runs the application, then it spits out an email that is sent back to our inbox showing the results. On our end, we have a mail rule... (5 Replies)
Discussion started by: sudo
5 Replies

4. Shell Programming and Scripting

Filtering data from text to csv

Hello, Is there a way to filerter data from a text file as shown below to a Column e.g. hostname nfsmount as two separate column. Currently I could get hostname and the mount is appearing below.. using this script #! /bin/bash for i in `cat fqdn.txt` do echo "$i ............ " >>... (3 Replies)
Discussion started by: Cy Pqa
3 Replies

5. Shell Programming and Scripting

Pulling data from xml

Hi there, Please could anyone help with this. I have an xml file that contains repeating values eg <Rule name> AAAAA <Action> BBBBB </Action> <Data> CCCCC </Data> <Type> DDDDD </Type> </Rule name> <Rule name> A1A1A1A1 <Action> B1B1B1B1 </Action> <Data> C1C1C1C </Data> <Type>... (4 Replies)
Discussion started by: ssideel
4 Replies

6. Shell Programming and Scripting

Reading 2 CSV files and filtering data based on group

I have two CSV files in the following format: First file: GroupID, PID:TID, IP, Port Sample data: 0,1000:11,127.0.0.1,445 0,-1:-1,127.0.0.1,800 1,1000:11,127.0.0.1,445 1,-1:-1,127.0.0.1,900 2,1000:11,127.0.0.1,445 2,-1:-1,180.0.0.3,900 Second file: IP,Port,PID Sample data... (6 Replies)
Discussion started by: rakesh_arxmind
6 Replies

7. Shell Programming and Scripting

pulling different fields from a csv file

Hi, I have a requirment where I need to pull different columns from a .csv file. Here is the sample of the csv file. account,item,flag1,flag2,flag3,flag4,flag5,......feed,tran I will be have a config.txt file which will have the following information. item,flag5,flag10,feed,tran... (2 Replies)
Discussion started by: akdevula
2 Replies

8. Shell Programming and Scripting

SFTP to server, pulling data and removing the data

Hi all, I have the following script, but are not too sure about the syntax to complete the script. In essence, the script must connect to a SFTP server at a client site with username and password located in a file on my server. Then change to the appropriate directory. Pull the data to the... (1 Reply)
Discussion started by: codenjanod
1 Replies

9. Shell Programming and Scripting

Pulling data and following lines from file

I saw a few posts close to what i want to do, but they didn't look like they would work exactly.. or I need to think out of the box on this. I have a file that I keep server stats in for my own performance analysis. this file has the output from many commands in it (uptime, vmstats, ps, swap... (2 Replies)
Discussion started by: MizzGail
2 Replies
Login or Register to Ask a Question