VBscript中要解决的一个小问题 [英] A small problem to solve in VBscript

问题描述

你能不能在 VBScript 中试试这个:您有一个包含多种货币金额的文本文件，每一行都采用以下格式:

Can you please try this in VBScript: You got a text file with several currency amounts, every one in one line on following format:

DESCRIPTION Date Amount

McDonalds 2011-01-01 20.00
Clothing Store 2011-01-02 30.00

尝试构建一个程序来读取该文本文件并对其中找到的所有值求和.

Try to build a program to read that text file and sum all values found in there.

推荐答案

第一次(行人)尝试:

Option Explicit

Dim goFS : Set goFS = CreateObject( "Scripting.FileSystemObject" )

Dim sFSpec : sFSpec = goFS.GetAbsolutePathName( "..\data\expenses.csv" )
WScript.Echo goFS.OpenTextFile( sFSpec ).ReadAll()
WScript.Echo "---------"
Dim tsIn  : Set tsIn = goFS.OpenTextFile( sFSpec )
Dim nSum  : nSum     = 0.0
Dim sLine : sLine    = tsIn.ReadLine()
Do Until tsIn.AtEndOfStream
   sLine = tsIn.ReadLine()
   Dim aFields : aFields = Split( sLine, "," )
   If 2 = UBound( aFields ) Then
      nSum = nSum + CDbl( aFields( 2 ) ) ' en_us, ...
'     nSum = nSum + CDbl( Replace( aFields( 2 ), ".", "," ) ) ' locale de_de, ...
   End If
Loop
tsIn.Close
WScript.Echo "Sum:", nSum      

输出:

lineLoop - sum values (line loop)
---------------------------------------------------
"DESCRIPTION","Date","Amount"
"McDonalds",2011-01-01,20.00
"Clothing Store",2011-01-02,30.00
"paying programmer",2011-07-09,47.11

---------
Sum: 97.11
===================================================
xpl.vbs: Erfolgreich beendet. (0) [ 0,01563 secs ]

第二次尝试(使用正则表达式):

Second attempt (using a regular expression):

Dim sFSpec : sFSpec = goFS.GetAbsolutePathName( "..\data\expenses.csv" )
Dim sAll   : sAll   = goFS.OpenTextFile( sFSpec ).ReadAll()
WScript.Echo sAll
WScript.Echo "---------"
Dim reAmount : Set reAmount = New RegExp
reAmount.Global    = True
reAmount.MultiLine = True
reAmount.Pattern   = ",(\d+\.\d+)\r$" 
Dim nSum     : nSum         = 0.0
Dim oMTS     : Set oMTS     = reAmount.Execute( sAll )
Dim oMT
For Each oMT In oMTS
    nSum = nSum + CDbl( oMT.Submatches(0) ) ' en_us, ...
'   nSum = nSum + CDbl( Replace( oMT.Submatches(0), ".", "," ) ) ' locale de_de, ...
Next
WScript.Echo "Sum:", nSum      

第三次尝试(ADO/文本驱动程序):

Third attempt (ADO/Text driver):

schema.ini:

schema.ini:

[expenses.csv]
Format=CSVDelimited
ColNameHeader=True
DateTimeFormat=YYYY-MM-DD
CharacterSet=ANSI
Col1=DESCRIPTION CHAR
Col2=Date DATE
Col3=Amount FLOAT

代码:

Const adClipString = 2

Dim sTDir : sTDir    = goFS.GetAbsolutePathName( "..\data" )
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 oTDb.Execute( "SELECT * FROM [expenses.csv]" ) _
  .GetString( adClipString, , vbTab, vbCrLf, "" )
WScript.Echo oTDb.Execute( "SELECT SUM(Amount) AS [Sum] FROM [expenses.csv]" ) _
  .GetString( adClipString, , vbTab, vbCrLf, "" )
WScript.Echo oTDb.Execute( "SELECT SUM(Amount) AS [Sum] FROM [expenses.csv] WHERE MONTH([Date]) = 7" ) _
  .GetString( adClipString, , vbTab, vbCrLf, "" )
oTDb.Close

输出:

McDonalds       01.01.2011      20
Clothing Store  02.01.2011      30
paying programmer       09.07.2011      47,11

97,11

47,11

===================================================
xpl.vbs: Erfolgreich beendet. (0) [ 0,23438 secs ]

或者 - 做一些额外的工作:

or - with a bit of additional work:

useAdoText - sum values (ADO/Text driver)
----------------------------------------------------------------------------
----------------------------------------------------------------------------
SELECT * FROM [expenses.csv]
----------------------------------------------------------------------------
|DESCRIPTION      |Date      |Amount|
|McDonalds        |01.01.2011|    20|
|Clothing Store   |02.01.2011|    30|
|paying programmer|09.07.2011| 47,11|
----------------------------------------------------------------------------
----------------------------------------------------------------------------
SELECT SUM(Amount) AS [Sum] FROM [expenses.csv]
----------------------------------------------------------------------------
|Sum  |
|97,11|
----------------------------------------------------------------------------
----------------------------------------------------------------------------
SELECT SUM(Amount) AS [Sum] FROM [expenses.csv] WHERE MONTH([Date]) = 7
----------------------------------------------------------------------------
|Sum  |
|47,11|
----------------------------------------------------------------------------
============================================================================
xpl.vbs: Erfolgreich beendet. (0) [ 0,25000 secs ]

这篇关于VBscript中要解决的一个小问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！