Update CSV file from MS Access

Question

Hi, all -

I need to edit one field/position in a CSV file.  Below is a sample of the CSV file when it's opened in Excel.  I want to update the value in the first row of the 5th column (see the bold/underlined/italicized value below).

Field1	Field2	Field3	Field4	Field5
Data	Data	Data	Data	Data
Data	Data	Data	Data
Data	Data	Data	Data

How do I do this using VBA in MS Access?

Thanks,

Manuel

Answer 1

Okay, here's a quick & dirty example procedure:

'------ start of code ------

Sub CopyAndModifyCSV()

    On Error GoTo Err_Handler

    Dim strInputFile    As String

    Dim strOutputFile   As String

    Dim strBackupFile   As String

    Dim strLine         As String

    Dim intFileIn       As Integer

    Dim intFileOut      As Integer

    Dim astrData()      As String

    strInputFile = "C:\My Path\To\MyFile.csv"

    strOutputFile = CurrentProject.Path & "\TempCSVCopy.csv"

    ' Open the input and output files

    intFileIn = FreeFile()

    Open strInputFile For Input As #intFileIn

    intFileOut = FreeFile()

    Open strOutputFile For Input As #intFileOut

    ' Read and copy header line.

    Line Input #intFileIn, strLine

    Print #intFileOut, strLine

    ' Read and fix the first data line.

    Line Input #intFileIn, strLine

    astrData = Split(strLine, ",")

    If UBound(astrData) <> 4 Then

        Err.Raise vbObjectError + 1, , "Data line does not have the expected number of columns."

    End If

    astrData(4) = "Whatever you want it to be"

    strLine = Join(astrData, ",")

    Print #intFileOut, strLine

    ' Read and copy all the remaining lines.

    Do Until EOF(intFileIn)

        Line Input #intFileIn, strLine

        Print #intFileOut, strLine

    Loop

    ' Rename the original file to ".bak", and rename the output file

    ' to the original file name.

    strBackupFile = strInputFile & ".bak"

    If Len(Dir(strBackupFile)) <> 0 Then

        Kill strBackupFile

    End If

    Name strInputFile As strBackupFile

    Name strOutputFile As strInputFile

Exit_Point:

    Close

    Exit Sub

Err_Handler:

    MsgBox Err.Description, vbExclamation, "Error " & Err.Number

    Resume Exit_Point

End Sub

'------ end of code ------

Note that this won't work if any of the data elements in the row to be changed contains a comma.  That's because the code uses the Split function to break the line up into separate columns, using the comma as a delimiter.  It won't know if one column's data was a quoted string containing a comma.  It also won't work if the second row (the first data row) doesn't have all 5 elements.  But the code could be modified to be more sophisticated, if necessary.

Answer 2

Thanks for the response!

I like the I/O solution you proposed in your last paragraph.  Could you provide what the VBA would be with this solution?

Answer 3

You can't link to a .CSV file and have the linked table be updatable, nor open an updatable recordset on it.  You would have to, effectively, read in the whole file, update it, and then write it back out again.  You might do this by importing the file into a table using "DoCmd.TransferText acImport, ..." , modifying the data in the table via a recordset, and then exporting the modified table data using "DoCmd.TransferText acExport, ...". 

Or potentially if the file is small enough, you could use basic I/O operations to open the file, read its whole contents into a String variable, modify the contents of that variable, and write it out again. Or you could process it from the input file to a different output file, reading the file line by line, and afterward delete or rename the original input file and rename the output file to replace it.