Hi, mrsneaky-ga:
I've proceeded to performing the requirements based on the
intial table design given above. The coding done assumes
a locally hosted SQL Server 2000 database called "mrsneaky"
in which the CarSales table has already been created using
the script given above.
Next let's describe the "file specification". I made some
small changes to your filenaming conventions. Input files
should have the extension .XNC (for transactions ?), and
the extension gets changed to .DNE (for success, per your
requirements) or .XNR (for errors) after moving these to
the .\DONE subdirectory. Also I chose C:\MyXNC as the main
file directory (rather than C:\TEST1, as you requested).
> > > > > > File Specification BEGINS > > > > > > > > >
Layout of "80 byte" fixed length file records:
FIELD LENGTH COLUMNS Notes:
ncomma 10 1-10 Numeric, but may contain commas
nwhole 5 11-15 Integer numeric (no commas)
nthous 9 16-24 Three places after decimal point
XXXXX.XXX (right justified)
csngle 1 25 Any character
dtonly 10 26-35 A date in format MM/DD/YYYY
dttime 26 36-61 Datetime MM/DD/YYYY HH:MM:SS.mmm
with optional AM or PM appended
nmoney 10 62-71 Two places after decimal point
XXXXXXX.XX (right justified)
strxox 3 72-74 Three letters with middle vowel
strnnx 3 75-77 Two digits and a letter or space
strabc 3 78-80 One to three captial letters in
ascending order (left justified)
An ASCII character set is assumed. Note that records may be
terminated by carriage returns, line feeds, or both, or in
the case of the last record by the end of file.
All fields are required to be nonempty, i.e. at least one
nonspace character (except csngle, which will be treated as
a space character if that is provided) but padded with space
character or optionally zeros in the case of numeric values,
as necessary.
It is recommended that numeric fields be right justified and
character fields left justified; however apart from specific
requirements noted above the implementation allows for small
amounts of flexibility.
< < < < < < END OF File Specification < < < < < < < < <
A sample file is a little hard to present here because the
Google Answers text here wraps after 70 columns, so bear in
mind that the following needs to be rejoined into a single
line (and also that a space occupies the 80th column):
> > > > > C:\MyXNC\New Text Document.xnc BEGINS > > > > >
12,345,678 500 3.142P04/01/200004/01/1998 12:15:00.012 AM1234567.89XOX99XAB
< < < < < END OF C:\MyXNC\New Text Document.xnc < < < < <
Note that my file spec allows for the lines to exceed 80
columns and places no restrictions (except ASCII encoding)
on what additional columns may contain. In my experience
it is good to plan for this type of "extensibility" and
does not cost much. One drawback is that it can make it
harder to check for errors caused by lines running together,
but my purpose here is very much weighted toward coding the
database functions in a readable and "solid" manner without
getting overly bogged down in defensive coding issues.
I will give you a link to a place on the Web where you can
download the zipped up project files. However let me give
the VB.Net code below, after a few words about how it was
constructed.
I created a "solution" called MrSneaky that currently has
only a single project Importxt.
I chose a "Standard EXE" Windows executable from among the
VB.Net project types. On the intial form (Form1) I placed
only a minimum number of items (controls) using the IDE:
- a button (Button1)
- a label (Label1)
Apart from changing the Form propert Text to "Import Text"
(so that appears in the form's title bar) and using the IDE
to create the shell code for Button1_Click, all this code
was edited in manually to the basic program outline. The
process started by adding certain variable declarations at
the top of the program (just above the computer generated
code that says "Windows Form Designer Generated Code". In
fact these declaration modify the basic "Class" definition
of Form1, to incorporate these extra "global" (instance)
variables:
Dim bProcess As Boolean
Dim tmrFndTx As System.Timers.Timer
Dim dirMyXNC As System.IO.DirectoryInfo
Dim cnSneaky As System.Data.SqlClient.SqlConnection
Dim cmSneaky As System.Data.SqlClient.SqlCommand
Declaring those variables only makes a placeholder for each
of them. The contents get initialized at the point in the
computer generated subroutine New() for application class
Form1, under the comment that says:
'Add any initialization after the InitializeComponent() call
A quick explanation of these variables:
- bProcess is tied to the "state" of the "command" button
and reflects whether the process is running or not.
- tmrFndTx is a timer that fires (when enabled) after 20 secs.
The enabled state coincides with bProcess, and the firing
event is handled by a routine that looks for the .XNC files,
then resets the enabled state.
- dirMyXNC is a .Net runtime object used to check for the
existence of the C:\MyXNC directory (and create it if it
does not exist) and some related tasks
- cnSneaky is an ADO.Net database "connection" object, and
cmSneaky is a "command" object derived from it. Together
they are used to communicate with the database and insert
records into the table CarSales.
The code currently reads like this (including all computer
generated lines). NOTE: The Google Answers textbox doesn't
preserve leading tabs on lines, so the appearance below will
necessarily be only a rough approximation of what you'd see
in the IDE once the project files are downloaded. I've tried
to avoid any unintended line wrapping, but I apologize in
advance where this occurs, e.g. within the computer generated
code:
> > > > > > > > > Form1.vb BEGINS > > > > > > > > > > >
Public Class Form1
Inherits System.Windows.Forms.Form
Dim bProcess As Boolean
Dim tmrFndTx As System.Timers.Timer
Dim dirMyXNC As System.IO.DirectoryInfo
Dim cnSneaky As System.Data.SqlClient.SqlConnection
Dim cmSneaky As System.Data.SqlClient.SqlCommand
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
bProcess = False
tmrFndTx = New System.Timers.Timer(20000)
AddHandler tmrFndTx.Elapsed, AddressOf OnTimedFind
tmrFndTx.AutoReset = False
tmrFndTx.Enabled = False
If System.IO.Directory.Exists("C:\MyXNC") Then
dirMyXNC = New System.IO.DirectoryInfo("C:\MyXNC")
Else
dirMyXNC = System.IO.Directory.CreateDirectory("C:\MyXNC")
End If
If Not System.IO.Directory.Exists("C:\MyXNC\Done") Then
dirMyXNC.CreateSubdirectory("Done")
End If
cnSneaky = New System.Data.SqlClient.SqlConnection
cnSneaky.ConnectionString = "Data Source=localhost;" _
+ "Integrated Security=SSPI;Initial Catalog=mrsneaky"
cmSneaky = cnSneaky.CreateCommand()
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents Label1 As System.Windows.Forms.Label
<System.Diagnostics.DebuggerStepThrough()> Private _
Sub InitializeComponent()
Me.Button1 = New System.Windows.Forms.Button
Me.Label1 = New System.Windows.Forms.Label
Me.SuspendLayout()
'
'Button1
'
Me.Button1.Font = New System.Drawing.Font("Microsoft Sans Serif", _
9.75!, System.Drawing.FontStyle.Bold, _
System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Button1.Location = New System.Drawing.Point(72, 64)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(128, 40)
Me.Button1.TabIndex = 0
Me.Button1.Text = "Start"
'
'Label1
'
Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", _
10.0!, System.Drawing.FontStyle.Bold, _
System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Label1.Location = New System.Drawing.Point(72, 24)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(128, 23)
Me.Label1.TabIndex = 1
Me.Label1.Text = "Import Text to SQL"
Me.Label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 165)
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.Button1)
Me.Name = "Form1"
Me.Text = "Import Text"
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub OnTimedFind(ByVal source As Object, _
ByVal e As System.Timers.ElapsedEventArgs)
' unless boolean bProcess is True, simply return
If Not Me.bProcess Then Return
' make Button1 inactive during this processing
Me.Button1.Enabled = False
' search for files *.xnc in directory C:\MyXNC
Dim fiArr As System.IO.FileInfo() = _
dirMyXNC.GetFiles("*.xnc")
' TODO: sort the entries in fiArr by creation datetime
Dim fi As System.IO.FileInfo
For Each fi In fiArr
Dim result As Boolean = ProcessFileXNC(fi)
' move file to .\Done
Dim pathDoneXNC As String = dirMyXNC.FullName _
+ "\Done\" + fi.Name
fi.MoveTo(pathDoneXNC)
Dim len As Integer = pathDoneXNC.Length
Dim pathDone As String = _
pathDoneXNC.Substring(0, len - 3)
If result Then
' rename to *.dne
System.IO.File.Move(pathDoneXNC, pathDone + "dne")
Else
' rename to *.xnr
System.IO.File.Move(pathDoneXNC, pathDone + "xnr")
End If
Next fi
' restart the Timer object
Me.tmrFndTx.Start()
' reenable the "command button"
Me.Button1.Enabled = True
End Sub
Private Function ProcessFileXNC(ByVal fi As System.IO.FileInfo) _
As Boolean
' extract the transaction data from file, validate and post
Dim dtXNC As System.DateTime
cmSneaky.CommandText = "select GETDATE()"
Try
cnSneaky.Open()
dtXNC = cmSneaky.ExecuteScalar()
Catch ex As Exception
System.Windows.Forms.MessageBox.Show( _
"command failed to execute: " + ex.Message)
Finally
cnSneaky.Close()
End Try
Dim millisc As Integer = dtXNC.Millisecond
Dim strMill As String = millisc.ToString("D3")
Dim strDtTm As String = dtXNC.ToString("s") + "." + strMill
' System.Windows.Forms.MessageBox.Show(strDtTm)
Dim srText As System.IO.StreamReader = fi.OpenText
Dim strLine, strVals As String
Dim result As Boolean = True
strLine = srText.ReadLine()
Do
' parse the line of text into insertable values
strVals = parseCarSales(strDtTm, strLine)
If strVals.Length > 0 Then
cmSneaky.CommandText = strVals
Try
cnSneaky.Open()
cmSneaky.ExecuteNonQuery()
Catch ex As Exception
System.Windows.Forms.MessageBox.Show( _
"insert failed to execute: " + ex.Message)
Return False
Finally
cnSneaky.Close()
End Try
Else
result = False
End If
strLine = srText.ReadLine()
Loop Until strLine Is Nothing
srText.Close()
Return result
End Function
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
bProcess = Not bProcess
tmrFndTx.Enabled = bProcess
If bProcess Then
Try
cnSneaky.Open()
' starting: change button label to Stop from Start
Button1.Text = "Stop"
Catch ex As Exception
' TODO: figure out better strategy for handling
System.Windows.Forms.MessageBox.Show( _
"problem opening connection:" + ex.Message)
bProcess = Not bProcess
tmrFndTx.Enabled = bProcess
Finally
cnSneaky.Close()
End Try
Else
' stopping: change button label to Start from Stop
Button1.Text = "Start"
End If
End Sub
Private Function parseCarSales(ByVal strDtTm As String, _
ByVal strLine As String) As String
' build SQL syntax for the insert statement
Dim strProlog As String = "INSERT into CarSales " _
+ "(timedt, status, ncomma, nwhole, nthous, csngle, " _
+ " dtonly, dttime, nmoney, strxox, strnnx, strabc) " _
+ "VALUES ('" + strDtTm + "', 1, "
Dim strNcomma, strNwhole, strNthous, strCsngle As String
Dim strDtonly, strDttime, strNmoney As String
Dim strXOX, strNNX, strABC As String
' return an empty string if any input data is invalid
Dim bValid As Boolean
' allow for longer lines than strictly required by feed
bValid = (strLine.Length >= 80)
' extract, validate and format ncomma field
If bValid Then
strNcomma = strLine.Substring(0, 10)
' trim white space from beginning and end
strNcomma = strNcomma.Trim()
' remove commas: replace them with empty strings
strNcomma = strNcomma.Replace(",", "")
' validate by checking IsNumeric
bValid = IsNumeric(strNcomma)
End If
' extract, validate and format nwhole field
If bValid Then
strNwhole = strLine.Substring(10, 5)
' trim white space from beginning and end
strNwhole = strNwhole.Trim()
' validate with regular expression
bValid = System.Text.RegularExpressions.Regex.IsMatch( _
strNwhole, "^[+-]?[0-9]+$")
End If
' extract, validate and format nthous field
If bValid Then
strNthous = strLine.Substring(15, 9)
' trim white space from beginning (right justification)
strNthous = strNthous.TrimStart()
' validate with regular expression
bValid = System.Text.RegularExpressions.Regex.IsMatch( _
strNthous, "^[+-]?[0-9]*\.[0-9]{3}$")
End If
' extract, validate and format csngle field
If bValid Then
' format character in single quotes for SQL
strCsngle = "'" + strLine.Substring(24, 1) + "'"
End If
' extract, validate and format dtonly field
If bValid Then
strDtonly = strLine.Substring(25, 10)
' validate by checking IsDate and if it's okay...
bValid = IsDate(strDtonly)
' ... then check Time parts hour & minute are zero
If bValid Then
bValid = (DatePart("h", strDtonly) = 0) _
And (DatePart("n", strDtonly) = 0)
End If
' format date in single quotes for SQL
strDtonly = "'" + strDtonly + "'"
End If
' extract, validate and format dttime field
If bValid Then
strDttime = strLine.Substring(35, 26)
' validate by checking IsDate
bValid = IsDate(strDttime)
' formate date in single quotes for SQL
strDttime = "'" + strDttime + "'"
End If
' extract, validate and format nmoney field
If bValid Then
strNmoney = strLine.Substring(61, 10)
' trim white space from beginning (right justification)
strNmoney = strNmoney.TrimStart()
' validate with regular expression
bValid = System.Text.RegularExpressions.Regex.IsMatch( _
strNmoney, "^[+-]?[0-9]*\.[0-9]{2}$")
End If
' extract, validate and format strxox field
If bValid Then
strXOX = strLine.Substring(71, 3)
' validate with regular expression
bValid = System.Text.RegularExpressions.Regex.IsMatch( _
strXOX, "[a-zA-Z][aeiouAEIOU][a-zA-Z]")
' format string with single quotes for SQL
strXOX = "'" + strXOX + "'"
End If
' extract, validate and format strnnx field
If bValid Then
strNNX = strLine.Substring(74, 3)
' validate with regular expression
bValid = System.Text.RegularExpressions.Regex.IsMatch( _
strNNX, "[0-9][0-9][a-zA-Z]")
' format string with single quotes for SQL
strNNX = "'" + strNNX + "'"
End If
' extract, validate and format strabc field
If bValid Then
strABC = strLine.Substring(77, 3)
' trim white space from end (assume left justification)
strABC = strABC.TrimEnd()
' validate with regular expression and if it's okay...
bValid = System.Text.RegularExpressions.Regex.IsMatch( _
strABC, "[A-Z]+")
' then check the ascending order of characters
If bValid And strABC.Length > 1 Then
For I As Integer = 1 To strABC.Length - 1
bValid = bValid And (strABC.Chars(I - 1) _
<= strABC.Chars(I))
Next I
End If
' format string with single quotes for SQL
strABC = "'" + strABC + "'"
End If
If bValid Then
Return strProlog + strNcomma + ", " _
+ strNwhole + ", " + strNthous + ", " _
+ strCsngle + ", " + strDtonly + ", " _
+ strDttime + ", " + strNmoney + ", " _
+ strXOX + ", " + strNNX + ", " + strABC + ")"
Else
Return ""
End If
End Function
End Class
< < < < < < < END OF Form1.vb < < < < < < < < < < < < <
When I get those project files loaded up on the Web, I'll
post some additional comments about the programming techniques
used, esp. for the validation of fields which I think is of
particular interest to you.
regards, mathtalk-ga |
,
0 Comments
)