如何将拒绝行重定向到另一个文件? [英] How to redirect rejected rows to another file?
问题描述
这是我的源 csv
文件
col1,col2, col3,col4,col5,col6
1,A,AA,X,Y,H
2,B ,,, CC,D - 拒绝此行,因为(CC)
3,E,FF,Y,L
4,G ,,, XX,P,B - 拒绝此行,因为(XX)应该在col3
5, Kk ,,, D,M - 这是正确的记录,因为kk 2字母数字在col3
6,G,MM ,,,, - 自记录以来的正确记录MM在col3
如何验证此csv文件?
,以便我获得第3栏中的所有
2
字母
我按照这些步骤将源文件复制到另一个文件(因为我不在源文件中操作),然后
我没有得到如何将被拒绝的行放到另一个文件,请有人帮助我这个!
Sub test()
'/// OPEN FILE和READ
设置objFileToRead = CreateObject(Scripting.FileSystemObject)OpenTextFile(C:\ coe。 txt,1)
'set objFileToWrite = CreateObject(Scripting.FileSystemObject)。OpenTextFile(C:\copyme2.txt,2)
strFileText = objFileToRead.ReadAll b objFileToRead.Close
'/// PASTE
设置objFSO = CreateObject(Scripting.FileSystemObject)
设置objFileToWrite = objFSO.OpenTextFile(c: \cou.txt,2)
objFileToWrite.Write strFileText
objFileToWrite.Close
End Sub
如果你是VBScript的新手,你应该使用一个计划(主要任务,子任务,每个任务)和一个骨架.vbs,使它很容易实验用于解决(子)任务的方法。
在你的情况下,主要任务是将源文件中的坏行过滤到目标文件。如果您可以读取源文件的行,识别错误的文件和将它们写入目标文件,则可以解决此任务。
默认方式读取文件的行是:
Dim tsIn:Set tsIn = goFS.OpenTextFile(.. \data\\ \\ 21755767.csv)
Do Until tsIn.AtEndOfStream
Dim sLine:sLine = tsIn.ReadLine()
Loop
tsIn.Close
default表示:您必须有非常好的/特定的原因不才能选择此成语(例如:using。 ReadAll()对一个短文件进行就地编辑或调试显示)或偏离它(例如:如果你的文件是UTF-16编码,你不能依赖.OpenTextFile的默认参数)。对于一些暴行 - 例如
Do While not tsIn.AtEndOfStream =False
写(一些)行到另一个文件应该看起来像这样:
Dim tsOut:Set tsOut = goFS.CreateTextFile(.. \data\21755767-bads.csv )
Dim tsIn:Set tsIn = goFS.OpenTextFile(.. \data\21755767.csv)
Do Until tsIn.AtEndOfStream
Dim sLine:sLine = tsIn.ReadLine ()
如果为True然后
tsOut.WriteLine sLine
结束如果
循环
tsIn.Close
tsOut.Close
使用.CreateTextFile(JustTheFileSpec)而不是.OpenTextFile(lots,of,other,args)是最简单/清除/错误保存
如上所述,附加一个
WScript.Echo goFS.OpenTextFile(.. \data\21755767-bads.csv)。ReadAll()
$ c $
过滤器子任务的想法是基于观察结果:
- 标题行包含正确的字段/逗号数量
然后很容易将上面工作的结果合并为:
Dim tsOut:Set tsOut = goFS.CreateTextFile(.. \data\21755767-bads.csv)
Dim tsIn:Set tsIn = goFS.OpenTextFile(.. \data\21755767.csv)
Dim sLine:sLine = tsIn.ReadLine()
Dim nUBSeps:nUBSeps = UBound(Split(sLine,,) )
Do Until tsIn.AtEndOfStream
sLine = tsIn.ReadLine()
如果nUBSeps<> UBound(Split(sLine,,))然后
tsOut.WriteLine sLine
结束如果
循环
tsIn.Close
tsOut.Close
完整脚本:
Option Explicit'(1)
Dim goFS:Set goFS = CreateObject(Scripting.FileSystemObject)'(2)
WScript.Quit demoReadFile 3)
WScript.Quit demoReadWriteFile()
WScript.Quit demoFilterBads()
函数demoReadFile()'(4)
demoReadFile = 0
Dim tsIn:Set tsIn = goFS.OpenTextFile(.. \data\21755767.csv)
Do Until tsIn.AtEndOfStream
Dim sLine:sLine = tsIn.ReadLine()
WScript .Echo tsIn.Line - 1,sLine
Loop
tsIn.Close
结束函数
函数demoReadWriteFile()'(5)
demoReadWriteFile = 0
Dim tsOut:Set tsOut = goFS.CreateTextFile(.. \data\21755767-bads.csv)
Dim tsIn:Set tsIn = goFS.OpenTextFile(.. \data\\ \\ 21755767.csv)
Do Until tsIn.AtEndOfStream
Dim sLine:sLine = tsIn.ReadLine()
如果为真,则
tsOut.WriteLine sLine
结束If
Loop
tsIn.Close
tsOut.Close
WScript.Echo goFS.OpenTextFile(.. \data\21755767-bads.csv)。ReadAll()
结束函数
函数demoFilterBads()'(6)
demoFilterBads = 0
Dim tsOut:Set tsOut = goFS.CreateTextFile(.. \data\ 21755767-bads.csv)
Dim tsIn:Set tsIn = goFS.OpenTextFile(.. \data\21755767.csv)
Dim sLine:sLine = tsIn.ReadLine $ b Dim nUBSeps:nUBSeps = UBound(Split(sLine,,))
Do Until tsIn.AtEndOfStream
sLine = tsIn.ReadLine()
如果nUBSeps< UBound(Split(sLine,,))然后
tsOut.WriteLine sLine
结束如果
循环
tsIn.Close
tsOut.Close
WScript .Echo goFS.OpenTextFile(.. \data\21755767-bads.csv)。ReadAll()
结束函数
范例输出:
demoReadFile()
code> cscript 21755767.vbs
1 col1,col2,col3,col4,col5
2 1,A,AA,X,Y
3 2,B ,,, CC, D
4 3,E,FF,Y,
5 4,G ,,, XX,P
demoFilterBads()
cscript 21755767.vbs
2,B ,,, CC, D
4,G ,,, XX,P
这样的脚本可以从骨骼/模板如下:
选项显式(1)
Dim goFS:设置goFS = CreateObject (Scripting.FileSystemObject)'(2)
WScript.Quit step00()'(3)
WScript.Quit step01()
...
函数step00()'(4)
step00 = 0
...
结束函数
- 所有脚本都以Option Explicit开头,以防止拼写错误的变量名
- 如果你允许全局变量,那么goFS是一个很好的候选者。如果没有,只创建一个FSO并将其传递给需要它的Subs / Functions / Methods。
每次需要时都需要创建新的FSO。
- 使用评论或重新排序来调用您目前使用的功能
- 'sample'function;
更新wrt评论:
添加一个效用函数:
函数qq(s):qq = & s& ::End Function
和一个实验/探索函数:
函数demoFilterSteps()
demoFilterSteps = 0
Dim sLine
对于每个sLine在拆分(col1,col2,col3 ,col4,col5 1,A,AA,X,Y 2,B ,,, CC,D)
WScript.Echo 0,qq(sLine)
Dim aParts:aParts = Split(sLine, ,$)
Dim nUBSeps:nUBSeps = UBound(aParts)
WScript.Echo 1,nUBSeps,qq(Join(aParts, - ))
WScript.Echo
Next
nUBSeps = 4'正确
sLine =2,B ,,, CC,D'错误
Dim sExpr:sExpr =nUBSeps<> UBound Split(sLine,,))
WScript.Echo 2,nUBSeps,qq(sLine),sExpr,CStr(Eval(sExpr))
结束函数
输出:
cscript 21755767 .vbs
0col1,col2,col3,col4,col5
1 4col1-col2-col3-col4-col5
01,A,AA ,X,Y
1 41-A-AA-XY
02,B ,,, CC,D
1 52-B --- CC-D
2 42,B ,,, CC,DnUBSeps< UBound(Split(sLine,,))True
查看
- 拆分标题行会产生4个nUBSeps(5个字段之间有4个分隔符)
- 一个好的行会导致nUBSeps
- 坏行给出nUBSeps不同的(<>)4; 5在此示例中
- 假设nUBSeps为4(正确),表达式
nUBSeps<当sLine持有一个错误行时,UBound(Split(sLine,,))
计算为True,因此该行应写入目标文件
This is my Source
csv
filecol1,col2,col3,col4,col5,col6 1,A,AA,X,Y,H 2,B,,,CC,D, -- reject this row because (CC)it should be in col3 3,E,FF,Y,L 4,G,,,XX,P,B -- reject this row because(XX) it should be in col3 5,P,Kk,,,D,M -- this is correct record since kk 2 letter digit is in col3 6,G,MM,,,,-- correct record since record MM is in col3
How do I validate this csv file?
so that I get all
2
letter incolumn 3
[col3]I am following these steps source file to be copied into another file(since I don't operate in source file) then
I am not getting how to put the rejected rows into another file ,please can somebody help me on this!
Sub test() '///OPEN FILE and READ Set objFileToRead = CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\coe.txt", 1) 'Set objFileToWrite = CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\copyme2.txt", 2) strFileText = objFileToRead.ReadAll() objFileToRead.Close ' ///PASTE Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFileToWrite = objFSO.OpenTextFile("c:\cou.txt", 2) objFileToWrite.Write strFileText objFileToWrite.Close End Sub
解决方案If you are new to VBScript, you should start your coding with a plan (the main task, the subtasks, the ideas to solve each task) and a skeleton .vbs that makes it easy to experiment with the methods used to solve the (sub)tasks.
In your case the main task is to "filter bad lines in a source file to a destination file". This task is solved if you can "read the lines of the source file", "recognize the bad ones", and "write them to the destination file".
The default way to read a file's lines is:
Dim tsIn : Set tsIn = goFS.OpenTextFile("..\data\21755767.csv") Do Until tsIn.AtEndOfStream Dim sLine : sLine = tsIn.ReadLine() Loop tsIn.Close
"default" means: You must have very good/specific reasons not to choose this idiom (e.g.: using .ReadAll() on a short file for in-place-editing or debug-display) or to deviate from it (e.g.: you can't rely on .OpenTextFile's default arguments if your file is UTF-16 encoded). For some atrocities - e.g.
Do While Not tsIn.AtEndOfStream = "False"
there is no excuse at all.
Writing (some) lines to another file should look like this:
Dim tsOut : Set tsOut = goFS.CreateTextFile("..\data\21755767-bads.csv") Dim tsIn : Set tsIn = goFS.OpenTextFile("..\data\21755767.csv") Do Until tsIn.AtEndOfStream Dim sLine : sLine = tsIn.ReadLine() If True Then tsOut.WriteLine sLine End If Loop tsIn.Close tsOut.Close
Using .CreateTextFile(JustTheFileSpec) instead of .OpenTextFile(lots, of, other, args) is the most simple/clear/error-save approach for the standard case: new (perhaps empty) destination file for each run of the script.
As mentioned above, appending a
WScript.Echo goFS.OpenTextFile("..\data\21755767-bads.csv").ReadAll()
for display is ok.
The idea for the filter subtask is based on the observations:
- the header line contains the correct number of fields/commas
- the offending lines contain a bad number of commas
Then it's easy to combine the results of the work above into:
Dim tsOut : Set tsOut = goFS.CreateTextFile("..\data\21755767-bads.csv") Dim tsIn : Set tsIn = goFS.OpenTextFile("..\data\21755767.csv") Dim sLine : sLine = tsIn.ReadLine() Dim nUBSeps : nUBSeps = UBound(Split(sLine, ",")) Do Until tsIn.AtEndOfStream sLine = tsIn.ReadLine() If nUBSeps <> UBound(Split(sLine, ",")) Then tsOut.WriteLine sLine End If Loop tsIn.Close tsOut.Close
The full script:
Option Explicit ' (1) Dim goFS : Set goFS = CreateObject("Scripting.FileSystemObject") ' (2) WScript.Quit demoReadFile() ' (3) WScript.Quit demoReadWriteFile() WScript.Quit demoFilterBads() Function demoReadFile() ' (4) demoReadFile = 0 Dim tsIn : Set tsIn = goFS.OpenTextFile("..\data\21755767.csv") Do Until tsIn.AtEndOfStream Dim sLine : sLine = tsIn.ReadLine() WScript.Echo tsIn.Line - 1, sLine Loop tsIn.Close End Function Function demoReadWriteFile() ' (5) demoReadWriteFile = 0 Dim tsOut : Set tsOut = goFS.CreateTextFile("..\data\21755767-bads.csv") Dim tsIn : Set tsIn = goFS.OpenTextFile("..\data\21755767.csv") Do Until tsIn.AtEndOfStream Dim sLine : sLine = tsIn.ReadLine() If True Then tsOut.WriteLine sLine End If Loop tsIn.Close tsOut.Close WScript.Echo goFS.OpenTextFile("..\data\21755767-bads.csv").ReadAll() End Function Function demoFilterBads() ' (6) demoFilterBads = 0 Dim tsOut : Set tsOut = goFS.CreateTextFile("..\data\21755767-bads.csv") Dim tsIn : Set tsIn = goFS.OpenTextFile("..\data\21755767.csv") Dim sLine : sLine = tsIn.ReadLine() Dim nUBSeps : nUBSeps = UBound(Split(sLine, ",")) Do Until tsIn.AtEndOfStream sLine = tsIn.ReadLine() If nUBSeps <> UBound(Split(sLine, ",")) Then tsOut.WriteLine sLine End If Loop tsIn.Close tsOut.Close WScript.Echo goFS.OpenTextFile("..\data\21755767-bads.csv").ReadAll() End Function
sample output:
demoReadFile()
cscript 21755767.vbs 1 col1,col2,col3,col4,col5 2 1,A,AA,X,Y 3 2,B,,,CC,D 4 3,E,FF,Y, 5 4,G,,,XX,P
demoFilterBads()
cscript 21755767.vbs 2,B,,,CC,D 4,G,,,XX,P
Such a script could start from a skeleton/template like:
Option Explicit ' (1) Dim goFS : Set goFS = CreateObject("Scripting.FileSystemObject") ' (2) WScript.Quit step00() ' (3) WScript.Quit step01() ... Function step00() ' (4) step00 = 0 ... End Function
- All your scripts should start with "Option Explicit" to guard against mis-spelled variable names
- If you allow global variables at all, then goFS is a good candidate. If not, create just one FSO and pass it to the Subs/Functions/Methods that need it. Never create a new FSO each time you need its methods/properties.
- Use comments or reordering to call the function you currently work with
- 'sample' function; write a lot of them to check/elaborate your ideas
Update wrt comment:
Add an utility function:
Function qq(s) : qq = """" & s & """" : End Function
and an experiment/explore function:
Function demoFilterSteps() demoFilterSteps = 0 Dim sLine For Each sLine In Split("col1,col2,col3,col4,col5 1,A,AA,X,Y 2,B,,,CC,D") WScript.Echo 0, qq(sLine) Dim aParts : aParts = Split(sLine, ",") Dim nUBSeps : nUBSeps = UBound(aParts) WScript.Echo 1, nUBSeps, qq(Join(aParts, "-")) WScript.Echo Next nUBSeps = 4 ' correct sLine = "2,B,,,CC,D" ' bad Dim sExpr : sExpr = "nUBSeps <> UBound(Split(sLine, "",""))" WScript.Echo 2, nUBSeps, qq(sLine), sExpr, CStr(Eval(sExpr)) End Function
output:
cscript 21755767.vbs 0 "col1,col2,col3,col4,col5" 1 4 "col1-col2-col3-col4-col5" 0 "1,A,AA,X,Y" 1 4 "1-A-AA-X-Y" 0 "2,B,,,CC,D" 1 5 "2-B---CC-D" 2 4 "2,B,,,CC,D" nUBSeps <> UBound(Split(sLine, ",")) True
To see
- Splitting the header line results in a nUBSeps of 4 (4 separators between 5 fields)
- A good line results in a nUBSeps of 4 too - not a surprise
- A bad line gives a nUBSeps different (<>) of 4; 5 in this sample
- Assuming nUBSeps is 4 (correct), the expression
nUBSeps <> UBound(Split(sLine, ","))
evaluates to True, when sLine holds a bad line - so that line should be written to the destination file
这篇关于如何将拒绝行重定向到另一个文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!