How to update or delete a row in CSV file

• • Reply

  14 years ago

  by parthsar5

  Parthasarathy

  , Bhutan

  Joined 14 years ago

  I am tring to delete or update a CSV file using a ADODB recoredset in VB6.0. Its giving a error multipal data can't be updated. Ony can help me to give the solution how to update CSV file using Recordset.

  Acctualy i am having some (around 50) records in my CSV file. I am able to add new records in the same file but not able to update nor delete the record from the CSV files. When i run this code it gives error mesg

   

  objconnection active connection

  strPathtoTextFile = App.Path & "\Data\"

  If objconnection.State <> 0 Then Set objconnection = Nothing

  
  objconnection.ConnectionString = _
          "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
          "DefaultDir=" & strPathtoTextFile
  
  
  
  
  
  
  
  
  

  objconnection.Open

   

  Private Function deleteob()
  Dim showrs As New ADODB.Recordset
  Dim sqlstr As String
  
  
  
  
  
  

  sqlstr = "delete from Observation.csv where ob_code=" & txtOb_code

  If showrs.State <> 0 Then Set showrs = Nothing
  showrs.Open sqlstr, objconnection, adOpenDynamic, adLockOptimistic
  End Function
  
  
  
  
  
  
  
  
  
  

  It gives error mesg "Delete Data in a linked table is not supported by this ISAM".

  Report abuse
• • Reply

  14 years ago

  by sync_or_swim

  Rob Bickel

  S. Wales, United Kingdom

  Joined 15 years ago

  Hi,
  
  Welcome to Developer Fusion.  The following code demonstrates how to read data from a CSV, populate a recordset and then write it out to another CSV.  It assumes that your CSV contains column headers:
  
  Private Sub Command1_Click()
  'General Declarations
  Dim oRS As ADODB.Recordset
  Dim aRecords() As String
  Dim aFieldNames() As String
  Dim oneline As String
  Dim x As Long
  Dim iFields As Long
  Dim iFileNum As Long
  Set oRS = New ADODB.Recordset
  iFileNum = FreeFile
  'Open the CSV and read the column headers into recordset
  Open Whateveryourfileiscalled For Input As #iFileNum
  'Get first line, this should contain the column names
  Line Input #iFileNum, oneline
  'split it into columns
  aFieldNames = Split(oneline, ",")
  'set the number of fields = upper bound of aFieldNames array
  iFields = UBound(aFieldNames)
  For x = 0 To iFields
         oRS.Fields.Append aFieldNames(x), adVarChar, 100
  Next
  oRS.Open
  x = 0
  'Read main information into memory
  Do While Not EOF(iFileNum)
        Line Input #iFileNum, oneline
        aRecords = Split(oneline, ",")
        'Populate recordset
        oRS.AddNew
        For x = 0 To UBound(aRecords)
             oRS(x) = aRecords(x)
        Next
  oRS.Update
  Loop
  Close #iFileNum
  
  'Now that you have created and populated your recordset you can perform updates upon it     
       
  'Once changes have been made you can write the new data out to a CSV, it is a good idea to create a new file each time - name it according to the current date so you can build up a series of backups     
  iFileNum = FreeFile
  Open Whateveryournewfileiscalled For Output As #iFileNum
  oneline = ""
  For x = 0 To iFields
       oneline = oneline & aFieldNames(x) & ","
  Next
  oneline = Left$(oneline, Len(oneline) - 1)
  Print #iFileNum, oneline
  Do While Not oRS.EOF
      oneline = ""
      For x = 0 To iFields
           oneline = oneline & oRS(x).Value & ","
      Next
      oneline = Left$(oneline, Len(oneline) - 1)
      Print #iFileNum, oneline
      oRS.MoveNext
  Loop
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  14 years ago

  by parthsar5

  Parthasarathy

  , Bhutan

  Joined 14 years ago

  Thanks for the sugestion. I am new in Developer Fusion. Acctualy i am having some records in my CSV file. I am able to add new records in the same file but not able to update nor delete the record from the CSV files. When i run this code it gives error mesg

   

  objconnection active connection

  strPathtoTextFile = App.Path & "\Data\"

  If objconnection.State <> 0 Then Set objconnection = Nothing

  
  objconnection.ConnectionString = _
          "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
          "DefaultDir=" & strPathtoTextFile
  
  
  

  objconnection.Open

   

  Private Function deleteob()
  Dim showrs As New ADODB.Recordset
  Dim sqlstr As String
  
  

  sqlstr = "delete from Observation.csv where ob_code=" & txtOb_code

  If showrs.State <> 0 Then Set showrs = Nothing
  showrs.Open sqlstr, objconnection, adOpenDynamic, adLockOptimistic
  End Function
  
  
  
  

  It gives error mesg "Delete Data in a linked table is not supported by this ISAM".

  Report abuse