asked on

Open and write to a csv file using vba

I need to do something that seems very simple but I can't seem to find the answer. I have a small csv file with about 8 columns (filename ibm.csv) .

Using vba code I need to open it, put a Y flag to the right of the last column if the last column equals something I pass in to the function and then save the file. Can anyone tell me how to do this?

Thanks in advance!

als315

csv file is simple text file, you can open it, read, split into columns and write to a new file. If you upload some sample file, we can help with code.

als315

You can try this sub (new file will be saved with "_out" in the name (ibm_out.csv)

Sub read_f(filen As String)
' filen - full path to file (c:\tmp\ibm.csv)
Dim Str As String, FileIN As Integer, FileO As Integer
Dim Arr() As String
FileIN = FreeFile
Open filen For Input As #FileIN
FileO = FreeFile
Open Left(filen, Len(filen) - 4) & "_out.csv" For Output As #FileO

Do While Not EOF(FileIN)
    Line Input #FileIN, Str
    Arr = Split(Str, ";")
    If Arr(7) = "OK" Then Str = Str & ";Y"
    Print #FileO, Str
Loop
Close #FileIN
Close #FileO
End Sub

Open in new window

boukaka

ASKER

I need to read and write back to the same file - not a different one...

ASKER CERTIFIED SOLUTION

nathaniel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

als315

You can't read and write in the same file in the same time. You can rename first file, read, write to the same name and delete first. Is it acceptable?

Sub read_f()
Dim filen As String
Dim Str As String, FileIN As Integer, FileO As Integer
Dim Arr() As String, fil As String
filen = "c:\tmp\ibm.csv"
FileIN = FreeFile
fil = Left(filen, Len(filen) - 4) & "_out.csv"
Name filen As fil
Open fil For Input As #FileIN

FileO = FreeFile
Open filen For Output As #FileO
Do While Not EOF(FileIN)
    Line Input #FileIN, Str
    Arr = Split(Str, ";")
    If Nz(Arr(7), "") = "OK" Then Str = Str & ";Y"
    Print #FileO, Str
Loop
Close #FileIN
Close #FileO
Kill fil
End Sub

Open in new window

boukaka

ASKER

Thank you - this will work for me.