Is there a way to remove a string from a line of a csv using sed in bash scripts?

Question

I have a scv file that is currently formatted like this:

INSERT INTO `kindle_reviews` VALUES (stuff I want); INSERT INTO `kindle_reviews` VALUES (stuff I want);

I would like to obtain the (stuff I want) and remove the 'INSERT INTO kindle_reviews VALUES ' part of every line.

Some command I tried on terminal:

sed -i -e 's/INSERT INTO 'kindle_reviews' VALUES //g' Untitled.csv

This did not work due to this error:

sed: 1: "s|</td><td><INSERT INTO ...": unterminated substitute pattern

Is it possible to remove a substring on every line of the csv file? Thank you very much

Good that you have shown your efforts, please do add samples of input and expected output too in your question and let us know then. — RavinderSingh13
– RavinderSingh13, Commented Dec 20, 2019 at 4:08
The output I would like to get are the values that are inside the brackets. The data inside the brackets are in the form of a csv. This csv is what I would like to extract. — danaq
– danaq, Commented Dec 20, 2019 at 4:09

Mark Reed · Accepted Answer · 2019-12-20 04:47:00Z

2

The problem is that you can't have apostrophes (') inside a string that is itself delimited by apostrophes. Use something like this:

sed -i -e 's/INSERT INTO '\''kindle_reviews'\'' VALUES //g' Untitled.csv

But are you sure those are apostrophes? Your sample line has backquotes (`) instead:

sed -i -e 's/INSERT INTO `kindle_reviews` VALUES //g' Untitled.csv

And the backquotes make it valid MySQL; the version with apostrophes is not. (You could also find double-quotes, e.g. INSERT INTO "kindle_reviews".)

If you want just the part between the parentheses (brackets), that'd be more like this:

sed -i -e \
  's/INSERT INTO `kindle_reviews` VALUES (\(.*\)).*$/\1/'

But only if everything between the parentheses is on a single line.

However, if want to get data out of a MySQL table into a CSV file, I don't know why you'd be dumping it as SQL INSERT commands in the first place. If you have access to the original database, you can export it directly as a CSV with something like this:

SELECT *
  FROM `kindle_reviews` 
  INTO OUTFILE '/tmp/kindle_reviews.csv' 
  FIELDS ENCLOSED BY '"' 
  TERMINATED BY ';' 
  ESCAPED BY '"' 
  LINES TERMINATED BY '\r\n';

edited Dec 20, 2019 at 4:47

answered Dec 20, 2019 at 4:10

Mark Reed

96k17 gold badges149 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

danaq Over a year ago

Yup, the csv we got from our mysql gives us the msql commands. I am trying to remove these commands such that we have a 'clean' csv file without the commands. I have tried the above commands, but the file remains unaltered. The command runs without any error though.

Mark Reed Over a year ago

See edit. The actual MySQL, like your first line, probably has backquotes (`), not apostrophes (').

danaq Over a year ago

You are right! I did what you said and got the output that I wanted. Thank you so much for your help!

danaq Over a year ago

Also, with regards to your sql commands, the problem we faced were permission issues. There was no way to run the sql commands on terminal because we did not have the proper permissions. That is why we resorted to csv dumping.

Collectives™ on Stack Overflow

Is there a way to remove a string from a line of a csv using sed in bash scripts?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related