-1

I have a CSV file I wish to edit before importing it into a SQLITE DB. It has thousands of lines in it and I want to copy part of the line and append it to the end with a pipe "|" so that it can easily delimited and imported into the DB.

The csv contains lines like:

989155126903533568|2018-04-25|14:52:14|GMT|report|"""Умственно отстал"" was checked -  http://steamcommunity.com/profiles/76561198402636850 …"|0|0|0|
989154874184085505|2018-04-25|14:51:14|GMT|report|"""Clavicus Vile"" was checked (8 reports) -  http://steamcommunity.com/profiles/76561198006267103 …"|0|0|0|
989154622890823685|2018-04-25|14:50:14|GMT|report|"""~TAKA~"" was checked (3 reports) -  http://steamcommunity.com/profiles/76561198161608591 …"|0|0|0|

I want to copy the 765 number and add it to the end of the line like the below:

989154622890823685|2018-04-25|14:50:14|GMT|report|"""~TAKA~"" was checked (3 reports) -  http://steamcommunity.com/profiles/76561198161608591 …"|0|0|0|76561198161608591

I'd like to do this for every line in the csv. So maybe a for loop would be required. I'm not sure.

1

sed solution:

sed -E 's/.*\/profiles\/([0-9]+).*/&\1/' file.csv

Sample output:

989155126903533568|2018-04-25|14:52:14|GMT|report|"""Умственно отстал"" was checked -  http://steamcommunity.com/profiles/76561198402636850 …"|0|0|0|76561198402636850
989154874184085505|2018-04-25|14:51:14|GMT|report|"""Clavicus Vile"" was checked (8 reports) -  http://steamcommunity.com/profiles/76561198006267103 …"|0|0|0|76561198006267103
989154622890823685|2018-04-25|14:50:14|GMT|report|"""~TAKA~"" was checked (3 reports) -  http://steamcommunity.com/profiles/76561198161608591 …"|0|0|0|76561198161608591
  • This works great. Especially as it removes the ... from the end of the number. Thanks for the answer – Blindy Apr 27 '18 at 6:18
  • 2
    @Blindy Hitting "accept answer" is the preferred way to say "thank you". – Philippos Apr 27 '18 at 10:12
0

With awk:

awk -F'["/]' '{print $0$(NF-1)}' infile > outfile

print the whole line $0 and second last field $(NF-1) where field separator -F is either set '[...]' of quote " or slash / from infile and save the result into outfile.

0
$ sed -E 'h;s/.*(http[^ ]*).*/\1/;s/.*\///;H;x;s/\n//' file
989155126903533568|2018-04-25|14:52:14|GMT|report|"""Умственно отстал"" was checked -  http://steamcommunity.com/profiles/76561198402636850 …"|0|0|0|76561198402636850
989154874184085505|2018-04-25|14:51:14|GMT|report|"""Clavicus Vile"" was checked (8 reports) -  http://steamcommunity.com/profiles/76561198006267103 …"|0|0|0|76561198006267103
989154622890823685|2018-04-25|14:50:14|GMT|report|"""~TAKA~"" was checked (3 reports) -  http://steamcommunity.com/profiles/76561198161608591 …"|0|0|0|76561198161608591

The sed script with annotations:

h                        # save a copy of the current line in the "hold space"
s/.*(http[^ ]*).*/\1/    # remove everything but the URL
s/.*\///                 # trim the URL so that only the last bit (the number) is left
H                        # add that last bit to the "hold space" (with a newline in-between)
x                        # swap the "hold space" and the "pattern space"
s/\n//                   # delete that inserted newline
                         # (implicit print at the end)

This assumes that the URL is always the only URL on the line and that it is always delimited by a space character.

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.