Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Parsing a CSV File

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Parsing a CSV File
# 8  
Old 01-26-2010
my example reads only 3 fields and you have 6 in the file
# 9  
Old 01-26-2010
Quote:
Originally Posted by frans
my example reads only 3 fields and you have 6 in the file
Right, but I removed the irrelevant stuff. it now only has 3 fields (hostname, Date, Status).

I'm understanding that the IFS (internal field separator) is being designated as a comma (,) and that is what allows the 'read' command to separate the 3 fields. Then if it matches the "Expired" status, it skips the entry. If it doesn't match, then it goes through each row and echos each field.

Is it possible to instead of printing the output to the screen, to maybe export it to a new file, with the entries separated by commas.

I know this sounds trivial, I'm just trying to get some basics down.
# 10  
Old 01-26-2010
Hi, try this:

Code:
#!/bin/bash                                                                                                                                   
IFS=','
while read ID HOST UNKNOWN NAME DATE STATUS;
do
    [ "$STATUS"	= "Expired" ] && continue
    echo "$HOST - $(ping -c1 $HOST | sed 's/^[^(]*(\([^)]*\)).*/\1/; q') - $(date)"
done <file.csv

# 11  
Old 01-26-2010
^^That is good, and it sorta worked, but I found something much easier to identify an IP for a hostname:

Code:
`dig +short www.example.com`

That returns only the IP, nothing else. Works extremely well for my needs.

So now, this is the point where I'm at. I went back to my original CSV file, because I cheated and opened it up in Excel, which put quotes around everything, and it was getting annoying.

Code:
#!/bin/bash                                                                                                                                   
IFS=','
#1,WWW.EXAMPLE.COM,,SSL Web Server certificate-1Year,07-Nov-09,Expired
while read NUMBER HOST BLANK TYPE DATE STATUS;
do 
    DATE=`echo $DATE | date -d $DATE +%s`
    HOST=`echo $HOST | tr '[:upper:]' '[:lower:]'`
    [ "$STATUS" = "Expired" ] && continue
    IP=`dig +short $HOST | head -1`
    echo "$DATE $HOST $IP $STATUS"
done <certs


Anyone have an idea about how I can sort each Host by the Date field?

Thanks for your help guys.


Edit: I changed it so each entry is printed on one line. I also figured that changing the date to Unix time would be the easiest to sort by. Any other ideas? How can I sort all the lines by date?
Last edited by dzl; 01-26-2010 at 05:17 PM..
# 12  
Old 01-26-2010
The best would be to convert the date in a 'sortable' format like
YYYY-MM-DD or in Unix timestamp.
Code:
man date

to check the possibilities of your date command.
As date is the first field, a simple
Code:
sort file1 > file2

would do the job
# 13  
Old 01-26-2010
awk is your friend :-).

Code:
awk -F, '{ print $5","$1","$2","$4","$6 }' foo.csv | sort

Convert the date into a sortable format (timestamp) and stream this into your script.

Code:
<$(awk -F, '{ print $5","$1","$2","$4","$6 }' foo.csv | sort)
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help with Parsing a CSV File

Hello All, I have an input CSV file like below, where first row data can be in different position after every run of the tool, i.e. pzTest in below example is in column 1, but it can be also in 3 column and same for all the headers in the first row. pzTest, pzExtract, pxUpdate, pzInfo... (1 Reply)
Discussion started by: asirohi
1 Replies

2. Shell Programming and Scripting

Csv file parsing and validating

Hi, I have basic knowledge on unix shell scripting(not an expert). My requirement is reading the csv file using the schema defined in the configuration file and if the condition is not mached then move the unmatched record to a error file and matched good records into other file. In brief: ... (43 Replies)
Discussion started by: shree11
43 Replies

3. Shell Programming and Scripting

Parsing csv file and pass to a variable

Hi, Newbie here and I need some help to parse a csv file that contains fields separated by ",". What I need to achieve here is, read the 1 line file and extract 240 fields and pass to a variable and then read the next 240 fields and pass to a variable, over and over. If anyone can assist that... (4 Replies)
Discussion started by: tmslixx
4 Replies

4. Shell Programming and Scripting

Help required in parsing a csv file

Hi Members, I am stuck with the following problem. Request your kind help I have an csv file which contains, 1 header record, data records and 1 footer record. Sample is as below Contents of cm_update_file_101010.csv -------------------------------------------------- ... (6 Replies)
Discussion started by: ramakanth_burra
6 Replies

5. Shell Programming and Scripting

Parsing a CSV file and deleting all rows on condition

Hello list, I am working on a csv file which contains two fields per record which contain IP addresses. What I am trying to do is find records which have identical fields(IP addresses) which occur 4(four) times, and if they do, delete all records with that specific identical field(ip address). ... (4 Replies)
Discussion started by: landossa
4 Replies

6. Shell Programming and Scripting

Parsing complicated CSV file with sed

Yes, there is a great doc out there that discusses parsing csv files with sed, and this topic has been covered before but not enough to answer my question (unix.com forums). I'm trying to parse a CSV file that has optional quotes like the following: "Apple","Apples, are fun",3.60,4.4,"I... (3 Replies)
Discussion started by: analog999
3 Replies

7. Shell Programming and Scripting

2 problems: Mailing CSV file / parsing CSV for display

I have been trying to find a good solution for this seemingly simple task for 2 days, and I'm giving up and posting a thread. I hope someone can help me out! I'm on HPUX, using sqlplus, mailx, awk, have some other tools available, but can't install stuff that isn't already in place (without a... (6 Replies)
Discussion started by: soldstatic
6 Replies

8. Shell Programming and Scripting

CSV file parsing and validation

I have a CSV file that needs to through two seperate processes (in the end there will be 2 files (Dload.unl and Tload.unl and we'll say the input file name is mass.csv). I have a processfile() function that will call the process Dload funtion. In Dload I want to read mass.csv into Dload and then... (1 Reply)
Discussion started by: dolo21taf
1 Replies

9. Shell Programming and Scripting

Parsing a csv file

I am trying to parse a csv file in the below 'name-value pair' format and then use the values corresponding to the name. Type:G,Instance:instance1,FunctionalID:funcid,Env:dev,AppName:appname... (6 Replies)
Discussion started by: chiru_h
6 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