Visit Our UNIX and Linux User Community

How do I read/find/replace fields in a csv datafile?

Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers How do I read/find/replace fields in a csv datafile?
# 1  
Old 06-20-2008
How do I read/find/replace fields in a csv datafile?


I'm somewhat a novice here so please be patient. My stumbling block when loading csvs into ORACLE tables is this:

I need to read a csv datafile, check several fields in each line, and if any of stated fields contain A ZERO only [instead of YYYYMONDD date field expected] then replace it with a null/blank character.

I had a go at:

cat <datafile> | awk $5 == 0 { } END { print }';
cat <datafile> | awk $8 == 0 { } END { print }'; ..etc..

..alas it didn't work..

all advice/comments very much appreciated

# 2  
Old 06-20-2008
Post a sample of the csv datafile and the desired output.

# 3  
Old 06-20-2008
thanks for the quick reply..

the csv's first two lines look like:

13426,Williams,Robyn,Robyn,20031106,0,0,20080421,0,NASP39,Casual Sport & Recreation Ass,43.24,FACI,E,FASP,SUPP,SP,NASP35,20080421,0,100,FACI,V,FASP,SUPP,SP,0,0,Ms,FT,FT,SUB
15721,Clinton,Hilary,Hilary,20041101,hs0jo,,20080601,0,HSL236,Project Manager,56.76,700,A,700,ACAD,PB,V32636,20071207,20080731,0.36,700,V,HSEN,VLEC,FL,0,0,Dr,PT,FT,SUB

..and the desired output is valid entry into our ORACLE database via sqlldr.. (apologies if thats not what you meant..)

thanks again..


Last edited by Yogesh Sawant; 06-20-2008 at 06:54 AM.. Reason: added code tags
# 4  
Old 06-20-2008
Try this:

awk 'BEGIN{FS=OFS=","}{for(i=1;i<=NF;i++){if($i=="0"){$i=""}}}1' file

# 5  
Old 06-20-2008 error is being returned when I try to run this either by itself or in a script - am I not running it right?

$ cat SLsTester_DataFile.csv | awk BEGIN{FS=OFS=","}{for(i=1;i<=NF;i++){if($i=="0"){$i=""}}}1' file;

..the error given reads:

-bash: syntax error near unexpected token `('

your assistance is very much appreciated..

regards, Steven
# 6  
Old 06-20-2008
Don't pipe the result of the cat command to the file. This is sufficient:

awk 'BEGIN{FS=OFS=","}{for(i=1;i<=NF;i++){if($i=="0"){$i=""}}}1' SLsTester_DataFile.csv

You can redirect the output to a file as follow:

awk 'BEGIN{FS=OFS=","}{for(i=1;i<=NF;i++){if($i=="0"){$i=""}}}1' SLsTester_DataFile.csv > NewFile

# 7  
Old 06-20-2008
..its stilll not running Im afraid..

$ awk 'BEGIN{FS=OFS=","}{for(i=1;i<=NF;i++){if($i=="0"){$i=""}}}1' SLsTester_DataFile.csv > SLsOUtputFile.csv;
awk: syntax error near line 1
awk: bailing out near line 1
/applmgr@fintest:~/finliveappl/su/11.5.0/payroll/gl $

..I've also tried running it within a script but it errors the same...

Thing is, as I have several date fields in each line ($5 $8 $9 $19 $20 $27) that may or may not need changing (exactly the same however, from zero to null), will this script simply swap ALL fields it finds with a zero only populated or will I need to specify field numbers?

Apologies for the questions but this is good for me to learn..



Previous Thread | Next Thread
Test Your Knowledge in Computers #135
Difficulty: Easy
As of 2006, approximately five percent of the Linux kernel was written by Linus Torvalds himself
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Find fields and replace using awk

Code: Using ksh Var1=`awk -F";" {print $1}' Input2.txt` cat Input1.txt | awk -F";" '{$3="Var1"}' > Output.txt (13 Replies)
Discussion started by: Roozo
13 Replies

2. Shell Programming and Scripting

Read in numbers from a datafile

Hi, I want to be able to read numbers from many files which have the same general form as follows: C3H8 4.032258004031807E-002 Phi = 1.000000E+00 Tau = 5.749E+00 sL0 = 3.805542E+01 dL0 = 1.514926E-02 Tb = 2.328291E+03 Tu = 3.450E+02 Alpha = ... (3 Replies)
Discussion started by: lost.identity
3 Replies

3. Shell Programming and Scripting

Find and replace variables using a csv table

I have a flat file (template) where I want to replace variables based upon a value in another file (csv). The variables in the template are named %VAR_X_z% The values are in the csv file and X is field 0 of each line and y field 1 and up. Example of the csv: Badidas, 13.00, 12.00, 11.00,... (8 Replies)
Discussion started by: biscayne
8 Replies

4. Shell Programming and Scripting

Find and Replace in multiple fields using awk

Hi, Say I have a record "1|22| | |". In which the third and fourth fields are <space> alone. I have to replace the <Space> with <null>. Input: "1|22| | |" --> "1|22|<space> |<space> |" Expected output: "1|22|||" --> "1|22|<null> |<null>|" I tried: echo "1|22| | |" | awk -F... (4 Replies)
Discussion started by: machomaddy
4 Replies

5. Shell Programming and Scripting

Read Field from file1 and find and replace in file2

Hi All, I have file1 line below: $myName$|xxx Now I need to read the file1 and find for $myName$ in file2 and replace with xxx file1: $myName$|xxx file2: My name is $myName$ expected output in file2 after executing the script is below: my name is xxx Thanks, (8 Replies)
Discussion started by: gdevadas
8 Replies

6. Shell Programming and Scripting

Converting .xls into .csv and find & Replace

Hi All, Please give me the solution to the following ASAP. 1) Converting the .xls into .csv Script i tried, mv hello.xls hello.csv The above given script converting the .xls file into .csv successfully. But after i run the below unix command I am no able to open the .csv file, its giving... (4 Replies)
Discussion started by: velava
4 Replies

7. Shell Programming and Scripting

How to read and parse the content of csv file containing # as delimeter into fields using Bash?

#!/bin/bash i=0 cat 1.csv | while read fileline do echo "$fileline" IFS="#" flds=( $fileline ) nrofflds=${#flds} echo "noof fields$nrofflds" fld=0 while do echo "noof counter$fld" echo "$nrofflds" #fld1="${flds}" trying to store the content of line to fields but i... (4 Replies)
Discussion started by: barani75
4 Replies

8. Shell Programming and Scripting

find & replace comma in a .csv file.

HI, Please find the text below. I receive a .csv file on server. I need the comma(,) in the second column to be replaced by a semi-colon( ; ). How to do it. Please help. Sample text: "1","lastname1,firstname1","xxxxxx","19/10/2009","23/10/2009","0","N","Leave"... (2 Replies)
Discussion started by: libin4u2000
2 Replies

9. Shell Programming and Scripting

Combine a datafile with Master datafile, emergent!

Hi guys, my supervisor has asked me to solve the problem in 7 days, I've taken 3 days to think about it but couldn't figure out any idea. Please give me some thoughts with the following problem, I have index.database that has only index date: 1994 1995 1996 1997 1998 1999 I have... (6 Replies)
Discussion started by: onthetopo
6 Replies

10. Shell Programming and Scripting

replace one section in a datafile

Hi: First, this is not a homework problem. I just need enough of a hint to get this going... My datafile ( is made up of 10 sections. Each section begins with & and with && So it looks like this:------------------------------------- &section1 ...etc... && &section2 ...etc...... (4 Replies)
Discussion started by: Paprika
4 Replies

Featured Tech Videos