2

How can I separate the line as below in a csv file:

(12,'hello','this girl,is lovely(adorable \r\n actually)',goodbye),(13,'hello','this fruit,is super tasty (sweet actually)',goodbye)

to the below as 2 different rows:

(12,'hello','this girl,is lovely(adorable \r\n actually)',goodbye)
(13,'hello','this fruit,is super tasty (sweet actually)',goodbye)

I tried using:

awk -F"[()]" '{print $2}' test.csv 

but it didn't work and lost a few rows.

This data is actually a SQL query and I need to extract the data and convert it into different rows using the comma after ) and before ( as row seperator

1

This awk command can do what you want:

awk -F '),' '{ print $1")" "\n" $2}' source.csv

Result:

(12,'hello','this girl,is lovely(adorable \r\n actually)',goodbye)
(13,'hello','this fruit,is super tasty (sweet actually)',goodbye)
2

With GNU sed (and your sample input saved in a file called ./input):

$ sed -e 's/),(/)\n(/g' ./input
(12,'hello','this girl,is lovely(adorable \r\n actually)',goodbye)
(13,'hello','this fruit,is super tasty (sweet actually)',goodbye)

This changes the comma in every ),( to a newline.

WARNING: If that character sequence occurs inside your actual data, it will be changed there too.

You could do the same in awk, but there's little or no advantage over using sed:

$ awk 'gsub(/\),\(/,")\n(",$0)' ./input
(12,'hello','this girl,is lovely(adorable \r\n actually)',goodbye)
(13,'hello','this fruit,is super tasty (sweet actually)',goodbye)

Unless you're going to do further processing on the input line that requires awk features, just use sed.

  • Thanks alot for your help..really appreciate it! thanks :) – Derek Jan 22 '18 at 18:54
0

cat test.csv | tr -d "()" | sed 's/goodbye/goodbye\n/g'

  • sed translates the string goodbye into newline (\n). You can exclude the goodbye with sed 's/goodbye/\n/g' the "g" is to do it with ALL lines not just the first match.
  • tr is optional to delete parenthesis (you can use tr to translate them to something else without deleting them)
0

Tried in python

a=(12,'hello','this girl,is lovely(adorable \r\n actually)',goodbye),(13,'hello','this fruit,is super tasty (sweet actually)',goodbye)

#!/usr/bin/python
import re
b=a.split("),")
for i in range(0,len(b),1):
    if i == 0:
        d=")"
        print b[i]+d
    else:
        print b[i]

output

(12,'hello','this girl,is lovely(adorable \r\n actually)',goodbye)
(13,'hello','this fruit,is super tasty (sweet actually)',goodbye)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.