| |
djfitzie
Posts: 7
Score: 0
Joined: 10/4/2006
Status: offline
|
Hello,
I am working on a script that reads in data from text files with a dxt extension, runs some checks on each line of data and moves the file to a Process error folder if the data is not within the given limits.
The problem I am having is that when reading in the "QuantityItem" field there are some figures with decimals, this is not a problem if they are within the top few lines (say <10) of the file as it will then show the decimals throughout the file but if there no decimals until further down in the file it will not read or display the decimal place.
I have posted the script below with the red highlighted section being where it runs through the file.
strComputer = "."
Const LOGFILE = "C:\Program Files\Free Scan\DATA\ProcessErrors\Error.log"
Const ForReading = 1 'Open a file for reading only. You can't write to this file.
Const ForWriting = 2 'Open a file for writing.
Const ForAppending = 8 'Open a file and write to the end of the file.
Const Inbox = "C:\Program Files\Free Scan\DATA\Inbox"
Const ProcessErr = "C:\Program Files\Free Scan\DATA\ProcessErrors"
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Dim strFName
Dim strLocFname
Dim strPatternName
Dim ofso
Set oFso = CreateObject("Scripting.FileSystemObject")
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Inbox)
Set colFiles = objFolder.Files
InitLogFile()
'Counters
a = 1
b = 0
c = 0
For Each objFile In colFiles
a = a + 1
WScript.Echo "----------------Next File----------------"
If objFSO.GetExtensionName(objFile) = "dxt" Then 'only check dxt's not dxt_Rs etc
c = c + 1
strFName = objFSO.GetFileName(objFile.Path)
WScript.Echo "File name - " & strFName
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
strPathtoTextFile = Inbox & "\"
'wscript.echo "Path to File - " & strPathtoTextFile
strLocFname = strPathtoTextFile & strFName
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(strLocFname)
If objFile.Size < 600 Then
objFSO.MoveFile strLocFname, "C:\Program Files\Free Scan\Data\ProcessErrors\"
Else
'WScript.Echo "Loc & Filename - " & strLocFname
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
objRecordset.Open "Select * FROM " & strFName & " where LinkIT = 1", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
strPatternName = "PAPER"
x = 6 'Minimum Dollar Value to be checked
y = 10 'Barcodes to be checked must be greater than this
z = 0 'If less than this check as refund sales
l = 1 'Line Number'
i = 0 'If i = 1 then file is moved
Do Until objRecordset.EOF
l = l + 1
'WScript.Echo "Sales " & objRecordSet.Fields.Item("SaleGSTList")
WScript.Echo "Qnty " & objRecordset.Fields.Item(20)
'WScript.Echo "Math " & (objRecordset.Fields.Item("QuantityItem") * 1.15)
If FormatNumber(objRecordSet.Fields.Item("EAN")) < FormatNumber(y) Then
objRecordSet.MoveNext
ElseIf FormatNumber(objRecordSet.Fields.Item("SaleGSTList")) < FormatNumber(x) Then
objRecordSet.MoveNext
Else
numSales = FormatNumber((objRecordset.Fields.Item("SaleGST"))/(objRecordset.Fields.Item("SaleGSTList")),2)
'WScript.Echo "Calculation " & objRecordSet.Fields.Item("SaleGST") & " / " & objRecordSet.Fields.Item("SaleGSTList") & " = " & numSales
numItemHigh = FormatNumber(CSng(objRecordset.Fields.Item("QuantityItem")) + ((CSng(objRecordset.Fields.Item("QuantityItem")) * 0.15)))
'WScript.Echo "Item Quantity High = " & numItemHigh
numItemLow = FormatNumber(CSng(objRecordset.Fields.Item("QuantityItem")) - ((CSng(objRecordset.Fields.Item("QuantityItem")) * 0.15)))
'WScript.Echo "Item Quantity Low = " & numItemLow
If Abs(CSng(numSales)) > Abs(CSng(numItemHigh)) Then 'Dollar Value is too high for quantity of Sales
WriteLogFile 1,"Item " & (objRecordset.Fields.Item("Description")) & " On Line " & l & " Shows " & numSales & " Units Sold Compared to " & (objRecordset.Fields.Item("QuantityItem"))
objRecordSet.MoveNext
i = 1
ElseIf Abs(CSng(numSales)) < Abs(CSng(numItemLow)) Then 'Dollar Value is too low for quantity of Sales
WriteLogFile 1,"Item " & (objRecordset.Fields.Item("Description")) & " On Line " & l & " Shows " & numSales & " Units Sold Compared to " & (objRecordset.Fields.Item("QuantityItem"))
objRecordSet.MoveNext
i = 1
Else
objRecordSet.MoveNext
End If
End If
Loop
objRecordSet.Close
If i = 1 Then
WriteLogFile 2,strFName & " to " & ProcessErr & "\"
objFSO.MoveFile strLocFname, "C:\Program Files\Free Scan\Data\ProcessErrors\"
b = b + 1
End If
End If
End If
Next
WriteLogFile 0,"------Results------"
WriteLogFile 0,a &" Files Checked"
WriteLogFile 0,b &" Files Moved"
WriteLogFile 0,a - c &" Files Ignored"
WriteLogFile 0,"-------------------"
' Add logging to script
Function InitLogFile()
If objFSO.FileExists(LOGFILE) Then
WScript.Echo "File Exists"
Else
oFso.CreateTextFile LOGFILE,True
End if
End Function
'Write to a txt log file
'WriteLogFile 0,"my text , you might want To add results of variables"
Function WriteLogFile(iResult, sLogInfo)
Dim sLog,fFile
If iResult = 0 Then
sLog = Date & " - " & Time & " - INFO - " & sLogInfo
Elseif iResult = 1 Then
sLog = Date & " - " & Time & " - ERROR - " & sLogInfo
ElseIf iResult = 2 Then
sLog = Date & " - " & Time & " - MOVING - " & sLogInfo
End If
Set fFile = oFso.OpenTextFile(LOGFILE,ForAppending,True)
fFile.WriteLine sLog
fFile.Close
End Function
HERE IS A COPY OF WHAT THE DATA LOOKS LIKE
ID|LinkIT|TRDate|ProductID|EAN|Description|QuantityUnits|SaleGST|SaleGSTList|CostGST|Cost|GST|Sale|ClientID|ClientType|UpdateLast|UpdateBy|IDHistProc|ExtractDate|ReceiptDate|QuantityItem
6|1|21/06/2008|3721|047995900700|AMERICAN SPIRIT BLUE 20/200|20|10.56|10.56|9.43|0|0.96|9.6|1|R|21/06/2008 4:43:07 PM||0|||1
6|1|21/06/2008|4052|3|SUNDRY CIGARS|1|2.95|0.29|0.22|0|0.2682|0.26|1|R|21/06/2008 4:43:07 PM||0|||1
6|1|21/06/2008|4569|40306445|DAVIDOFF LIGHTS 20/200|20|10.22|10.22|9.12|0|0.9291|9.29|1|R|21/06/2008 4:43:07 PM||0|||1
6|1|21/06/2008|6784|4032900004341|CAMEL 25s FILTER|200|88.46|88.46|81.91|0|8.0418|80.42|1|R|21/06/2008 4:43:07 PM||0|||1
6|1|21/06/2008|6751|40329963|MORE MENTHOL HARD 20/200|40|22.04|11.02|19.68|0|2.0036|20.04|1|R|21/06/2008 4:43:07 PM||0|||2
6|1|21/06/2008|3623|5000432001229|SUPERKINGS RICH BLUE 20/200|200|74.25|77.97|74.25|0|6.75|70.88|17|R|21/06/2008 4:43:07 PM||0|||1
6|1|21/06/2008|3621|5000432001731|SUPERKINGS MENTHOL 20/200|200|74.25|77.97|74.25|0|6.75|70.88|17|R|21/06/2008 4:43:07 PM||0|||1
6|1|21/06/2008|3620|50432127|SUPERKINGS SKY BLUE 20/200|20|8.1|8.1|7.43|0|0.7364|7.36|1|R|21/06/2008 4:43:07 PM||0|||1
Any suggestions or help would be greatly appreciated.
|
|
Printable Version
ADODB Recordset Data Reading Problem - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread