用vbs查询csv [英] querying csv with vbs

问题描述

有一个这样的csv文件，我可以使用下面的代码轻松地阅读它。但是正如您所看到的，csv中有多个name1，group1，status1，name2，group2等列。每个用户将具有不同数量的列。我想知道是否有一种方法可以在调用 objRecordset.Fields.Item（ Group1）的地方使用通配符，例如（ Group％）或如果我可以自动递增数字直到找不到记录

There's a csv file like so, I can read it easily enough with the code below. But as you can see there are multiple name1, group1, status1, name2, group2, etc columns in the csv. Each user will have a different number of columns. I was wondering if there is a way to use wild cards where I'm calling objRecordset.Fields.Item("Group1") something like ("Group%") or if I can auto increment the number until no records are found

UserName，Domain， Site，MCO，名称1，组1，状态1，名称2，组2，状态2，名称3，组3，状态3
Paolina，AA，雅典，希腊，Adobe Acrobat Pro，ACROBAT009，实时，``，乔治，AA，雅典，希腊，SpotFire 2.20，SPOTFIRE220，Live ,,,,,,

UserName,Domain,Site,MCO,Name1,Group1,Status1,Name2,Group2,Status2,Name3,Group3,Status3 Paolina,AA,Athens,Greece,Adobe Acrobat Pro,ACROBAT009,Live,,,,,, George,AA,Athens,Greece,SpotFire 2.20,SPOTFIRE220,Live,,,,,,

option explicit

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Dim strPathtoTextFile, objConnection, objRecordSet, objNetwork
Dim wshshell, Username

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

Set objNetwork = CreateObject("WScript.Network")
userName = objNetwork.UserName

strPathtoTextFile = "C:\Hunter\vbs\" 'must have a trailing \

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""

objRecordset.Open "SELECT * FROM Users.txt where [user name] like '" & UserName & "'", _
          objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
    Wscript.Echo "Name: " & objRecordset.Fields.Item("User Name")
    Wscript.Echo "Group: " & objRecordset.Fields.Item("Group1")
    Wscript.echo "Status:" & objRecordset.Fields.Item("Status1")
    objRecordSet.MoveNext
Loop

推荐答案

您的示例建议最大组数是最后一个字段，所以也许：

Your example suggests that the maximum group number is the last field, so perhaps:

objRecordset.Open "SELECT * FROM Users.txt where [user name] like '" _
      & UserName & "'", _
      objConnection, adOpenStatic, adLockOptimistic, adCmdText
MaxNum = _
      Replace(objRecordset.Fields(objRecordset.Fields.Count-1).Name,"Status","")
Do Until objRecordset.EOF
    Wscript.Echo "Name: " & objRecordset.Fields.Item("User Name")
    For i=1 to MaxNum
       Wscript.Echo "Group: " & objRecordset.Fields.Item("Group" & i)
       Wscript.echo "Status:" & objRecordset.Fields.Item("Status" & i)
    Next
    objRecordSet.MoveNext
Loop

我还没有测试过，但总体思路应该成立。

I have not tested, but the general idea should hold.

这篇关于用vbs查询csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！