-2

I have a large number of data in below format as a txt file. As you can see, each line has Title and then path. What is need here is, i need the data to be present next to each other in an excel - something like Title: Path:

Can someone please assist in resolve this?

Current result

Title:Projects and Ideas
Path:/content/en_us/
Title:buying-guide
Path:/content/en_us/buying-guide
Title:Choose Exterior Paint Colors
Path:/content/en_us/buying-guide/choose-exterior-paint-colors
Title:Water Softener Buying Guide
Path:/content/en_us/buying-guide/water-softener-buying-guide
Title:Presentation Page
Path:/content/en_us/buying-guide/presentation-page

Expected Result

Title:Projects and Ideas       Path:/content/en_us/
Title:buying-guide        Path:/content/en_us/buying-guide
Title:Choose Exterior Paint Colors Path:/content/en_us/buyingguide/choose
Title:Water Softener Buying Guide  Path:/content/en_us/buying-guide/water
Title:Presentation Page      Path:/content/en_us/buying-guide/presentation-page
schrodingerscatcuriosity
  • 12,396
Surendar
  • 35
  • 3
    I don't quite see how this is a Unix/Linux question. Have you tried the data import feature of Excel? – Plergux Feb 01 '21 at 07:50
  • Thanks i thought this can be achieved through some commands..Will expore more on the excel sheet side – Surendar Feb 01 '21 at 08:01
  • 1
    One of the problem is that you would need to specify the exact format that your Excel expects in order to correctly load the text file - and while researching what Excel expects, you may actually find it easier to configure the import of the file as it is now. Also, when asking this type of question please always include what you already tried and/or the tools you are using for the task, so that contributors can help you in a targetted way. – AdminBee Feb 01 '21 at 10:54
  • 2
    Does this answer your question? Text processing - join every two lines with commas – muru Feb 01 '21 at 10:59
  • ^Use comma, tab, semicolon, whatever and then import it to Excel with that character as the delimiter – muru Feb 01 '21 at 11:00
  • Thanks for the comments..I was able to achieve with few manual way in Notepad++ & Excel... First from Notepad++ i was able to in this way -
    Title:Projects and Ideas Path:/content/en_us/ then with excel delimiter it was able to separate Title & Path     – Surendar Feb 02 '21 at 12:27

1 Answers1

0

I assume you want the file tab separated so you can paste it as a table. So you can do something like this:

$ sed 'N;s/\n\(Path.*\)$/\t\1/' file
Title:Projects and Ideas    Path:/content/en_us/
Title:buying-guide  Path:/content/en_us/buying-guide
Title:Choose Exterior Paint Colors  Path:/content/en_us/buying-guide/choose-exterior-paint-colors
Title:Water Softener Buying Guide   Path:/content/en_us/buying-guide/water-softener-buying-guide
Title:Presentation Page Path:/content/en_us/buying-guide/presentation-page

Excel is off topic being not Unix/Linux, but the same question could be applied to Linux available office software like LibreOfiice Calc, hence my answer.

schrodingerscatcuriosity
  • 12,396
  • Thanks you so much for the response...It worked with above sed command and from there using excel delimiter i was able to separate Title & Path – Surendar Feb 02 '21 at 12:29