Import data from Text file to Msaccess Database

[vks.gautam1]

Here im import data from Text file to Msaccess Database.
Data structure in Notepad is

22-Jan-09|919876165328|9878216613|

im storing this data in string & then split that & store to a array variable.
if i give cmd parameter
" .Add("@Date", OleDbType.date).Value = data(0)"
then this give error " Cannot convert string to date." so i do this
".Add("@Date", OleDbType.varchar).Value = data(0)"

Is there any better way to do this. Or this is ok

Dim file_name As String = "E:\hello.txt"
            Dim txtline, q, data(2) As String
            Dim cn As OleDbConnection
            Dim cmd As OleDbCommand
            If System.IO.File.Exists(file_name) = True Then
                Dim objreader As New System.IO.StreamReader(file_name)
                Do While objreader.Peek <> -1
' Storing line to string variable
                    txtline = objreader.ReadLine() & vbNewLine
'breaking the line & stroing to array variable
                    data = txtline.Split("|")
                    q = "insert into TAB1 values(@Date,@SNo,@MobNo)"
                    cn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=E:\dat.mdb;")
                    cn.Open()

                    cmd = New OleDbCommand(q, cn)
                    cmd.Connection = cn
                    With cmd.Parameters
                        .Add("@Date", OleDbType.VarChar).Value = data(0)
                        .Add("@SNo", OleDbType.VarChar).Value = data(1)
                        .Add("@MobNo", OleDbType.VarChar).Value = data(2)
                    End With
                    cmd.ExecuteNonQuery()
                Loop
            End If
           
            MessageBox.Show("Data Saved")

 

[jmcilhinney]

I would suggest using a TextFieldParser to read the data, and then populate a DataTable row by row. Once you're done you use a DataAdapter to save all the data in a batch.

 

[cjard]

Every single pass of the loop, you declare a new connection, open it, run a new sql on it (after adding all the parameters to it), and close it..

That it's parameterized is good, but I think you just missed the whole point of parameterization

'pseudocode

DIm con as New Conenction)
Dim cmd as New Command(sql, con)
cmd.Parameters.AddWithValue("paramname", DateTime.Now) 'dummy add

con.Open()
For Each line as String in File.ReadAllLines(file)
  Dim spl() as String = line.Split(splitchar)

  cmd.Parameters("dateParam") = DateTime.ParseExact(spl(0), "dd-mmm-yy", CultureInfo.CurrentCulture)
  cmd.Parameters("paramname") = value..
  cmd.ExecuteNonQuery()
Next

con.Close()

 

[vks.gautam1]

Here i tried TextFieldParser in new application so after that i will implement this on my notepad project.
Try to fill datatable & show that data in datagridview.

 With table.Columns
                .Add("Date", GetType(Date))
 End With

Error - Invalid cast from char to date & so on.......

then

 With table.Columns
                .Add("Date", GetType(String))
 End With

Now it gives error =" Index was outside of bounds"

Whole code

  Dim DS As DataSet
            Dim table As New DataTable("Data")
            With table.Columns
                .Add("Date", GetType(String))
                .Add("SNo", GetType(Long))
                .Add("MNo", GetType(Long))
            End With

            filepath = "E:\hello.txt"
            Dim theTextFieldParser As FileIO.TextFieldParser
            theTextFieldParser = My.Computer.FileSystem.OpenTextFieldParser(filepath)
            theTextFieldParser.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
            theTextFieldParser.Delimiters = New String() {"|"}
            Dim currentRow() As String
            While Not theTextFieldParser.EndOfData
                currentRow = theTextFieldParser.ReadFields()
                Dim currentField As String
                For Each currentField In currentRow
                    With table.Rows
                        .Add(currentField(0))
                        .Add(currentField(1))
                        .Add(currentField(2))
                    End With
                Next
            End While
            DS.Tables.Add(table)
            DataGridView1.DataSource = DS.Tables(0)


        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

Must be mistake in loops of TextFieldParser rows , columns & rows of datatable..

 

[jmcilhinney]

You can't add a Date column to your DataTable and then add Strings to it. When you use a TextFieldParser you're going to be reading nothing but text, so it's up to you to interpret every field as the appropriate data type. If it's supposed to represent a date then you need to convert it to a Date before adding it to the DataTable. The same goes for any other data types. Anything other than String should be being converted to the appropriate type by your code.

 

[vks.gautam1]

I have changed the data to date type as required. Other are fine.

If Date.TryParse(currentField(0), dat) = True Then
                    With table.Rows
                        .Add(dat)
                        .Add(currentField(1))
                        .Add(currentField(2))
                    End With
                End If

Error- index was outside the bound of array. This part is wrong.

Dim currentRow() As String
            While Not theTextFieldParser.EndOfData
                currentRow = theTextFieldParser.ReadFields()
                Dim currentField As String
                For Each currentField In currentRow
                    If Date.TryParse(currentField(1), dat) = True Then
                        With table.Rows
                            .Add(dat)
                            .Add(currentField(2))
                            .Add(currentField(3))
                        End With
                    End If

                Next
            End While

How should i deal with columns index of textfiledparser.

 

[jmcilhinney]

There is no index of a TextFieldParser. currentRow is a String array, not a TextFieldParser. How do you usually loop through an array if you need to refer to each element by index? Also, currentField is a String, so why are you getting anything from it by index? currentField(1) is getting a single character from that string. Have you actually looked at currentRow and currentField to see what they contain? I'd say not. If you look at the data you're using instead of making assumptions about then you can see whether you're using it correctly.

 

[vks.gautam1]

i Have no idea. wt should i do for this.

 

[jmcilhinney]

You don;t need that For each loop at all. currentRow is a String array. Simply refer directly to each element of currentRow by index. Get rid of currentField altogether.

 

[cjard]

i Have no idea. wt should i do for this.

Implement the pseudocode I gave you in my first post and it will work; there's a reason why I took a few minutes out of my life to write it!

 

[vks.gautam1]

i will let u know abt my work on your advise day after tomarrow. Thanks for your advise.

 

[vks.gautam1]

For Each line As String In File.ReadAllLines(file_Path)

It giving error = File is not declared

 

[jmcilhinney]

Have you imported the System.IO namespace? If not then the File class doesn't exist. You must either import the namespace or qualify the class name.

 

[vks.gautam1]

Imports System.IO

i used it. Now it is giving error on

cmd.Parameters("@date").Value = DateTime.ParseExact(spl(0), "dd-mmm-yy", CultureInfo.CurrentCulture)

Culturinfo = Name 'Culturinfo' is not declared.
Now wt should i import ?

 

[InertiaM]

In cases like this, I either refer to the documentation or the Object Browser

If I search for CultureInfo in the Object Browser (F2), it shows the following :-

Public Class CultureInfo
          Inherits System.Object
     Member of: System.Globalization