LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 07-03-2013, 11:06 AM   #1
panic
LQ Newbie
 
Registered: Aug 2011
Posts: 13

Rep: Reputation: Disabled
Parsing CSV data in CentOS


Hello

I have a csv file that comes into my CentOS server that needs to be manipulated/parsed before it goes back out.

I've been doing a lot of searching and I'm not finding a way to copy some of the data (in each row). The file will come in with several rows but I'll only list one here for simplicity.

As in example a row will look like this:

test@test.com, testuser, first_name, last_name, test@test.com, active

What I need to manipulate it to be:

test@test.com, test@test.com, testuser, first_name, last_name, active

I'm not seeing a way to copy this first value in the row to also being the second value in the row AND remove that same value from being the fifth value in the row. It *might* be possible for me to get the input without the email address as the fifth value in each row, but I would still need to copy it to the second value. There is no way around that part.

Thanks for the read. As always any help is appreciated!
 
Old 07-03-2013, 11:12 AM   #2
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-20-live-usb/ aix
Posts: 5,026

Rep: Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845
awk ?
 
Old 07-03-2013, 01:19 PM   #3
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,243

Rep: Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684
Perl?

Ruby?
 
Old 07-03-2013, 02:19 PM   #4
panic
LQ Newbie
 
Registered: Aug 2011
Posts: 13

Original Poster
Rep: Reputation: Disabled
I worked my way around it.

I used awk to pull the first entry of the line and saved that as a variable. Then I used sed to write that variable as the first entry of the line. Lastly I used cut --complement to pull the 6th column out and leave the rest

So for anyone else looking to do something like this:
#make a variable of the first entry
var=`(awk '{print $1}' test.csv)`

#place the variable as the first entry
sed -i '${s/^/'"$var/}" test.csv

#remove the 6th column delimited by a comma and send to a finalized version
cut --complement -f 6 -d, test.csv >>final_test.csv

Hope this helps someone else also. Thanks guys
 
Old 07-03-2013, 05:03 PM   #5
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-20-live-usb/ aix
Posts: 5,026

Rep: Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845
Code:
[schneidz@hyper ~]$ awk -F , '{print $1 "," $1 "," $2 "," $3 "," $4 "," $6}' panic.txt 
test@test.com,test@test.com, testuser, first_name, last_name, active
 
Old 07-04-2013, 04:31 AM   #6
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,243

Rep: Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684
Code:
awk -F, '{$5=$6;NF=5;$0=$1 FS $0}$1=$1' OFS="," file
 
Old 07-08-2013, 09:05 AM   #7
panic
LQ Newbie
 
Registered: Aug 2011
Posts: 13

Original Poster
Rep: Reputation: Disabled
or just make it look easy

One liners are so much more elegant. I think that I had sed on the brain too much to reduce it to what I had as the example line and tried to continue with that to further parse the file. I should have instead focused on awk more. I must admit that neither of these near languages are my forte at all.

Thanks for all the help again.
 
Old 07-08-2013, 10:01 AM   #8
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,243

Rep: Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684
sed still works, just takes a little more finesse:
Code:
sed -rn 's/([^ ]*)(.*)\1 (.*)/\1\1\2\3/p' file
Of course if second email address can be different, it will need a little more poking
 
Old 07-08-2013, 10:44 AM   #9
panic
LQ Newbie
 
Registered: Aug 2011
Posts: 13

Original Poster
Rep: Reputation: Disabled
Ha! wow

why don't you just give me your phone number and I'll start calling you instead of wasting 3-4 hours of my time. (j/k)

That's beautiful and you are without a doubt a better man than me

Thanks grail
 
  


Reply

Tags
awk, csv, parsing, sed


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Parsing a ..csv file devnull10 Programming 6 06-20-2013 04:15 AM
Parsing data and generating a CSV file Striketh Programming 4 11-04-2011 08:15 AM
Help with simple CSV parsing nilleso Programming 10 09-23-2010 12:23 AM
Parsing a pseudo CSV file. sharky Programming 8 11-03-2008 11:47 PM
Parsing XLS or CSV in Perl - what and what not me_the_apprentice Programming 13 02-28-2006 04:44 AM


All times are GMT -5. The time now is 11:02 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration