Importing Text File

[lothos12345]

Can anyone give me a simple example in Visual Basic.NET of how to import a text file into a Microsoft Access Database, into an existing table. Any help with this would be greatly appreiciated.

[Ontani]

Try This:

       Dim strContents As String
       Dim objReader As StreamReader
       Try
           objReader = New StreamReader("textfile.txt")
           strContents = objReader.ReadToEnd()
           objReader.Close()
           Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb")
           Dim MyCommand As New OleDbCommand("INSERT INTO table (text) VALUES ('" & strContents & "')", MyConnection)
           MyConnection.Open()
           MyCommand.ExecuteNonQuery()
           MyConnection.Close()
           MyCommand.Dispose()
       Catch
       End Try

 

Make sure you have imported this:

Imports System.IO
Imports System.Data.OleDb

[lothos12345]

need a little more help

 

The example you provided to me worked great, however I have multiple rows and columns of in this text file. Each row is a new record and each column is a new field. When I run the program it reads the entire text file into one record in one field of the database. I do I get it to read multiple records into the database from the text file. Again any help given is greatly apprieciated.

[Ontani]

place some text that can be find in the textfile.

 

to read the file line by line use this:

 

Dim file As String = "textfile.txt"
Dim strLine As String
Dim intNr As Integer
intNr = FreeFile()
FileOpen(intNr, file, OpenMode.Input)

Do
strLine = LineInput(intNr)
'add database input here, 
Loop Until EOF(intNr)
FileClose(intNr)

 

Greetz

[sgt_pinky]

Better (read: hell of a lot faster) to use IO.StreamReader class:

 

Dim sFile As String = "textfile.txt" 
Dim sLine As String 
Dim SR as New IO.StreamReader(file)
While SR.Peek <> -1
  sLine = SR.ReadLine
End While
SR.Close

[lothos12345]

Another quick question

 

I can read the line in by record now, thanks, but how do I get the program to seperate the text in to the proper fields, the text is seperated by commas. As always any help given is greatly appreciated.

[PlausiblyDamp]

Using the code from sgt_pinky

Dim sFile As String = "textfile.txt" 
Dim sLine As String 
Dim SR as New IO.StreamReader(file)
While SR.Peek <> -1
  sLine = SR.ReadLine
  dim fields() as string = sLine.Split(",")    
   
End While
SR.Close