统计文件信息及生成SQL语句（VBS）实例

news/2024/7/10 5:27:36 标签: sql, function, server, path, 测试, each

查询文件名(带日期及大小)_0714_1655.vbs 

 

'======================================================================================
'dim ws,fso,f,fd,files,tmpname,url,result,preFix
set ws=createobject("wscript.shell") 
set fso = CreateObject("Scripting.FileSystemObject") 
set fd = fso.getfolder(ws.currentdirectory) 
set files=fd.files 
'生成前缀
result=ws.currentdirectory

result =replace(result ,"\","+")
result =replace(result ,".","_")
result =replace(result ,":","=")
'建立一个文本，存储文件的名字
result= result & "_" & "fileinfos.txt"

set resultFile=fso.createtextfile(result,2,ture)
for each f in files
'读取文件的名字 要将文件名都存成小写可 将f.name 改成：lcase(f.name) 
tmpname="Name:<" & f.name &">Lastdate:<"& f.datelastmodified &">Size:<"& f.Size &">"'BYTE
'上面语句换成以下语句后，就只查询文件名，而没有其它信息
'tmpnamef.name
'把原来的文件名保存起来
resultFile.writeline tmpname
next

MsgBox "   完成！"


 

转换成SQL20110715_1324.VBS

Const ForReading=1
Const ForWriting=2
Const ForAppending=8
set fso=createobject("scripting.filesystemobject")
'源文件 
strSrcFile=InputBox("请输入源文件")'"W=+素材一致性问题_fileinfos.txt"
set sourceFile=fso.opentextfile(strSrcFile,ForReading)
'目标文件
resultFile="result_" & strSrcFile & TimeStr(Now) & ".sql"
set dstFile=fso.opentextfile(resultFile,ForWriting,true)
'''''''''''''''''''''''构造SQL语句
strSql= "--查询条件中硬盘文件信息来自文件：" & strSrcFile & vbCr
strSql=strSql & "SELECT * FROM m_uploadtask where " & vbCr & GetSqlCondition(sourceFile,"2011-6-26") & vbCr & ""
'MsgBox strSql'测试输出
'''''''''''''''''''''''
dstFile.Write(strSql)
sourceFile.Close
dstFile.Close

'===============================================================得到SQL语句中的条件表达式
Function GetSqlCondition(srcFile,timeBefore)
	oTimeBefore=DateValue(timeBefore)
	
	nValidCount=0
	nTotalCount=0
	delimiter="|"
	condition = " materialid IN ("
	conditionNotValid="/*"
	do until srcFile.atendofstream
		sLine=TransLineFromat(srcFile.readline,delimiter)
		arr=Split(sLine,delimiter)
		'以下是解析出的每一行中的三个变量
		'If UBound(arr)=2 Then
		
		sName=arr(0)
		sTime=arr(1)
		oTime=DateValue(sTime)'不能用TimeValue
		sSize=arr(2)

		sMaterialID =Left(sName,InStr(sName,"_")-1)
		'nSize=CLng(sSize)
		
		'MsgBox sMaterialID
		nTotalCount=nTotalCount+1
		if(oTime < oTimeBefore) Then
			nValidCount=nValidCount+1
			condition=condition & "'" & sMaterialID & "',"
		Else
			conditionNotValid=conditionNotValid & sMaterialID & "(" & sTime & "),"
		End If
		'end if
		'result=result & " " & sName & " " & oTime & " " & nSize &vbCr'测试用
	loop
	condition=Left(condition,Len(condition)-1)
	condition=condition & ")" 
	conditionNotValid=conditionNotValid & "*/" 
	'在SQL注释中添加统计信息
	condition= condition & vbCr &"--有效ID是指"& timeBefore &"以前的素材ID，"
	condition= condition & vbCr &"--valid/total=" & nValidCount &"/" & nTotalCount & vbTab &"未用的ID如下：" 
	condition= condition & vbCr &"--" &conditionNotValid
GetSqlCondition=condition
End Function
'===============================================================格式转换
Function TransLineFromat(sLine,delimiter)
	sReturn=sLine
	D = Replace( D, N & ",", "",1,1) 
	sReturn=Replace( sReturn,"Name:<","",1,1) 
	sReturn=Replace( sReturn,">Lastdate:<",delimiter,1,1) 
	sReturn=Replace( sReturn,">Size:<",delimiter,1,1) 
	sReturn=Replace( sReturn,">","",1,1) 
TransLineFromat=sReturn
End Function
'===============================================================将时间类型转换成字符串
Function TimeStr(t)  
	ret=Year(t) & "" & Month(t) & "" & Day(t) & "_" & Hour(t) & "" & Minute(t)' & "" & Second(t)
TimeStr = CStr(ret)
End Function

 

仅根据ID文件生成SQL_ID条件.VBS

Const ForReading=1
Const ForWriting=2
Const ForAppending=8
set fso=createobject("scripting.filesystemobject")
'源文件 
strSrcFile=InputBox("请输入源文件")'"W=+素材一致性问题_fileinfos.txt"
set sourceFile=fso.opentextfile(strSrcFile,ForReading)
'目标文件
resultFile="Sql条件_" & strSrcFile & TimeStr(Now) & ".sql"
set dstFile=fso.opentextfile(resultFile,ForWriting,true)
'''''''''''''''''''''''构造SQL语句
strSql= "--查询条件中信息来自文件：" & strSrcFile & vbCr
strSql=strSql & "SELECT * FROM m_uploadtask where " & vbCr & GetSqlCondition(sourceFile) & vbCr & ""
'MsgBox strSql'测试输出
'''''''''''''''''''''''
dstFile.Write(strSql)
sourceFile.Close
dstFile.Close

'===============================================================得到SQL语句中的条件表达式
Function GetSqlCondition(srcFile)
	nTotalCount=0
	condition = " materialid IN ("
	do until srcFile.atendofstream
		sLine=srcFile.readline
		if sLine<>"" Then
			nTotalCount=nTotalCount+1
			condition=condition & "'" & sLine & "',"
		End If
	loop
	condition=Left(condition,Len(condition)-1)
	condition=condition & ")" 
	'在SQL注释中添加统计信息
	condition= condition & vbCr &"--total=" &  nTotalCount & vbTab 
GetSqlCondition=condition
End Function

'===============================================================将时间类型转换成字符串
Function TimeStr(t)  
	ret=Year(t) & "" & Month(t) & "" & Day(t) & "_" & Hour(t) & "" & Minute(t)' & "" & Second(t)
TimeStr = CStr(ret)
End Function

删除文件脚本.vbs

Const ForReading=1
Const ForWriting=2
Const ForAppending=8
Set objShell = CreateObject("Wscript.Shell")  
set fso=createobject("scripting.filesystemobject") 
'Executeglobal fso.opentextfile("DeleteFileService.vbs ", 1).readall

'建立日志文件
strDelLogFile="DeletedFile_" & TimeStr(Now) & ".log"
set fLog=fso.opentextfile(strDelLogFile,ForWriting,true)

Dim arrA(0)'A类1
arrA(0)="7777-7777"
Dim arrB(1)'B类2
arrB(0)="9999-0007"
arrB(1)="9999-0006"
Dim arrF(3)'F类4
arrF(0)="4050-0882"
arrF(1)="7111-4204"
arrF(2)="5025-0843"
arrF(3)="6032-0803"
Dim arrG(9)'G类10
arrG(0)="9999-0001"
arrG(1)="9999-0002"
arrG(2)="9999-0021"
arrG(3)="9999-0008"
arrG(4)="9999-0027"
arrG(5)="9999-0039"
arrG(6)="9999-0041"
arrG(7)="9999-0043"
arrG(8)="9999-0044"
arrG(9)="9999-9999"
Dim arrI(0)'I类1
arrI(0)="9999-0045"

'''真实的删除操作
strLogItem=DeleteArrayFile(fso,arrA)
fLog.Write(strLogItem)
strLogItem=DeleteArrayFile(fso,arrB)
fLog.Write(strLogItem)
'F类暂不处理
'strLogItem=DeleteArrayFile(fso,arrF)
'fLog.Write(strLogItem)
strLogItem=DeleteArrayFile(fso,arrG)
fLog.Write(strLogItem)
strLogItem=DeleteArrayFile(fso,arrI)
fLog.Write(strLogItem)

fLog.Close



'===============================================================这个没有用到
Function GetIDSqlCondition(fso,arrFile)  
	condition=" materialid IN ("
	For i = 0 To UBound(arrFile)
		strID = arrFile(i)
		condition=condition & "'" & strID & "',"
	Next
	condition=Left(condition,Len(condition)-1)
	condition=condition & ")"
GetIDSqlCondition =condition
End Function
'===============================================================将时间类型转换成字符串
Function TimeStr(t)  
	ret=Year(t) & "" & Month(t) & "" & Day(t) & "_" & Hour(t) & "" & Minute(t)' & "" & Second(t)
TimeStr = CStr(ret)
End Function
'===============================================================删除一个数组中的文件
Function DeleteArrayFile(fso,arrFile)  
	strDelDetail=""
	For i = 0 To UBound(arrFile)
		strID = arrFile(i)
		strDelDetail=strDelDetail & DeleteByMaterialID(fso,strID)
	Next
	strDelDetail=strDelDetail & vbCr
DeleteArrayFile =strDelDetail
End Function

'===============================================================删除strID对应的所有文件
Function DeleteByMaterialID(fso,strID)  
		
	'真实路径
	pathKeyfram="\\172.26.142.82\server3_fs1\keyframe\"
	pathUploadSD="\\172.26.142.82\server3_fs1\uploadsd\"
	pathPrepareSD="\\172.26.142.81\server2_fs1\preparesd\"
	pathLow="\\172.26.142.99\lowstream\"

	 
	strRetDetail=""
	cnt=0
	strTem=pathKeyfram & strID & "_icon.dkf"
	bSucc=DeleteFile(fso,strTem)
	If bSucc =True Then
		strRetDetail=strRetDetail & strTem & vbCr
		cnt=cnt+1
	End If
	strTem=pathUploadSD & strID & "_sd.mxf"
	bSucc=DeleteFile(fso,strTem)
	If bSucc =True Then
		strRetDetail=strRetDetail & strTem & vbCr
		cnt=cnt+1
	End If
	strTem=pathPrepareSD & strID & "_sd.mxf"
	bSucc=DeleteFile(fso,strTem)
	If bSucc =True Then
		strRetDetail=strRetDetail & strTem & vbCr
		cnt=cnt+1
	End If
	strTem=pathLow & strID & "_lsd.wmv"
	bSucc=DeleteFile(fso,strTem)
	If bSucc =True Then
		strRetDetail=strRetDetail & strTem & vbCr
		cnt=cnt+1
	End If
	strRetDetail="共删除" & cnt & "个文件，文件信息：" & vbCr & strRetDetail
DeleteByMaterialID = strRetDetail
End Function


'===============================================================删除单个文件
Function DeleteFile(fso,strFile)  
	bRet=True
	If fso.FileExists(strFile) Then 
		Set f1=fso.GetFile(strFile)
		fso.DeleteFile f1
		bRet=True
	Else
		bRet=False
	End If
DeleteFile = bRet
End Function


 

 文件改名.vbs

'======================================================================================
'dim ws,fso,f,fd,files,tmpname,url,strResultFileName,preFix
set ws=createobject("wscript.shell") 
set fso = CreateObject("Scripting.FileSystemObject") 

'真实路径
	'关键帧
	pathKeyfram="\\172.26.142.82\server3_fs1\keyframe\"
	'上载区
	pathUploadSD="\\172.26.142.82\server3_fs1\uploadsd\"
	pathUploadHD="\\172.26.142.82\server3_fs1\uploadhd\"
	'备播区
	pathPrepareSD="\\172.26.142.81\server2_fs1\preparesd\"
	pathPrepareHD="\\172.26.142.83\server4_fs1\preparehd\"
	'低码区
	pathLowSD="\\172.26.142.99\lowstream\"
	pathLowHD="\\172.26.142.81\server2_fs1\lowerhd\"
'建立一个LOG文件
	strResultFileName=ws.currentdirectory
	strResultFileName =replace(strResultFileName ,"\","+")
	strResultFileName =replace(strResultFileName ,".","_")
	strResultFileName =replace(strResultFileName ,":","=")
	strResultFileName= strResultFileName & "_" &  TimeStr(Now) & "LOG.txt"
set fLog=fso.createtextfile(strResultFileName,2,ture)

'开始重命名
	'关键帧
	cnt=ReName(fso,pathKeyfram,"_ICON.DKF",".DKF",fLog) 
	'上载区
	cnt=ReName(fso,pathUploadSD,"S_SD.mxf","S.mxf",fLog) 
	cnt=ReName(fso,pathUploadHD,"H_HD.mxf","H.mxf",fLog) 
	'备播区
	cnt=ReName(fso,pathPrepareSD,"S_SD.mxf","S.mxf",fLog) 
	cnt=ReName(fso,pathPrepareHD,"H_HD.mxf","H.mxf",fLog) 
	'低码区
	cnt=ReName(fso,pathLowSD,"S_LSD.wmv","S.wmv",fLog)
	cnt=ReName(fso,pathLowSD,"S_LS.wmv","S.wmv",fLog)
	cnt=ReName(fso,pathLowSD,"SL.wmv","S.wmv",fLog) 	
	
	cnt=ReName(fso,pathLowHD,"H_LHD.wmv","H.wmv",fLog)
	cnt=ReName(fso,pathLowHD,"H_LH.wmv","H.wmv",fLog)
	cnt=ReName(fso,pathLowHD,"HL.wmv","H.wmv",fLog)
	cnt=ReName(fso,pathLowHD,"H_LS.wmv","H.wmv",fLog)

fLog.Close
MsgBox "   完成！"
'===================================================================================对指定文件夹下文件重命名
Function ReName(fso,path,strOld,strNew,fLog)  
	fLog.writeline "正在重命名：" & path & "    " &  strOld & "->" & strNew
	set fd = fso.getfolder(path) 
	set files=fd.files 
	newName=""
	oldName=""
	nDealedCnt=0
	for each f in files
		newName=f.Name
		oldName=f.Name
		If InStr(oldName,strOld)>0  Then 	
			newName=Replace( newName,strOld,strNew,1,1)
			'newName=Replace( newName,"H.mxf","H_HD.mxf",1,1)
			'重命名
			fso.MoveFile path & oldName, path &  newName
			'记日志
			fLog.writeline oldName & "->" & newName
			'统计
			nDealedCnt=nDealedCnt+1
		End If
	next
	fLog.writeline "已处理：" & nDealedCnt & "个" 
ReName = nDealedCnt
End Function

'===============================================================将时间类型转换成字符串
Function TimeStr(t)  
	ret=Year(t) & "" & Month(t) & "" & Day(t) & "_" & Hour(t) & "" & Minute(t)' & "" & Second(t)
TimeStr = CStr(ret)
End Function


文件改名_改DB.sql

 

 

sql">--标清
UPDATE    MS_FILE
SET     FILENAME = REPLACE(FILENAME, 'S_SD.mxf', 'S.mxf')
WHERE    FILENAME LIKE '%S_SD.mxf%'

update M_UPLOADTASK set TECHNICREPORT=Replace(Cast(TECHNICREPORT as varchar(8000)),'S_SD.mxf','S.mxf')
WHERE    TECHNICREPORT LIKE '%S_SD.mxf%'

--高清
UPDATE    MS_FILE
SET     FILENAME = REPLACE(FILENAME, 'H_HD.mxf', 'H.mxf')
WHERE    FILENAME LIKE '%H_HD.mxf%'

update M_UPLOADTASK set TECHNICREPORT=Replace(Cast(TECHNICREPORT as varchar(8000)),'H_HD.mxf','S.mxf')
WHERE    TECHNICREPORT LIKE '%H_HD.mxf%'

--关键帧

UPDATE    MS_FILE
SET     FILENAME = REPLACE(FILENAME, '_ICON.DKF', '.DKF')
WHERE    FILENAME LIKE '%_ICON.DKF%'

--低码
UPDATE    MS_FILE
SET     FILENAME = REPLACE(FILENAME, 'H_LHD.wmv', 'H.wmv')
WHERE    FILENAME LIKE '%H_LHD.wmv%'
UPDATE    MS_FILE
SET     FILENAME = REPLACE(FILENAME, 'H_LH.wmv', 'H.wmv')
WHERE    FILENAME LIKE '%H_LH.wmv%'
UPDATE    MS_FILE
SET     FILENAME = REPLACE(FILENAME, 'HL.wmv', 'H.wmv')
WHERE    FILENAME LIKE '%HL.wmv%'

UPDATE    MS_FILE 
SET     FILENAME = REPLACE(FILENAME, 'H_LS.wmv', 'H.wmv')
WHERE    FILENAME LIKE '%H_LS.wmv%'


UPDATE    MS_FILE
SET     FILENAME = REPLACE(FILENAME, 'S_LSD.wmv', 'S.wmv')
WHERE    FILENAME LIKE '%S_LSD.wmv%'
UPDATE    MS_FILE
SET     FILENAME = REPLACE(FILENAME, 'S_LS.wmv', 'S.wmv')
WHERE    FILENAME LIKE '%S_LS.wmv%'
UPDATE    MS_FILE
SET     FILENAME = REPLACE(FILENAME, 'SL.wmv', 'S.wmv')
WHERE    FILENAME LIKE '%SL.wmv%'


 

 


http://www.niftyadmin.cn/n/1310382.html

相关文章

递归查询文件名.vbs

递归查询文件名.vbs

递归查询文件名.vbs dim ws,fso,f,fd,files,tmpname,url,result,preFixset wscreateobject("wscript.shell") set fso CreateObject("Scripting.FileSystemObject") set fd fso.getfolder(ws.currentdirectory) set filesfd.files 生成前缀resultws.cur…
阅读更多...
SQL Server中处理死锁

SQL Server中处理死锁

查询方法1_处理死锁_存储过程.SQL /*--处理死锁-- 查看当前进程,或死锁进程,并能自动杀掉死进程-- 因为是针对死的,所以如果有死锁进程,只能查看死锁进程-- 当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程--邹建 2004.4----调用示例exec p_lockinfo--*/create proc…
阅读更多...
提高工作效率

提高工作效率

阅读更多...
JS函数和变量名称冲突

JS函数和变量名称冲突

在JS中如果函数名与变量名冲突&#xff0c;JS是怎么执行的&#xff1f; 1 <script> 2 console.log(sum);//function sum(){} 3 4 function sum(){} 5 var sum 10; 6 7 console.log(sum);//10 8 </script> View…
阅读更多...
服务实例（Service Instance）生命周期如何控制

服务实例（Service Instance）生命周期如何控制

服务调用的目的体现在对某项服务功能的消费上&#xff0c;而功能的实现又定义在相应的服务类型中。不论WCF服务端框架处理服务调用请求的流程有多么复杂&#xff0c;最 终都落实在服务实例的激活和操作方法的执行上面。WCF中的实例管理&#xff08;Instance Management&#xf…
阅读更多...
python全栈自学笔记-day001

python全栈自学笔记-day001

1.计算机是什么 主板cpu内存 cpu: 主频, 核数&#xff08;16&#xff09; 内存&#xff1a;大小(8G, 16G, 32G) 型号: DDR3, DDR4, DDR5, 主频(海盗船&#xff0c;玩家国度) 显卡:　显存。型号(N-GTX 1080 TI, A)。位宽 240显卡(512MB) 210 105 硬盘: 西数。希捷。日立&#x…
阅读更多...
CExcel_DEMO

CExcel_DEMO

#pragma once#include <afxdb.h>#include <odbcinst.h>class CExcel_DEMO{public:CExcel_DEMO(void);~CExcel_DEMO(void);//创建并写入Excel文件void WriteToExcel(){CDatabase database;CString sDriver GetExcelDriver();// _T("MICROSOFT EXCEL DRIVER (*.…
阅读更多...
python3-sql解析库——sqlparse

python3-sql解析库——sqlparse

1.官方文档 https://sqlparse.readthedocs.io/en/latest/ 2.快速开始 使用pip或者conda安装&#xff1a; conda install sqlparse 使用官网示例快速入门&#xff0c;使用sqlparse的三大常用功能&#xff1a; # -*- coding:UTF-8 -*- import sqlparsesql "select id,name_…
阅读更多...
最新文章

Copyright @ 2022~2023