how to browse and import text files into Access. Plz help...

[omigod]

Dear guys,

now i am coding for a project that require the program must browse and import any text file into a table (named: import) in Access database (named: test.mdb)

I just code for browse button and i don't know how to code for importing as well as updating, inserting text files into that table in Access.
plz...plz...help me...very urgent. Thanks in advance!

this is my coding for browse button:

 Dim alltext, lineoftext As String
        OpenFileDialog1.Filter = "Text files (*.txt)|*.TXT"

        OpenFileDialog1.ShowDialog()
        If OpenFileDialog1.FileName <> " " Then
            Try
                FileOpen(1, OpenFileDialog1.FileName, OpenMode.Input)
                Do Until EOF(1)
                    lineoftext = LineInput(1)
                    alltext = alltext & lineoftext & vbCrLf
                Loop
                lblNote.Text = OpenFileDialog1.FileName
                txtNote.Text = alltext
                txtNote.Select(1, 0)
                txtNote.Enabled = True

            Catch ex As Exception
                MsgBox("Error opening file.")
            Finally
                FileClose(1)
            End Try
        End If

 

[Tom]

What is the delimiter in your text files? How large are the files your importing? You can insert records to database line by line with each iteration of your loop (slow) or all at once (preferred) after reading your file. Take a look at the textfieldparser in the help file for reading the file.

I usually itterate through the file such as your example, and as it reads each line I add a new record to my dataset table(s). After all the data is added to my dataset, I call a stored procedure to insert the dataset records to the database.

 

[omigod]

Dear Tom,
Thanks u very much
The delimiter uses in my text files are: #. The files are about 500 records or more. So, u can show me how the way u code to browse to any file and the call your store procedure to import this file?
Best regards

 

[Tom]

For a small file such as 500 records, speed isnt an issue so any of the ways above will work fine.

I dislike writing out the whole answer, it takes to much time plus doesnt help if you dont understand it. I am willing to help you each step of the way though but try it first and let me know where you get stuck.

Again start with looking up the TextFieldParser in the help file, it will have examples of how to read a delimited file. The only difference is with each iteration of the loop, you will add a row of data to your dataset.

Using MyReader As New FileIO.TextFieldParser("c:\logs\datafile.txt")

    MyReader.TextFieldType = FileIO.FieldType.Delimited
    MyReader.Delimiters = New String() {"#"}
    Dim currentRow As String()

    'Loop through all of the fields in the file. 
    'If any lines are corrupt, report an error and continue parsing. 

    While Not MyReader.EndOfData
        Try
            'Read line and split into array fields
            currentRow = MyReader.ReadFields()

            ' Include code here to handle the row.

            rowNew = MyDataSet.TableName.NewRow
            rowNew("Field1") = currentRow(0)
            rowNew("Field2") = currentRow(1)
            rowNew("Field3") = currentRow(2)
            MyDataSet.TableName.Rows.Add(rowNew)
            
        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
            MsgBox("Line " & ex.Message & " is invalid.  Skipping")

        End Try
    End While
End Using

The above will read the file and put the data in your dataset. At this point you should be able to bind your data to a datagridview for viewing purposes. After that you need to update the dataset to send the data to the database.

 

[omigod]

Thanks u so much, my problem is solved