Delimited Text File

This question is related to reading in text files delimited by commas into a VBExpress 2005 program and then the values written to a database.

http://msdn2.microsoft.com/en-us/library/cakac7e6.aspx

This is the way i have been doing this. With the file layouts that i have some alteration has been required. The way i am currently doing it is this:

y = 1
Using MyReader As New _
Microsoft.VisualBasic.FileIO.TextFieldParser("C:\testfile.txt")
  MyReader.TextFieldType = FileIO.FieldType.Delimited
  MyReader.SetDelimiters(",")
  Dim currentRow As String()
  While Not MyReader.EndOfData
     currentRow = MyReader.ReadFields()
     Dim currentField As String
     For Each currentField In currentRow
      If y = 1 then
		y = y + 1
		variable = currentField
	  ElseIf y = 2 then
		y = y + 1
		variable = currentField
	  Elseif y = 3 then
		y = 1
		Me.TableAdapter.Update(variable, variable, ....)
	  EndIf
     Next
  End While
End Using

This is the jist of what i have been using. Each row contains different fields from the previous one. Some of the rows or feilds within get written to a completely different table than the rest.

This example is one that would happen if the entire file was the same (2 fields per row). The fiel i am currently working on is not the same but the overall concept of processing remains. As of right now in my current program it looks something like this:

ElseIf y = 367 then
  y = y + 1
  variable = currentField
ElseIf y = 368 then
.
.
.
.

There MUST be an easier way to do this. My current file has aproximatly 800-900 different fields and there is no way i am willing to write 800-900 ElseIf statments to read it all in. Does anyone know an easier way to do this or is VB2005 simply not intended to access text files as previous versions were

Thank you for any help that you can provide

Answer this question

Delimited Text File

Cesar Augusto BR
With the format your talking about think of the

currentRow = MyReader.ReadFields()

as a simple way to read a delimited line into an array which you can then process.

From what you saying about them being different lengths - you still need to identify how the record/line will be processed. But this using the readfiles method allows you to extract the fields easily.

It sounds a though your writing a conversion process - which once done will have the data in the database tables and use these instead of the single file.

I'm sure the file is not quite as bad as first appears. Good luck and let us know how this works out for you.

Does this infomation sort of answer you original question about reading the delimited file Obviously if it doesnt let us know what more info we can provide.

Obviously if this now opens up a different issue - then feel free to start up a new thread on the new issue.
JimSim
That is the kind of help i was looking for. I went from VB6 training years back into self-taught VB2005 for work.

I just looked in my program and it shows CurrentRow as a string. I will probably just need to redimention it and will try that with the index.

My only problem is that almost all of the rows in the .cad file are a different length and layout. Basically what i am doing is taking the files of a program that was written 10+ years ago and putting it into a database format. This specific text file has information in it that needs to go to 7 different tables at different points. Since databases were not an option when the program was first created, the original programmer just stuck as much information in one file as possible no matter what it was.

I will keep trying. Thank you so much for your advice. I will see how much i can implement it here.
Game.Coder.UK
I have come to a solution. It is so easy now that i feel really dumb for posting this topic to start with, but the suggestions/replys have lead to this find. Here it is:

Imports System
Imports System.Collections
Imports System.IO

Dim SR As StreamReader
Dim ENTRY As String

SR = New StreamReader("C:\FileToRead")

ENTRY = SR.ReadLine
Dim ARR() As String = Split(ENTRY, ",")
variable1 = ARR(1)
variable2 = ARR(5)
*write info to database*

This has let me read in an entire line at one time, and based on the file layout access a specific item based on its position in the string/array. This has also allowed me to do a loop of the same code ont he few lines that repeat themselves with similar information/same layout. My 1000 lines of code is already more than 3/4 changed into this and is currently only aprox. 50 lines long. I had completely forgotten about streamreaders in the years that i have not used VB.

Thanks again to all that have posted suggestions - they led up to this discovery and take my word for it, it is VERY appreciated.
giddy
OK,

CurrentRow if I'm correct is a collection where you can uniquely refer to indexed members so you should be able to refer to the members by an index number

variable = CurrentRow(0)

So At the moment you looping through all the fields in the current row - you may be able to refer just to the important ones through indexing and forget about the looping.

Also most of the fields appear to be doing the same thing

y=y+1
variable = CurrentField

If so then why does each require a separate IF statement, cant you stick this in the else clause and simply have the ones you want to process in the IF or ELSEIF Clauses.

How many are exceptions and need to go to be processed differently. Code the exceptions and for everything else use the else class.

In fact lets suggest using a select case statement rather than an if...else, possibly something like the following.

Select case y
case 3
variable = currentField
Me.TableAdapter.Update(variable, variable, ....)
case else
variable = currentField
End Select
y = y + 1

You appear to be doing this the hardway and not looking for the commonality in code to be refactored out. It sounds as though what you asking to do can be done much simpler using the suggestions above.
Delimited Text File

Answer this question