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


from each line?

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

  • 1
    Given you can easily edit .CSV in Notepad++, I'd use a macro in Notepad++ or multi-line delete, or replace with null... Mar 1 '19 at 13:12
  • thx but I am working in linux and this has to be scripted;
    – pkaramol
    Mar 1 '19 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
    Mar 1 '19 at 13:33
  • No it will be an entire csv whose first column will be like this
    – pkaramol
    Mar 1 '19 at 13:34

You can remove the first field with sed:

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

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)

  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.


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.