0

I have a csv file (created in excel file) whose first 2 lines are somehow like this:

"=HYPERLINK(""https://some.url/browse/ISSUE-3146"", ""ISSUE-3146"")","Bug","Related Case : Some description ",0.0,0,0,0,0,115734.466,21505.476,0,85.693,21404.944,14.839,0,0,0,94029.214,199.776
"=HYPERLINK(""https://some.url/browse/ISSUE-3145"", ""ISSUE-3145"")","Bug","Another description",1.0,0,0,0,0,115224.32,21329.679,0,2248.391,18584.597,496.691,0,0,0,91695.307,2199.334

Unfortunately (for reasons beyond the scope of this question) this file will be handed over to me like this.

Can I remove using bash (or ansible) this part

HYPERLINK(""https://some.url/browse/ISSUE-3146"",

from each line?

i.e. I want each first column to be:

ISSUE-XXXX
Improve this question
4
  • 1
    Given you can easily edit .CSV in Notepad++, I'd use a macro in Notepad++ or multi-line delete, or replace with null...
    – spikey_richie
    Commented Mar 1, 2019 at 13:12
  • thx but I am working in linux and this has to be scripted;
    – pkaramol
    Commented Mar 1, 2019 at 13:19
  • Is it always the first 2 lines you want to delete from every file? Not just the URL part? There are several regex's to match any URL in google...
    – Xen2050
    Commented Mar 1, 2019 at 13:33
  • No it will be an entire csv whose first column will be like this
    – pkaramol
    Commented Mar 1, 2019 at 13:34

3 Answers 3

Reset to default
1

You can remove the first field with sed:

sed -e 's/=HYPERLINK(""http[^,]*,//' /input/file > /output/file
Improve this answer
0

Take your data and place it in a text file that you save as file-type .csv.

Double-click the file and Excel will open it like:

enter image description here

The data you want is in column A, but it is in hyperlink form.

Copy column A and PasteSpecial Values back onto column A

(If you don't need the other columns, just delete them)

Improve this answer
0
  1. In the first column enter the text in A1
  2. In the second column in B1 enter the formula =len(A1)
  3. In the third column in C1 enter =find("/",A1)
  4. In the fourth column D1=find("/",$A$1,(C1+1))
  5. Copy D1 in E1,E2,E3,....etc.....right across.
  6. When you get #Value Error Stop

  7. In the column next to Error Cell enter =Right(A1,(B1-The last cell before Error))

  8. Thus String is found after removing all unwanted "/" characters.

Formulas can be copied down the rows. We can also use "ISNUMBER" function to check the condition and display the result.

I hope the reply may be found satisfactory.

Thanks.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .