INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

manipulate column string in a csv file

manipulate column string in a csv file

(OP)
Hi,
I've been struggling to write a simple script to manipulate a column in a large csv file but i can't get any further.
Any help is greatly appreciated!

csv file
2013-12-6T02:05:08+01:00,0,0,0,0,0,258,0,0,0,0,0,258,0,0,0,0,0,258,0,0,0,0,0,258,false
2013-12-11T02:10:08+01:00,0,0,0,0,0,283,0,0,0,0,0,283,0,0,0,0,0,283,0,0,0,0,0,283,false
2013-12-11T02:15:08+01:00,11,3662,0,9,3898,302,11,3662,0,9,3898,302,11,3662,0,9,3898,302,11,3662,0,9,3898,302,false
2013-12-5T02:20:08+01:00,0,0,0,0,0,254,0,0,0,0,0,254,0,0,0,0,0,254,0,0,0,0,0,254,false
2013-12-11T02:25:08+01:00,0,0,0,0,0,1324,0,0,0,0,0,1324,0,0,0,0,0,1324,0,0,0,0,0,1324,false

In need to split the first column into 2  as date and time (remove the 'T' and '+01:00) and sort it so that :

2013-12-5,02:20:08,0,0,0,0,0,254,0,0,0,0,0,254,0,0,0,0,0,254,0,0,0,0,0,254,false
2013-12-6,02:05:08,0,0,0,0,0,258,0,0,0,0,0,258,0,0,0,0,0,258,0,0,0,0,0,258,false
2013-12-11,02:10:08,0,0,0,0,0,283,0,0,0,0,0,283,0,0,0,0,0,283,0,0,0,0,0,283,false
2013-12-11,02:15:08,11,3662,0,9,3898,302,11,3662,0,9,3898,302,11,3662,0,9,3898,302,11,3662,0,9,3898,302,false
2013-12-11,02:25:08,0,0,0,0,0,1324,0,0,0,0,0,1324,0,0,0,0,0,1324,0,0,0,0,0,1324,false

 

RE: manipulate column string in a csv file

(OP)
Thanks for the tip.

I was able to manage with sed.

#Replace "T" with ',' in column 1
sed -i 's/T/,/1' subok.csv
sed -i 's/+01:00,/,/1' subok.csv
sed -i 's/ime/time/1' subok.csv

#sort column 1 with column 2
sort -t\; -k 1 -k 2 subok.csv

my only problem now, is to sort skipping the first row?
Any suggestion?

Is it possible to combine these 4 command line is a single liner or in a perl script?


RE: manipulate column string in a csv file

CODE

sed 's/T\(.*\)+01:00,/,\1,/' subok.csv | sort -t- -k 1,1n -k2,2 -k3,3n 

RE: manipulate column string in a csv file

Hello,

Following may help on same.

CODE

sed 's/\(.*T\)\(.*\+\)\(.*\:00\,\)\(.*\)/\1 \2\,\4/g;s/T//g;s/\+//g' check_data_specific_form 

Where check_data_specific_form is the Input file name.

RE: manipulate column string in a csv file

Sorry just want to add the sorting stuff as it is suggested in previous posts same we can add after running script as follows.


CODE

ksh check_data_specific_form.ksh | sort -t- -k 1,1n -k2,2 -k3,3n 

Thanks,
R. Singh

RE: manipulate column string in a csv file

Here is all in all coede now.

CODE -->

sed 's/\(.*T\)\(.*\+\)\(.*\:00\,\)\(.*\)/\1 \2\,\4/g;s/T//g;s/\+//g' | sort -t- -k 3n 

Output will be as follows.


CODE -->

2013-12-5 02:20:08,0,0,0,0,0,254,0,0,0,0,0,254,0,0,0,0,0,254,0,0,0,0,0,254,false
2013-12-6 02:05:08,0,0,0,0,0,258,0,0,0,0,0,258,0,0,0,0,0,258,0,0,0,0,0,258,false
2013-12-11 02:10:08,0,0,0,0,0,283,0,0,0,0,0,283,0,0,0,0,0,283,0,0,0,0,0,283,false
2013-12-11 02:15:08,11,3662,0,9,3898,302,11,3662,0,9,3898,302,11,3662,0,9,3898,302,11,3662,0,9,3898,302,false
2013-12-11 02:25:08,0,0,0,0,0,1324,0,0,0,0,0,1324,0,0,0,0,0,1324,0,0,0,0,0,1324,false 

Thanks,
R. Singh

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close