VBScript正确/重新格式化分隔的文本文件？ [英] VBScript to correctly/re format a delimited text file?

问题描述

有人可以帮助我使用VBScript重新格式化/正确格式化分隔的文本文件吗？

Can someone help me reformat/correctly format a delimited text file using VBScript?

我有一个文本文件，该文本文件为 ^ 分隔如下：

I have a text file that is ^ delimited as below:

AGREE^NAME^ADD1^ADD2^ADD3^ADD4^PCODE^BAL^ARREARS
00010004000051162^MISS JENNIFER GRAY                      ^123 FAKE STREET           ^                              ^TOWN                     ^COUNTY    ^POSTCODE    ^ 004978.00^ 000188.72

所有数据都包含需要删除的前导和尾随空格。我只有VBScript可以做到这一点。

All of the data contains leading and trailing spaces that need to be removed. I only have VBScript available to do this.

我尝试使用ADO GetStrings，但是由于前导和尾随空格，它产生了不一致的结果。

I have tried to use ADO GetStrings but it has yielded inconsistent results because of the leading and trailing spaces.

有人可以提供任何建议或替代方案吗？

Can anyone offer any suggestions or alternatives?

谢谢

推荐答案

使用ADO文本文件时，应从适当的schema.ini文件中的表定义
开始：

When working with ADO text files, you should start with table definitions in the approriate schema.ini file:

[agree.txt]
Format=Delimited(^)
ColNameHeader=True
DecimalSymbol=.
CharacterSet=ANSI
TextDelimiter=None
Col1=AGREE   CHAR
Col2=NAME    CHAR
Col3=ADD1    CHAR
Col4=ADD2    CHAR
Col5=ADD3    CHAR
Col6=ADD4    CHAR
Col7=PCODE   CHAR
Col8=BAL     FLOAT
Col9=ARREARS FLOAT

然后您可以访问您的数据：

Then you can access your data:

  Dim sTDir  : sTDir    = goFS.GetAbsolutePathName( "..\data" )
  Dim sTbl1  : sTbl1    = "agree.txt"
  Dim sFSpec : sFSpec   = goFS.BuildPath(sTDir, sTbl1)
  Dim sTbl2  : sTbl2    = "agree2.txt"
  WScript.Echo "src file with spaces:"
  WScript.Echo goFS.OpenTextFile(sFSpec).ReadAll()
  Dim oTDb   : Set oTDb = CreateObject( "ADODB.Connection" )
  Dim sCS    : sCS      = Join( Array( _
     "Provider=Microsoft.Jet.OLEDB.4.0" _
   , "Data Source=" & sTDir _
   , "Extended Properties=" & Join( Array( _
        "text" _
      ), ";" ) _
  ), ";" )
  oTDB.Open sCS
  WScript.Echo "trimmed automagically:"
  WScript.Echo oTDb.Execute(Replace("SELECT * FROM [@T]", "@T", sTbl1)) _
    .GetString( adClipString, , "|", vbCrLf, "" )

输出：

src file with spaces:
AGREE^NAME^ADD1^ADD2^ADD3^ADD4^PCODE^BAL^ARREARS
00010004000051162^MISS JENNIFER GRAY                      ^123 FAKE STREET     ^                              ^TOWN                     ^COUNTY    ^POSTCODE    ^ 004978.00^ 000188.72

trimmed automagically:
00010004000051162|MISS JENNIFER GRAY|123 FAKE STREET||TOWN|COUNTY|POSTCODE|4978|188,72

如您所见，如果使用$，则虚假空格没有任何问题b $ b正确的工具。

As you can see, there isn't any problem with spurious spaces, if you use the right tool.

要获得完整副本，只需添加

To get a clean copy, just add

  sFSpec = goFS.BuildPath(sTDir, sTbl2)
  If goFS.FileExists(sFSpec) Then goFS.DeleteFile sFSpec
  Dim sSQL : sSQL = Replace(Replace( _
      "SELECT * INTO [@T2] FROM [@T1]" _
    , "@T1", sTbl1), "@T2", sTbl2)
  WScript.Echo "Copy statement"
  WScript.Echo sSQL
  oTDb.Execute sSQL
  WScript.Echo "QED: no spurious whilespace"
  WScript.Echo goFS.OpenTextFile(sFSpec).ReadAll()

输出：

Copy statement
SELECT * INTO [agree2.txt] FROM [agree.txt]
QED: no spurious whilespace
"AGREE";"NAME";"ADD1";"ADD2";"ADD3";"ADD4";"PCODE";"BAL";"ARREARS"
"00010004000051162";"MISS JENNIFER GRAY";"123 FAKE STREET";;"TOWN";"COUNTY";"POSTCODE";4978,00;188,72

驾驶员将添加

[agree2.txt]
ColNameHeader=True
CharacterSet=1252
Format=Delimited(;)
Col1=AGREE Char Width 255
Col2=NAME Char Width 255
Col3=ADD1 Char Width 255
Col4=ADD2 Char Width 255
Col5=ADD3 Char Width 255
Col6=ADD4 Char Width 255
Col7=PCODE Char Width 255
Col8=BAL Float
Col9=ARREARS Float

（默认为德语语言环境）到schema.ini；将其编辑为

(german locale defaults) to the schema.ini; edit this to

[agree2.txt]
Format=Delimited(^)
ColNameHeader=True
DecimalSymbol=.
CharacterSet=ANSI
TextDelimiter=None
Col1=AGREE   CHAR
...

重新创建原始格式：

QED: no spurious whilespace
AGREE^NAME^ADD1^ADD2^ADD3^ADD4^PCODE^BAL^ARREARS
00010004000051162^MISS JENNIFER GRAY^123 FAKE STREET^^TOWN^COUNTY^POSTCODE^4978.00^188.72

通过使用更复杂的SQL语句和/或增强表定义，您可以轻松地解决更复杂的任务。

By using more elaborate SQL statements and/or enhancing the table definitions you can solve more complex tasks in a straighforward way.

PS

查看此处，如果

  Dim sSQL : sSQL = Replace(Replace( _
      "SELECT * INTO [@T2] FROM [@T1]" _
    , "@T1", sTbl1), "@T2", sTbl2)

让您感到困惑（@ T1和@ T2不是不是 ADO命令参数；嵌套的Replace（）调用适用 之前。

makes you wonder (@T1 and @T2 are not ADO command parameters; the nested Replace() calls apply before ADO sees the then modified statement).

这篇关于VBScript正确/重新格式化分隔的文本文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！