Introduction

Oft times we are forced into situations where we clearly need to think outside of the box. A case at hand arose early in 2016 where one of our client’s required a “quick and dirty” mechanism to ascertain which proposal data were contained

within CSV files that are generated as a part of a Dynamics CRM data transfer from the cloud to their local data warehouse.

Unorthodox Yes!!  Abnormal No!!

But I am getting ahead of myself. A bit of high level history: 
Within the client’s firm, project proposals are generated within Dynamics CRM.
Portions of this data including proposed costing must be captured and stored within the local data warehouses (for reporting purposes).
This data is pushed to the data warehouse from Dynamics via a process that is set to “kick off” upon the completion of data entry within Dynamics CRM.

Every two minutes a SQL Server Agent job, polls to see if new data has been added to the warehouse.
If there is new data, these new rows are extracted to a CSV file,
otherwise the process goes to sleep. As the process of how this is achieved is out of the realm of this discussion, the avid reader is referred to an article that I wrote last year for SQL Shack.

http://www.sqlshack.com/creating-dynamically-generated-csv-files-containing-sql-server-data/

One point must be critically understood before we venture into defining a solution for the issue at hand. As pointed out in the article (listed above), extract files were created with
the company name as the prefix and the date and time of the file creation was appended at run time (see the sample screen shot below).

Now, the contents of these CSV files are the vital data that the client requires. A sample proposal number may be seen on the second line of the screen shot below (its actual label is ”quote”).

The contents of each of the CSV files is for one and only one proposal, thus the proposal number is the same for all records.

The plot now “thickens”: The client requested that the file names no longer be the name of the firm along with the date and time of creation (see above) but rather that the files be named with the actual proposal numbers.

For those of us whom have worked on such issues, it is often challenging to perform operating system tasks such as dynamic file naming from within SQL Server Integration Services, once package execution and processing has commenced.

A drum roll please maestro!!! Let us see how this task may be achieved, however let us utilize .NET to achieve our goals, AFTER the extract file is created.

Getting started

The key to our solution is a nifty tool that the good people at Microsoft inserted into their earlier versions of Visual Studio. We are going to be utilizing a “FileSystemWatcher” to achieve our goals.

.In our case we shall be utilizing VB.Net, however we could utilize C# as well.
The application will be running 24 x 7 and NO!!, it does not tie up much CPU as it lies dormant until a “file created” event is detected.

The important point for the reader to retain is that “FileSystemWatchers” have events and we shall utilize these events to our benefit.

The eagle-eyed reader will remember that the data is extracted from Dynamics and placed into the client’s warehouse. The extraction job runs every two minutes and will extract one and only one proposal at a time.
We might say that it “Pops the stack”. The end result being that a disk based file is “created”.

We begin by creating a new Visual Basic .Net Windows application. We shall be utilizing Visual Studio 2015. Upon arrival at our first data entry screen, we give our VB.Net project a name (see below)

We click “OK” to continue and we arrive at our first form (see below).

The avid reader will note that we have a blank form visible. Believe it or not, we shall NOT be adding any visual controls to the screen itself short of the file system watcher as may be seen above.

The “FileSystemWatcher” is found upon the “component” menu as may be seen below:

Configuring the “FileSystemWatcher”

We begin by configuring the FileSystemWatcher’s Property page (see above and to the bottom right.
The “Watcher” is told  that it should be looking at the C:\Stellenbosch directory for new files HOWEVER it should only concern itself with files that satisfy the mask “Altico_*.csv.

This done.

By double clicking upon the “FileSystemWatcher” we open the code page for the watcher.

We begin by creating a PUBLIC subroutine which we shall call:  “Public Sub ProcessFile()”.

Having achieved this, we define a few variables that we shall be utilizing throughout the course of this exercise.

At the top level, we create a GLOBAL string variable called “Proposal” The purpose of this variable will become apparent within a few minutes.

I declare a few local variables within our subroutine.

Dim I, Z, Length, kount, before As Integer

Dim substr As String

I = 1

Being a guy whom was brought up as a FORTRAN programmer, the astute reader will note that I have used single letter to define counters. Let us put it that “Old habits die hard!”

We must understand that in order to ascertain the proposal number that we need read the file and that we really wish to process the whole file in one go.

This said, we shall be utilizing a System.IO “Streamreader”. To do so, we shall include two libraries, System.IO and System.

Imports System

Imports System.IO

Within our subroutine ProcessFile(), we declare a StreamReader.

Dim fileReader As System.IO.StreamReader

fileReader =

My.Computer.FileSystem.OpenTextFileReader("C:\Stellenbosch\Altico_.csv")

Once a file is created within the directory shown above in the code, a “file created” event occurs; it fires if and only if the file name matches a predefined file name mask.

Our subroutine “ProcessFile” is then executed if the filename and the masking, match. The code for this event may be seen below.

Private Sub FileSystemWatcher1_Created(sender As Object, e As FileSystemEventArgs)

Handles FileSystemWatcher1.Created

Call ProcessFile()

End Sub

Let the fun begin!!!

We declare a string variable called “stringReader”.

StringReader will contain the contents of the file. For each row within the file we must.

• Eventually write the contents of the line to an output file.
• Parse the second record of the file as it is the first data record that will contain the proposal number as the first line of the file is a header.
  There will always be a minimum of two lines within any data file (i.e. the header and at least one line of data).
• Locate and capture the proposal number (which is in column 7).

The code to achieve this is shown below and it should be noted that one may stop the read process at the successful completion of parsing the second line of code.

This exercise is left to the reader. The full code listing may be found in Addenda 1

Dim stringReader As String

For Each row In "C:\Stellenbosch\Altico_.csv"

stringReader = fileReader.ReadLine()

If I = 2 Then

‘If you are on the second line of the file, begin a “Do while” loop to

‘parse the line to find the position of the sixth comma. The string

‘that we are looking for begins immediately after the 6th comma and continues until one character BEFORE the 7th comma (see below)

Do While kount < 6

Length = Len(stringReader)

Z = stringReader.IndexOf(",")

before = before + Z

stringReader = stringReader.Substring(Z + 1, Length - (before + 1))

substr = stringReader.Substring(0, stringReader.IndexOf(","))

‘It is important to note that within the iteration of the second record, each column of ‘the source data file,has its own turn to become the value of “Substring”.

These values ‘are all false hits ‘UNTIL our Kount variable becomes 5. Our Global variable Proposal now ‘takes on this value. We shall look at the “Write to file “process in a few seconds.

If kount = 5 Then

Proposal = substr

' MsgBox(proposal)

End If

kount = kount + 1

Loop

End If

' MsgBox("The first line of the file is " & stringReader)

I = I + 1

Next

Writing our data contents to the output file.

Now that we have the “name” of the proposal, all that remains to do is to write the entire file contents to disc utilizing the value of “proposal” as a part of the TXT file name.

This is achieved utilizing the following code.

Before we work our way through the code however, it is important to remind the reader that the stream reader that we used above had reached the “end of file” marker. This said, we must re allocate it.

Further, we now allocate a “Streamwriter” (which originates from the System.IO library).

' Write to file

‘Declare our new file reader and open a Text file reader.

‘We shall now read a line and write the line to file. The astute reader will note the ‘usage of “Proposal” in the text WRITER on line 6 of the code in gold below.

Dim fileReader1 As System.IO.StreamReader

fileReader1 =

My.Computer.FileSystem.OpenTextFileReader("C:\Stellenbosch\Altico_.csv")

Dim stringReader1 As String

stringReader1 = fileReader1.ReadLine()

‘Declare our file writer. Note that in declaring the output file, we utilize the value

‘of our global variable “Proposal” to define the name of the output file.

Dim file As System.IO.StreamWriter

file = My.Computer.FileSystem.OpenTextFileWriter("c:\Stellenbosch\" & Proposal & ".txt", True)

‘In this section of code we perform the actual read and write process until the

‘end of input file marker is reached.

Do While (Not stringReader1 Is Nothing)

' MsgBox(stringReader1)

file.WriteLine(stringReader1)

stringReader1 = fileReader1.ReadLine()

Loop

‘Close the OUTPUT file

file.Close()

Let us give our project a test drive

We begin by selecting “Debug” and “Start Debugging” from the “Debug” Menu. Our form is brought to the screen.

At this point in time, the application is active and is quiescent.

Our C:\Stellenbosch directory may be seen below:

To prove that our project works, I am going to grab a copy of a file from another directory (see below).

Having copied the file, we now DROP the file into the “C:\Stellenbosch” directory.

This simulates the creation of a production file.

Once again, the “eagle-eyed” reader will note that the desired data file was created as highlighted in the screenshot above.

Conclusions

Whilst this is only one way of achieving our end goal, I prefer it as it separates the SQL Server from operating system interaction.

There are most certainly other ways and means of achieving the same results.

The complete code listing may be found in the Addenda section immediately below:

As always, should you have any questions or concerns, please do feel free to contact me.

Happy programming to all of you!

Addenda 1

Imports System

Imports System.IO

Public Class Form1

Dim Proposal As String

Public Sub New()

' This call is required by the designer.

InitializeComponent()

End Sub

Public Sub ProcessFile()

Dim I, Z, Length, kount, before As Integer

Dim substr As String

I = 1

Dim fileReader As System.IO.StreamReader

fileReader =

My.Computer.FileSystem.OpenTextFileReader("C:\Stellenbosch\Altico_.csv")

Dim stringReader As String

'Do While I <= 3

For Each row In "C:\Stellenbosch\Altico_.csv"

stringReader = fileReader.ReadLine()

'MsgBox("Hello")

If I = 2 Then

Do While kount < 7

Length = Len(stringReader)

Z = stringReader.IndexOf(",")

before = before + Z

stringReader = stringReader.Substring(Z + 1, Length - (before + 1))

substr = stringReader.Substring(0, stringReader.IndexOf(","))

If kount = 5 Then

Proposal = substr

' MsgBox(proposal)

End If

kount = kount + 1

Loop

End If

' MsgBox("The first line of the file is " & stringReader)

I = I + 1

Next

kount = 1

'' Write to file

Dim fileReader1 As System.IO.StreamReader

fileReader1 =

My.Computer.FileSystem.OpenTextFileReader("C:\Stellenbosch\Altico_.csv")

Dim stringReader1 As String

stringReader1 = fileReader1.ReadLine()

Dim file As System.IO.StreamWriter

file = My.Computer.FileSystem.OpenTextFileWriter("c:\Stellenbosch\" & Proposal & ".txt", True)

Do While (Not stringReader1 Is Nothing)

' MsgBox(stringReader1)

file.WriteLine(stringReader1)

stringReader1 = fileReader1.ReadLine()

Loop

file.Close()

End Sub

Private Sub FileSystemWatcher1_Created(sender As Object, e As FileSystemEventArgs) Handles FileSystemWatcher1.Created

Call ProcessFile()

End Sub

Private Sub FileSystemWatcher1_Changed(sender As Object, e As FileSystemEventArgs) Handles FileSystemWatcher1.Changed

End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

End Sub

End Class