csv file

  • 12 years ago

    how to read the contents of a csv file and write it into msaccess database
    do help me

  • 12 years ago
    You can use ADO.NET to read CSV files just like a database, so this would likely be the most efficient way to get the data into Access.  I've never done it myself but I believe the folder is treated as though it was the database and each file is treated like it was a table.  Visit www.connectionstrings.com for details of how to write the connection string for an OleDbConnection object.  You may have to search MSDN for info on writing the SQL code, but I'd guess that you just use the file name in place of a table name.

    The alternative would be to read the data from the CSV file using a StreamReader and building and filling the DataTable yourself.
  • 12 years ago

    hi


    i am using stream reader to read data from a csv file
    as every data is comma seperated i do not know how to seperate them.
    i know i have to use spit fuction
    but i do not know how to use it


    i am able to read only the first line from the csv file how to read other line too
    do help me

  • 12 years ago

    You should create an OleDbDataAdapter with a Select statement to get the table schema by calling FillSchema:

    Code:
    Dim myAdapter As New OleDbDataAdapter("SELECT * FROM MyTable", myConnection)
    Dim myTable As New DataTable("MyTable")


    'Get table schema from Access database.
    myAdapter.FillSchema(myTable)


    Dim myRow As DataRow
    Dim fieldValues As String()
    Dim myReader As New IO.StreamReader("file path here")


    While myReader.Peek() <> -1
       fieldValues = myReader.ReadLine().Split(","c)
       myRow = myTable.NewRow()


       'Place values from fieldValues array into row fields here.
    End While

    You can then use the same OleDbDataAdapter to insert the rows into the database.

  • 12 years ago

    what is the character


    c



    in the split function


    can u explain me this coding of urs


    While myReader.Peek() <> -1
      fieldValues = myReader.ReadLine().Split(","c)
      myRow = myTable.NewRow()


      'Place values from fieldValues array into row fields here.
    End While

  • 12 years ago

    I'll let you look up StreamReader.Peek yourself in the help to see what it does.  You can also look up StreamReader.ReadLine and String.Split to see what they do.


    When you put a single character between double quotes and follow it with a "c" character, it forces the literal value to type Char instead of type String.


    Once you have a new DataRow object and an array of strings read from the file, you just need to convert the values to the correct type if necessary, assign them to the appropriate field of the row, then add the row to the table.

  • 12 years ago
    i have tried to my extent
    my coding is:



    Try
                   cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\db\report.mdb;")
                   cn.Open()
                   Dim ob As New System.IO.StreamReader("..\db\BiopsyExport.csv")
                   Dim ar(16) As String
                   Dim i, y As Integer
                   Dim c, r, ind As Integer
                   c = 0
                   r = 0
                   Dim str, str1 As String
                   str = ob.ReadToEnd
                   Do While ob.Peek() >= -1
                       For i = 1 To 15

                           ind = str.IndexOf(",", r)
                           ar(i) = str.Substring(c, ind - c)
                           c = ind + 1
                           r = ind + 1

                           ' MsgBox(ar(i).ToString)
                           'If (ar(i) = ".") Then
                           
                           'End If
                       Next i
                   Loop
                   Dim command As String
                   command = "insert into report1(SNo,sysdate,RefNo,Name,FHName,Address,Age,Sex,SpecimenDate,ReportDate,Histopathology,Impression,OtherNotes,ExportedOn)" & " values(" & ar(1) & ",'" & ar(2) & "'," & ar(3) & ",'" & ar(4) & "','" & ar(5) & "','" & ar(6) & "'," & ar(7) & ",'" & ar(8) & "','" & ar(9) & "','" & ar(10) & "','" & ar(11) & "','" & ar(12) & "','" & ar(13) & "','" & ar(14) & "')"
                   'command = "insert into report1(SNo,sysdate,RefNo,Name,FHName,Address,Age,Sex,SpecimenDate,ReportDate,Histopathology,Impression,OtherNotes,ExportedOn)" & " values('" & ar(i) & "')"
                   cmd = New OleDbCommand(command, cn)
                   cmd.ExecuteNonQuery()
                   MsgBox("inserted successfully")
               Catch ex As Exception
                   MessageBox.Show(ex.Message & " - " & ex.Source)
               End Try





    but it gives me an error as
    Length cannot be less than zero
    parameter name:length-mscorlib
    do help me
    i want to insert all the datas inside the csv file into another table by name report1

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“I have always wished for my computer to be as easy to use as my telephone; my wish has come true because I can no longer figure out how to use my telephone” - Bjarne Stroustrup