删除包含特定字符的MS Access单词/字符串 [英] Deleting words/strings containing a specific character in MS Access

问题描述

我正在写一个查询，提取，通过一个供应商创建的文字处理软件到Oracle数据库中输入的文本，我需要将其导出到Word或Excel。文本输入到一个备注字段和文本交织着codeS的字处理程序使用不同的功能（加粗，缩进，硬回车，字体大小等）。

我用的替换功能解析了很多比较常见的codeS，但有这么多的变化，这几乎是不可能追上他们。有没有办法做到这一点？不幸的是，我仅限于使用Microsoft Access 2010，试图做到这一点。

我发现的共同点是，所有的codeS开始一个反斜杠，我希望能够删除开头的所有字符串一个反斜杠到下一个空间，让所有C $ CS的$被剥离出来的最终文本。

下面是我的工作与文字的简单例子：

  {\ RTF1 \ ANSI \ ansicpg1252 \ deff0 \ deflang1033 {\ fonttbl {\ F0 \ fnil \ fcharset0宋体;

    \ viewkind4 \ UC1 \ PARD \ F0 \ FS36的简称进行了调查
    在15年2月2日，调查投诉＃OK000227。 \杆
    没有不足之处进行了列举。\杆

    \ FS20 \相提并论
}}
 

解决方案

如果你的机器上安装Microsoft Word，那么你已经有了一个RTF解析器可用，因此您不必滚你自己。你可以得到Word中打开RTF文档，并保存为这样的纯文本：

选项比较数据库 显式的选项 公共功能RtfToPlainText（rtfText为Variant）为Variant     昏暗的RTN为Variant     昏暗tempFolder作为字符串，rtfPath作为字符串，txtPath作为字符串     昏暗FSO作为对象'FileSystemObject的     昏暗˚F作为对象文本流     昏暗wordApp作为对象Word.Application     昏暗wordDoc作为对象Word.Document     昏暗tempFileName作为字符串     tempFileName =〜RtfToPlainText     如果ISNULL（rtfText）然后         RTN = NULL     其他         保存的RTF文本文件         设置FSO =的CreateObject（Scripting.FileSystemObject的）         tempFolder = fso.GetSpecialFolder（2）'Temporaryfolder         rtfPath = tempFolder和放大器; \＆安培; tempFileName和放大器; 的.rtf         集合F = fso.CreateTextFile（rtfPath）         f.Write rtfText         f.Close         将f =什么         在Word中打开并保存为纯文本         设置wordApp =的CreateObject（Word.Application）         设置wordDoc = wordApp.Documents.Open（rtfPath）         txtPath = tempFolder和放大器; \＆安培; tempFileName和放大器; 。TXT         wordDoc.SaveAs2 txtPath，2'wdFormatText         wordDoc.Close假         设置wordDoc =什么         wordApp.Quit假         设置wordApp =什么         fso.DeleteFile rtfPath         检索纯文本         集合F = fso.OpenTextFile（txtPath）         RTN = f.ReadAll         f.Close         将f =什么         fso.DeleteFile txtPath         设置FSO =什么     结束如果     RtfToPlainText = RTN 端功能

然后，如果你有两个备注字段有一个表 - [rtfText]和[明文] - 您可以使用下面的查询在Access中提取纯文本到第二个备注字段：

更新rtfTestTable组明文= RtfToPlainText（[rtfText]）;

I'm writing a query to extract text that was entered through a vendor-created word processor to an Oracle database and I need to export it to Word or Excel. The text is entered into a memo field and the text is intertwined with codes that the word processor uses for different functions (bold, indent, hard return, font size, etc.).

I've used the replace function to parse out a lot of the more common codes, but there are so many variations, it's nearly impossible to catch them all. Is there a way to do this? Unfortunately, I'm limited to using Microsoft Access 2010 to try and accomplish this.

The common thread I've found is that all the codes start with a back-slash and I'd like to be able to delete all strings that start with a back-slash up to the next space so all the codes are stripped out of the final text.

Here's a brief example of the text I'm working with:

  {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Times New Roman;

    \viewkind4\uc1\pard\f0\fs36 An abbreviated survey was conducted
    on 02/02/15 to investigate complaint #OK000227. \par  
    No deficiencies were cited.\par

    \fs20\par
}}

解决方案

If your machine has Microsoft Word installed then you already have an RTF parser available so you don't have to "roll your own". You can just get Word to open the RTF document and save it as plain text like this:

Option Compare Database
Option Explicit

Public Function RtfToPlainText(rtfText As Variant) As Variant
    Dim rtn As Variant
    Dim tempFolder As String, rtfPath As String, txtPath As String
    Dim fso As Object  ' FileSystemObject
    Dim f As Object  ' TextStream
    Dim wordApp As Object  ' Word.Application
    Dim wordDoc As Object  ' Word.Document
    Dim tempFileName As String
    tempFileName = "~RtfToPlainText"

    If IsNull(rtfText) Then
        rtn = Null
    Else
        ' save RTF text as file
        Set fso = CreateObject("Scripting.FileSystemObject")
        tempFolder = fso.GetSpecialFolder(2)  ' Temporaryfolder
        rtfPath = tempFolder & "\" & tempFileName & ".rtf"
        Set f = fso.CreateTextFile(rtfPath)
        f.Write rtfText
        f.Close
        Set f = Nothing

        ' open in Word and save as plain text
        Set wordApp = CreateObject("Word.Application")
        Set wordDoc = wordApp.Documents.Open(rtfPath)
        txtPath = tempFolder & "\" & tempFileName & ".txt"
        wordDoc.SaveAs2 txtPath, 2  ' wdFormatText
        wordDoc.Close False
        Set wordDoc = Nothing
        wordApp.Quit False
        Set wordApp = Nothing
        fso.DeleteFile rtfPath

        ' retrieve plain text
        Set f = fso.OpenTextFile(txtPath)
        rtn = f.ReadAll
        f.Close
        Set f = Nothing
        fso.DeleteFile txtPath
        Set fso = Nothing
    End If
    RtfToPlainText = rtn
End Function

Then, if you had a table with two Memo fields - [rtfText] and [plainText] - you could extract the plain text into the second Memo field using the following query in Access:

UPDATE rtfTestTable SET plainText = RtfToPlainText([rtfText]);

这篇关于删除包含特定字符的MS Access单词/字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！