mjhjmtu

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

(12,'hello','this girl,is lovely(adorable rn 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 rn 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

This awk command can do what you want:

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

Result:

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

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 rn 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 rn 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.