StevensNet.com

...Because someone's gotta tell the story...

Blogs

To return to the main Blog List, click Full Blog Listing.

Tags

Google
Opinion
SMB
Technical
Visual Basic

Reading a comma delimited text file (VB6, for Exchange IMF)

Wednesday, February 13, 2008 in Technical Articles (Views: 3624)
It didn't take long after writing the blog for Exchange 2007 and the IMF to be asked how to use something like Visual Basic to read a comma delimited file. So, here are some basics, using Visual Basic 6 - I will probably follow this up with a basic File I/O article for .NET.

The following topics will be discussed in this article:

1. Opening and closing files

2. Defining variables

3. Defining (and redefining) arrays

4. Using the "Split" function to determine a value

5. Using the command line to define options

6. Getting the current date and backing it up a day so you can read last night's logs (my job runs at midnight for the previous day's log)

To read a text file, you need to do the following:

1. Open the file

2. Read the data in the file (either into a variable, or an array)

Note: We will be using an array in this example, since in my application the results were being sorted by SCL.

3. Close the file

4. Process the data in the array

I am not going into details about arrays, but this is sample code, commented and that is as far as I'll document it. This is what Google is meant for.

Note: All BOLDED items are comments.



Program to read a Commd Delimited Text File

sub Main

strLogEntries is an array we will be reading from.

dim strLogEntries() as string

Store how many records will be in the array, not to mention the temporary marker for processing them

dim intRecords,intTemp as integer

We have no records, set intRecords to 0

intRecords=0

Time to open the file, if you need to write to a file, create a file

named c:\test.txt in Notepad and put in 3 values, seperated by commas like this:

Hello,World,1

Greetings,America,2

Howdy,Earth,3

and save it.

Open the text file


Open "c:\test.txt" for input as #1

Process the file until you see the end of it

While Not EOF(1)

Increase intRecords, since we will be adding a record at this point

intRecords=intRecords+1

Now, redefine the array, since you need to add the additional space

ReDim Preserve (intRecords)

...and read the file now, putting the variable into strLogEntries, but in position intRecords, whatever that number is at this point.

strLogEntries(intRecords)

This ends the While statement above

Wend

Close the file handle, since we're done reading the file.

Close #1

Now, to process the input, go through each record individually...

For intTemp=1 to intRecords

Use the Split Command to show the values. We will print them to the Immediate Window

The split command works like this:

split(variablename, delimiter)(index)

The index starts at 0, so for the 2nd item, you would use (1)

This example gets intTemp (whatever number its on) and grabs its record from the array. Then it splits via the comma, and we are looking for the first item, which will be "Hello" (in our example)


Debug.Print "Item 1: "+split(strLogEntries(intTemp),",")(0)

Then repeat with the second and third items...

Debug.Print "Item 2: "+split(strLogEntries(intTemp),",")(1)

Debug.Print "Item 3: "+split(strLogEntries(intTemp),",")(2)

You can also use a For statement here and say something like

for variable=0 to 2 and then condense the above 3 lines into one. Also, you can append Item with the variable, such as "Item"+str(variable)

Process the next record, back to the For statement, unless its done


Next

End of the routine

End Sub

That is the basics on how to read a comma delimited text file.

A hint for reading the Exhange IMF, the most valuable columns will be:

(remember to add 1, so 6 is really the 7th column)

6: The "From Sender"

8: The action taken

15: Spam SCL Rating

 

Related Blogs You May Be Interested In:


To leave a comment, please log in and/or register.