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

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

所有数据都包含需要删除的前导和尾随空格。我只有VBScript可用来做这个。

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

我尝试过使用ADO GetStrings，但由于前导和后置空格，结果不一致。

Can anyone offer any suggestions or alternatives?

谁能给我一些建议或选择吗?

Thanks

谢谢

1 个解决方案

#1

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

在处理ADO文本文件时，应该从approriate模式中的表定义开始。ini文件:

[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, "" )

output:

输出:

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

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()

output:

输出:

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

The driver will add

司机将增加

[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

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

(德语语言环境默认)模式。ini;编辑这

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

to re-create your original format:

重新创建您的原始格式:

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

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

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

P.S.

注:

Look here, if

看这里,如果

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

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

让您疑惑(@T1和@T2不是ADO命令参数;在ADO看到then modified语句之前，嵌套的Replace()调用apply。