How to update or delete a row in CSV file

vb6 Bhutan
  • 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".

  • 14 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



























































  • 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".

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.

“Any fool can write code that a computer can understand. Good programmers write code that humans can understand.” - Martin Fowler