Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk/sed/something else for csv file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk/sed/something else for csv file
# 1  
Old 05-19-2011
awk/sed/something else for csv file
Hi,

I have a filename.csv in which there are 3 colums, ie:
Code:
Name ; prefixnumber ; number
root ; 020 ; 1234567
user1,2,3 ; 070 ; 7654321

What I want is to merge colum 2 and 3 that it becomes 0201234567 or even better +31201234567 so the country number is used and drop the leading 0.
Names that have commas in their name (yes, it happens...) need those commas replaced with dashes like user1-2-3.

I've been trying with sed and awk, splitting the file up and then join it again, but my knowledge is just unsufficient Smilie


Thanks in advance,

Niels

Moderator's Comments:
Mod Comment Please use [code] and [/code] tags when posting code, data or logs etc. to preserve formatting and enhance readability, thanks.
Last edited by zaxxon; 05-19-2011 at 08:48 AM.. Reason: code tags
# 2  
Old 05-19-2011
An awk:
Code:
awk '{gsub(/ /,"");gsub(/,/,"-",$1);sub(/^0/,"+31",$2);print $1":"$2$3}' FS=";" file

Last edited by Klashxx; 05-19-2011 at 09:16 AM.. Reason: gsub for first field
This User Gave Thanks to Klashxx For This Post:
necron
# 3  
Old 05-19-2011
Hero! Thanks a million Smilie
The only thing I changed is the
Code:
print $1":"$2$3}

I replaced the : with ; so it's still a ; seperated file for the import.

Oh one thing though, I noticed the spaces in the first colum are missing now as well, for instance:
Code:
Just A Name ;06 ;12346578 ;

Became:
Code:
JustAName;+3161234578

When I remove the first gsub it leaves the spaces in the names alone, but the number output gets spaced as well, like:
Code:
Just A Name ;+316 1234578

Anyway to leave the spaces in the first colum alone, but remove in the 2nd and 3rd?
Last edited by necron; 05-19-2011 at 10:07 AM.. Reason: Codetags...
# 4  
Old 05-19-2011
You can use the FS:
Code:
 awk '{gsub(/ /,"");gsub(/,/,"-",$1);sub(/^0/,"+31",$2);print $1FS$2$3}' FS=";" file

# 5  
Old 05-19-2011
I've solved it this way:
Code:
awk '{gsub(/ /,"",$2);gsub(/,/,"-",$1);sub(/^0/,"+31",$2);print $1FS$2$3}' FS=";" file

If you have a better solution let me know, but this works fine too.
Last edited by necron; 05-19-2011 at 10:21 AM.. Reason: solution
# 6  
Old 05-19-2011
Hi,

Here a 'Perl' script:
Code:
$ perl -F";" -lane 'next if $. == 1 ; ($num = join "", @F[1,2]) =~ s/\s*//g ;$F[0] =~ tr/,/-/ ; $num =~ s/^0/+31/; print (join "; ", $F[0], $num)' infile

Regards,
Birei
# 7  
Old 05-19-2011
Hi Birei,

Appriciated, but that would involve installing perl (not default on FreeBSD) and since awk is 'base' I'll stick to that.
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

Datestamp format 2nd change in csv file (awk or sed)

I have a csv file formatted like this: 2014-08-21 18:06:26,A,B,12345,123,C,1232,26/08/14 18:07and I'm trying to change it to MM/DD/YYYY HH:MM for both occurances. I have got this: awk -F, 'NR <=1 {print;next}{"date +%d/%m/%Y\" \"%H:%m -d\""$1 "\""| getline dte;$1=dte}1' OFS="," test.csvThis... (6 Replies)
Discussion started by: say170
6 Replies

3. Shell Programming and Scripting

Combined sed+awk for lookup csv file

have written a combined sed+awk to perform a lookup operation which works but looking to enhance it. looking to match a record using any of the comma separated values + return selected fields from the record - including the field header. so: cat foo make,model,engine,trim,value... (6 Replies)
Discussion started by: jack.bauer
6 Replies

4. Shell Programming and Scripting

Replacing FQDN by hostnames in a CSV file with sed & awk

Hello, Beginning with shell scipting, I'm trying to find in a csv file, the lines where the field related to hostname is displayed as an FQDN intead the hostname. (some lines are correct) and the to correct that inside the file: Novell,11.0,UNIX Server,bscpsiws02,TxffnX1tX1HiDoyBerrzWA==... (2 Replies)
Discussion started by: Wonto
2 Replies

5. Shell Programming and Scripting

Converting txt file into CSV using awk or sed

Hello folks I have a txt file of information about journal articles from different fields. I need to convert this information into a format that is easier for computers to manipulate for some research that I'm doing on how articles are cited. The file has some header information and then details... (8 Replies)
Discussion started by: ksk
8 Replies

6. Shell Programming and Scripting

HELP with AWK or SED. Need to replace the commas between double quotes in CSV file

Hello experts, I need to validate a csv file which contains data like this: Sample.csv "ABCD","I",23,0,9,,"23/12/2012","OK","Street,State, 91135",0 "ABCD","I",23,0,9,,"23/12/2012","OK","Street,State, 91135",0 I just need to check if all the records contain exactly the number of... (5 Replies)
Discussion started by: shell_boy23
5 Replies

7. Shell Programming and Scripting

Use grep sed or awk to extract string from log file and put into CSV

I'd like to copy strings from a log file and put them into a CSV. The strings could be on different line numbers, depending on size of log. Example Log File: File = foo.bat Date = 11/11/11 User = Foo Bar Size = 1024 ... CSV should look like: "foo.bat","11/11/11","Foo Bar","1024" (7 Replies)
Discussion started by: chipperuga
7 Replies

8. Shell Programming and Scripting

Using awk/sed in handling csv file.

Please study the below script and the output Script: echo "Minimum ${host} ${process} response time=${min} ms" >> ${OUTDIR}/${OUTFILE}; echo "Maximum ${host} ${process} response time=${max} ms" >> ${OUTDIR}/${OUTFILE}; echo "Average ${host} ${process} response time=${avg} ms" >>... (0 Replies)
Discussion started by: ajincoep
0 Replies

9. Shell Programming and Scripting

Need help with a script to process a CSV file using SED and AWK

I get a CSV file every day with 2 columns and multiple rows ex: date1,date2 ( both the fields are varchar fields) This data has to be updated in a table which is being done manually and i want to automate that. 1. I have to select all the data from the prod table( 2 columns { date1,date2}) into... (4 Replies)
Discussion started by: kkb
4 Replies

10. Shell Programming and Scripting

Updating a line in a large csv file, with sed/awk?

I have an extremely large csv file that I need to search the second field, and upon matches update the last field... I can pull the line with awk.. but apparently you cant use awk to directly update the file? So im curious if I can use sed to do this... The good news is the field I want to... (5 Replies)
Discussion started by: trey85stang
5 Replies
Login or Register to Ask a Question