Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


Delphinus

611 posts

Ultimate Geek
+1 received by user: 274


#303028 11-Jan-2023 13:32
Send private message

We're taking some data out of a SQL database, and it's stored as RTF. What's the best way to convert this to plain text (keeping line breaks) using Excel? Using Power Query or something?

 

Looks like this (3 cells as example)

 

 

 

{\rtf1\ansi\ansicpg1252\deff0\deflang5129{\fonttbl{\f0\fnil\fcharset0 Tahoma;}}
{\colortbl ;\red0\green0\blue0;}
\viewkind4\uc1\pard\tx30\tx45\tx30\tx75\tx105\cf1\lang1033\f0\fs22 ASD repair\par
watch detecting asympt ATach most likely\par
not heart block\cf0\lang5129\fs23\par
}

 

{\rtf1\ansi\ansicpg1252\deff0\deflang5129{\fonttbl{\f0\fnil\fcharset0 Tahoma;}}
{\colortbl ;\red0\green0\blue0;}
\viewkind4\uc1\pard\tx30\tx45\tx30\tx75\tx105\cf1\lang1033\f0\fs22 I am certain these are pseudoseizures, but should exclude other path\par
echo ?structurally N heart\cf0\lang5129\fs23\par
}

 

{\rtf1\ansi\ansicpg1252\deff0\deflang5129{\fonttbl{\f0\fnil\fcharset0 Tahoma;}}
{\colortbl ;\red0\green0\blue0;}
\viewkind4\uc1\pard\tx30\tx45\tx30\tx75\tx105\cf1\lang1033\f0\fs22 frequent but minimally symtomatic RVOT VT\par
imaging N\par
recommend no Rx\cf0\lang5129\fs23\par
}


Create new topic
cddt
1965 posts

Uber Geek
+1 received by user: 1904


  #3020055 11-Jan-2023 14:05
Send private message

Your options are almost endless and depend on your level of confidence with technology...

 

 

 

1) Go back to whatever routine exports this from the database (what kind of SQL database?) and export what you want in the format you want.

 

2) Use a text utility such as awk/sed/powershell to strip out the text you want.

 

3) Use a higher level language such as python to strip out the text you want.

 

4) Use Excel formulas to extract the text you want.

 

5) Use PowerQuery as you suggested... (bleh)




Delphinus

611 posts

Ultimate Geek
+1 received by user: 274


  #3020076 11-Jan-2023 14:35
Send private message

I understand options are endless, but was hoping to get some tips or recommendations where possible.

 

     

  1. Connecting straight from Excel. Data > Get Data > From Database > SQL. So we're getting raw information from that table. What do you mean what kind of SQL database?
  2. Haven't used awk/sed/powershell in this context
  3. In an ideal world trying to work out a way to do this so that non-programmers can do it.
  4. Do you have any examples of Excel formulas that might do this?
  5. Any ideas how to use PowerQuery to achieve this?

 

I did found https://stackoverflow.com/questions/42576291/convert-rtf-rich-text-format-code-into-plain-text-in-excel but unfamiliar with how to use those code examples.


Create new topic








Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.