ammartahir1978
asked on
how to change the file name
Hi Guys,
I have this script which create a text file to CSV file, currently the csv file name is po.<number>.date.time.csv.
I want to change it to . po.<purchase order number>.date.time.csv please.
code attached. i know the the file name is created in the second section of the code but dont know how to replace it with purchase order number.
Regards,
Ammar
I have this script which create a text file to CSV file, currently the csv file name is po.<number>.date.time.csv.
I want to change it to . po.<purchase order number>.date.time.csv please.
code attached. i know the the file name is created in the second section of the code but dont know how to replace it with purchase order number.
Regards,
Ammar
Option Explicit
' Declare global variables
Dim objFSO
Dim strDateTimeStamp
' Specify folder locations
Const strFtpFolder = "D:\LABELS\ChessODBCFiles\BedfordPO"
Const strLocalFolder = "D:\LABELS\ChessODBCFiles\BedfordPO"
Const strProcessedFolder = "D:\LABELS\ChessODBCFiles\BedfordPO\Processed"
' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Quit if needed folders don't exist
If Not objFSO.FolderExists(strFtpFolder) Then
Wscript.Echo Now & " ERROR: FTP folder """ & strFtpFolder & """ does not exist, quiting."
Wscript.Quit
End If
If Not objFSO.FolderExists(strLocalFolder) Then
Wscript.Echo Now & " ERROR: Local folder """ & strLocalFolder & """ does not exist, quiting."
Wscript.Quit
End If
If Not objFSO.FolderExists(strProcessedFolder) Then
Wscript.Echo Now & " ERROR: Processed folder """ & strProcessedFolder & """ does not exist, quiting."
Wscript.Quit
End If
' Copy *.PRE files from FTP folder to local work folder
objFSO.CopyFile strFtpFolder & "\*.pre", strLocalFolder
' Build date/time stamp for converted files
strDateTimeStamp = DateTimeStamp(Now)
' Call subroutine to process all files in the local folder
ProcessFolder objFSO.GetFolder(strLocalFolder)
' Move all *.PRE and *.CSV files from local folder to proessed folder
objFSO.MoveFile strLocalFolder & "\*.pre", strProcessedFolder
objFSO.MoveFile strLocalFolder & "\*.csv", strProcessedFolder
' Done
Wscript.Quit
Sub ProcessFolder(objFolder)
' Declare local variables
Dim objFile
' Check each file in this folder
For Each objFile In objFolder.Files
' Make sure it is a file to process
If LCase(Right(objFile.Name, 4)) = ".pre" Then
' Call subroutine to process this file
ProcessFile(objFile)
End If
Next
End Sub
Sub ProcessFile(objFile)
' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
' Delcare local variables
Dim objFileIn
Dim objFileOut
Dim strData
Dim strOutput
Dim strProcessMode
Dim objRegExp
Dim objMatch
Dim strPO
Dim strDateOrdered
Dim strDateExpected
Dim strSupplier
Dim strLine
Dim strProduct
Dim strQty
Dim strUOM
Dim intNumber
Dim i
' Open input and output files
Set objFileIn = objFSO.OpenTextFile(objFile.Path, ForReading, False, TriStateUseDefault)
Set objFileOut = objFSO.OpenTextFile(BuildFileName(objFile), ForWriting, True)
' Write CSV header line
' objFileOut.WriteLine "PO,DateOrdered,DateExpected,CustomerAccount,Line,ProductQty,UOM"
' Create reguglar expression object
Set objRegExp = New RegExp
' Clear out data extract variables (header)
strProcessMode = "Header"
strPO = ""
strDateOrdered = ""
strDateExpected = ""
strSupplier = ""
i = 0
' Read input file line by line
Do While Not objFileIn.AtEndOfStream
' Read next line from text file
strData = objFileIn.ReadLine
i = i + 1
' Skip blank lines
If strData <> "" Then
' Process line differently if we are looking for header data, or detail item lines
Select Case strProcessMode
Case "Header"
' Look for PO line
objRegExp.Pattern = "\b(.*)\b[ ]*Purchase Order No\.[ ]*\b[0-9]+/(.*)\b"
objRegExp.IgnoreCase = True
objRegExp.Global = True
Set objMatch = objRegExp.Execute(strData)
If objMatch.Count > 0 Then
strDateOrdered = Trim(objMatch(0).SubMatches(0))
strPO = objMatch(0).SubMatches(1)
End If
' Look for DateExpected line
objRegExp.Pattern = "\bRequired Date\:[ ]*\b(.*)\b"
objRegExp.IgnoreCase = True
objRegExp.Global = True
Set objMatch = objRegExp.Execute(strData)
If objMatch.Count > 0 Then
strDateExpected = Trim(objMatch(0).SubMatches(0))
End If
If i = 14 Then
' Look for CustomerAccount line
objRegExp.Pattern = "\b(.*)\b"
objRegExp.IgnoreCase = True
objRegExp.Global = True
Set objMatch = objRegExp.Execute(strData)
If objMatch.Count > 0 Then
strSupplier = Trim(objMatch(0).SubMatches(0))
' strOutput = "H," & strPO & "," & strSupplier & "," & YYYYMMDD(strDateOrdered) & "," & YYYYMMDD(strDateExpected) & ",,,,,,+PURCHASE+"
' objFileOut.WriteLine strOutput
' This is last line of header info, switch to detail mode
strProcessMode = "Detail"
' Clear out data extract variables (detail)
strLine = ""
strProduct = ""
strQty = ""
strUOM = ""
intNumber = 0
End If
End If
Case "Detail"
' Get data from first line of item info
objRegExp.Pattern = "\b(\d{4})\s\b(\S*)\b\s*\b(.{30})\s+(\S+)\s+(\S+)\s+(\S+)\b"
objRegExp.IgnoreCase = True
objRegExp.Global = True
Set objMatch = objRegExp.Execute(strData)
If objMatch.Count > 0 Then
strLine = Trim(objMatch(0).SubMatches(0))
strProduct = Trim(objMatch(0).SubMatches(1))
strQty = Trim(objMatch(0).SubMatches(3))
strProcessMode = "Detail2"
End If
Case "Detail2"
' Get UOM from second line of item info
intNumber = intNumber + 1
strUOM = Trim(Mid(strData, 65))
If LCase(strUom) = "each" Then
strUom = "Unit"
End If
' This is last line of item detail info, write out a detail line
' strOutput = strPO & "," & strSupplier & "," & strDateOrdered & "," & strDateExpected & "," & strSupplier & "," & strLine & "," & strProduct & "," & strQty & "," & strUOM
strOutput = strPO & "/ 1," & strSupplier & "," & YYYYMMDD(strDateOrdered) & "," & YYYYMMDD(strDateExpected) & ",,,,," & intNumber & "," & strProduct & "," & strQty & ",," & strUOM & ",,,,"
objFileOut.WriteLine strOutput
' Clear out data extract variables (detail)
strLine = ""
strProduct = ""
strQty = ""
strUOM = ""
strProcessMode = "Detail"
Case Else
End Select
End If
Loop
' Close files
objFileIn.Close
objFileOut.Close
End Sub
Function BuildFileName(objFile)
BuildFileName = objFSO.GetParentFolderName(objFile.Path) & "\" & objFSO.GetBaseName(objFile.Path) & "." & strDateTimeStamp & ".csv"
End Function
' Build string of current date time (YYYYMMDD.HHMM)
Function DateTimeStamp(dtmDateTime)
DateTimeStamp = Year(dtmDateTime) & Right("0" & Month(dtmDateTime), 2) & Right("0" & Day(dtmDateTime), 2) & "." & Right("0" & Hour(dtmDateTime), 2) & Right("0" & Minute(dtmDateTime), 2)
End Function
Function YYYYMMDD(ByVal strDate)
If Len(strDate) = 7 Then
strDate = "0" & strDate
End If
YYYYMMDD = "20" & Mid(strDate, 7, 2) & Mid(strDate, 4, 2) & Mid(strDate, 1, 2)
End Function
Function BuildFileName(objFile)
BuildFileName = objFSO.GetParentFolderName(objFile.Path) & "\" & objFSO.GetBaseName(objFile.Path) & "." & strDateTimeStamp & ".csv"
End Function
ASKER
thanks Ryan,
how do i do that?
how do i do that?
what's the generated file name currently looks like?
you probably can use a function like this to rename a file:
you probably can use a function like this to rename a file:
function renamefile(a, b)
Dim Fso
Set Fso = WScript.CreateObject("Scripting.FileSystemObject")
Fso.MoveFile a, b
end function
ASKER
currently it looks like
po.10004.pre (is the file i pick then script converts it in to the format in csv)
po.10004.datestamp.timesta mp.csv
po.10004.pre (is the file i pick then script converts it in to the format in csv)
po.10004.datestamp.timesta
I adjusted the code to handle the change you requested, give it a test and see if it works as you wanted.
Option Explicit
' Declare global variables
Dim objFSO
Dim strDateTimeStamp
' Specify folder locations
Const strFtpFolder = "D:\LABELS\ChessODBCFiles\BedfordPO"
Const strLocalFolder = "D:\LABELS\ChessODBCFiles\BedfordPO"
Const strProcessedFolder = "D:\LABELS\ChessODBCFiles\BedfordPO\Processed"
' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Quit if needed folders don't exist
If Not objFSO.FolderExists(strFtpFolder) Then
Wscript.Echo Now & " ERROR: FTP folder """ & strFtpFolder & """ does not exist, quiting."
Wscript.Quit
End If
If Not objFSO.FolderExists(strLocalFolder) Then
Wscript.Echo Now & " ERROR: Local folder """ & strLocalFolder & """ does not exist, quiting."
Wscript.Quit
End If
If Not objFSO.FolderExists(strProcessedFolder) Then
Wscript.Echo Now & " ERROR: Processed folder """ & strProcessedFolder & """ does not exist, quiting."
Wscript.Quit
End If
' Copy *.PRE files from FTP folder to local work folder
objFSO.CopyFile strFtpFolder & "\*.pre", strLocalFolder
' Build date/time stamp for converted files
strDateTimeStamp = DateTimeStamp(Now)
' Call subroutine to process all files in the local folder
ProcessFolder objFSO.GetFolder(strLocalFolder)
' Move all *.PRE and *.CSV files from local folder to proessed folder
objFSO.MoveFile strLocalFolder & "\*.pre", strProcessedFolder
objFSO.MoveFile strLocalFolder & "\*.csv", strProcessedFolder
' Done
Wscript.Quit
Sub ProcessFolder(objFolder)
' Declare local variables
Dim objFile
' Check each file in this folder
For Each objFile In objFolder.Files
' Make sure it is a file to process
If LCase(Right(objFile.Name, 4)) = ".pre" Then
' Call subroutine to process this file
ProcessFile(objFile)
End If
Next
End Sub
Sub ProcessFile(objFile)
' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
' Delcare local variables
Dim objFileIn
Dim objFileOut
Dim strData
Dim strOutput
Dim strProcessMode
Dim objRegExp
Dim objMatch
Dim strPO
Dim strDateOrdered
Dim strDateExpected
Dim strSupplier
Dim strLine
Dim strProduct
Dim strQty
Dim strUOM
Dim intNumber
Dim i
' Open input and output files
Set objFileIn = objFSO.OpenTextFile(objFile.Path, ForReading, False, TriStateUseDefault)
blnOutputFileOpen = False
' Create reguglar expression object
Set objRegExp = New RegExp
' Clear out data extract variables (header)
strProcessMode = "Header"
strPO = ""
strDateOrdered = ""
strDateExpected = ""
strSupplier = ""
i = 0
' Read input file line by line
Do While Not objFileIn.AtEndOfStream
' Read next line from text file
strData = objFileIn.ReadLine
i = i + 1
' Skip blank lines
If strData <> "" Then
' Process line differently if we are looking for header data, or detail item lines
Select Case strProcessMode
Case "Header"
' Look for PO line
objRegExp.Pattern = "\b(.*)\b[ ]*Purchase Order No\.[ ]*\b[0-9]+/(.*)\b"
objRegExp.IgnoreCase = True
objRegExp.Global = True
Set objMatch = objRegExp.Execute(strData)
If objMatch.Count > 0 Then
strDateOrdered = Trim(objMatch(0).SubMatches(0))
strPO = objMatch(0).SubMatches(1)
If blnOutputFileOpen = False Then
End If
Set objFileOut = objFSO.OpenTextFile(BuildFileName(objFile, strPO), ForWriting, True)
' Write CSV header line
' objFileOut.WriteLine "PO,DateOrdered,DateExpected,CustomerAccount,Line,ProductQty,UOM"
End If
' Look for DateExpected line
objRegExp.Pattern = "\bRequired Date\:[ ]*\b(.*)\b"
objRegExp.IgnoreCase = True
objRegExp.Global = True
Set objMatch = objRegExp.Execute(strData)
If objMatch.Count > 0 Then
strDateExpected = Trim(objMatch(0).SubMatches(0))
End If
If i = 14 Then
' Look for CustomerAccount line
objRegExp.Pattern = "\b(.*)\b"
objRegExp.IgnoreCase = True
objRegExp.Global = True
Set objMatch = objRegExp.Execute(strData)
If objMatch.Count > 0 Then
strSupplier = Trim(objMatch(0).SubMatches(0))
' strOutput = "H," & strPO & "," & strSupplier & "," & YYYYMMDD(strDateOrdered) & "," & YYYYMMDD(strDateExpected) & ",,,,,,+PURCHASE+"
' objFileOut.WriteLine strOutput
' This is last line of header info, switch to detail mode
strProcessMode = "Detail"
' Clear out data extract variables (detail)
strLine = ""
strProduct = ""
strQty = ""
strUOM = ""
intNumber = 0
End If
End If
Case "Detail"
' Get data from first line of item info
objRegExp.Pattern = "\b(\d{4})\s\b(\S*)\b\s*\b(.{30})\s+(\S+)\s+(\S+)\s+(\S+)\b"
objRegExp.IgnoreCase = True
objRegExp.Global = True
Set objMatch = objRegExp.Execute(strData)
If objMatch.Count > 0 Then
strLine = Trim(objMatch(0).SubMatches(0))
strProduct = Trim(objMatch(0).SubMatches(1))
strQty = Trim(objMatch(0).SubMatches(3))
strProcessMode = "Detail2"
End If
Case "Detail2"
' Get UOM from second line of item info
intNumber = intNumber + 1
strUOM = Trim(Mid(strData, 65))
If LCase(strUom) = "each" Then
strUom = "Unit"
End If
' This is last line of item detail info, write out a detail line
' strOutput = strPO & "," & strSupplier & "," & strDateOrdered & "," & strDateExpected & "," & strSupplier & "," & strLine & "," & strProduct & "," & strQty & "," & strUOM
strOutput = strPO & "/ 1," & strSupplier & "," & YYYYMMDD(strDateOrdered) & "," & YYYYMMDD(strDateExpected) & ",,,,," & intNumber & "," & strProduct & "," & strQty & ",," & strUOM & ",,,,"
objFileOut.WriteLine strOutput
' Clear out data extract variables (detail)
strLine = ""
strProduct = ""
strQty = ""
strUOM = ""
strProcessMode = "Detail"
Case Else
End Select
End If
Loop
' Close files
objFileIn.Close
objFileOut.Close
End Sub
Function BuildFileName(objFile, strPO)
BuildFileName = objFSO.GetParentFolderName(objFile.Path) & "\po." & strPO & "." & strDateTimeStamp & ".csv"
End Function
' Build string of current date time (YYYYMMDD.HHMM)
Function DateTimeStamp(dtmDateTime)
DateTimeStamp = Year(dtmDateTime) & Right("0" & Month(dtmDateTime), 2) & Right("0" & Day(dtmDateTime), 2) & "." & Right("0" & Hour(dtmDateTime), 2) & Right("0" & Minute(dtmDateTime), 2)
End Function
Function YYYYMMDD(ByVal strDate)
If Len(strDate) = 7 Then
strDate = "0" & strDate
End If
YYYYMMDD = "20" & Mid(strDate, 7, 2) & Mid(strDate, 4, 2) & Mid(strDate, 1, 2)
End Function
~bp
ASKER
thanks Bill this is what i get when i run script
Untitled12.png
Untitled12.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I AM THRILLED AND DEEPLY IMPRESSED BY BILL'S LEVEL OF EXPERTISE AND ACTUALLY HE HAS INSPIRED ME TO LEARN SCRIPTING.
I CAN NOT THANK ENOUGH FOR HIS HELP.
I CAN NOT THANK ENOUGH FOR HIS HELP.
if yes, I suggest you do a file rename after your output file is being generated.