csv file

• • Reply

  15 years ago

  by gayathrikumarasamy

  gayathri

  , India

  Joined 15 years ago

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

  Report abuse
• • Reply

  15 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

  Joined 15 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.
  Report abuse
• • Reply

  15 years ago

  by gayathrikumarasamy

  gayathri

  , India

  Joined 15 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

  Report abuse
• • Reply

  15 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

  Joined 15 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.

  Report abuse
• • Reply

  15 years ago

  by gayathrikumarasamy

  gayathri

  , India

  Joined 15 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

  Report abuse
• • Reply

  15 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

  Joined 15 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.

  Report abuse
• • Reply

  15 years ago

  by gayathrikumarasamy

  gayathri

  , India

  Joined 15 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
  Report abuse