Loading text

[hog]

What is the best way to get a text file into Access via VB.NET?

 

Do I use a streamreader to read the file into a dataset then into Access or is there a method to whack it straight in Access?

 

Ta

[hog]

Well I got it to work.......but this code is UGLY MAN just plain UGLY!!

 

Surely there is a more elegant way??

 

Dim fFile1 As FileStream = File.Open(Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\p1.txt", _
                          FileMode.Open, FileAccess.Read)

Dim bfBuffer As UTF8Encoding = New UTF8Encoding(True)

Dim arrBytes(9) As Byte

strSQL = "SELECT * FROM tblPersonelID1 WHERE tblPersonelID1.pi = 'NOTHING'"

odaPILists = New System.Data.OleDb.OleDbDataAdapter(strSQL, objConn)

odaPILists.Fill(dtPILists)

ocbPILists = New System.Data.OleDb.OleDbCommandBuilder(odaPILists)

Try


  Do While fFile1.Read(arrBytes, 0, arrBytes.Length) > 0

     drPILists = dtPILists.NewRow()

     drPILists.BeginEdit()

     drPILists.Item("PI") = bfBuffer.GetString(arrBytes)

     drPILists.EndEdit()

     dtPILists.Rows.Add(drPILists)

     odaPILists.InsertCommand = ocbPILists.GetInsertCommand

     odaPILists.Update(dtPILists)

     dtPILists.AcceptChanges()

  Loop

Catch objException As Exception

     ShowError("Location:   Class ImportData" & ControlChars.CrLf & ControlChars.CrLf & "Procedure:  " & _
               "cmdGenerate(ByVal strSuppliername As String)" & ControlChars.CrLf & ControlChars.CrLf & "Error Text: " & _
                objException.Message)

End Try

[Bucky]

How about reading the entire text file into a string first, and then

sticking it in the DB?

 

Dim arrBytes() As Byte
Dim fFile1 As FileStream = File.Open(Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\p1.txt", _
                          FileMode.Open, FileAccess.Read)
Dim reader As New StreamReader(fFile1)
Dim data As String

reader.ReadBlock(arrBytes, 0, fFile1.Length)
data = System.Text.Encoding.UTF8.GetString(arrBytes)

 

Is that slightly more elegant?

[hog]

just love ya avitar:):):)

 

Much more elegant, but doesn't the need to cycle trough the array to load it into the dataset make it sloppy again:(

[Bucky]

Oh... databases aren't my forté, so I didn't get that's what you

were doing there. In that case, then, your way is probably more

logical, unless you want to split up the data string and do it

that way. *shrug*

 

As for your error handling, try the ToString() method of the

Exception class; it provides a nice summary of the error which is

very similar to the one you concatenate in your Catch handler.

 

Also, my avatar is of Paul Zaloom who played Beakman on

Beakman's World. I used to think that was the greatest show.

[hog]

Ah OK cheers I'll check out the ToString() approach:)

 

As for Paul Zaloom and Beakman's World? I don't think this show has made it to the UK so don't know him.......but he looks bloody funny lol:)

[hog]

So another problem arises....

 

My code above is fine, (allbeit ugly), to read and write a single field from a text file that contains just one field per line.

 

But how would I go about getting the data from a file that has 7 tab seperated fields, all of which could contain varying lenghs of data????

[Bucky]

[mshelp=ms-help://MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfSystemStringClassSplitTopic.htm]String.Split()[/mshelp]

 

Also, the most comprehensive site I could find on Beakman. :)

[hog]

Sorry Bucky, just going through the site I see I never posted back to say your suggestion worked a treat :)