Can I write back single CSV record from Excel?

[rolo]

Hello,

Supposse I have this CSV file:

ID,Name,Salary
1,Peter,12300
2,Bob,32000
3,Mary,37800
4,Mick,28900
5,Luke,15900

Then from Excel, I need some VBA code to fill "ID" in cell B1, change "Name" or "Salary" data and then save that record back to the CSV (in this example overwrite 3rd record of csv file).



Is it possible to do it without opening the csv file
and only update the changed record, not the entire data set?

Thanks!

 

[Hui]

You cannot read or write a file without opening it
However you can read it into memory and then work on it

You can use this code

Sub Update_CSV_File()

Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String

'File Path of Text File
FilePath = "C:\Users\PC_1\Desktop\CSVFile.csv"

TextFile = FreeFile

'Read source file
Open FilePath For Input As TextFile
  FileContent = Input(LOF(TextFile), TextFile)
Close TextFile


'Find name
Dim Name As String
Dim Salary As String
Dim s1 As Integer, s2 As Integer

Name = Sheet1.Range("B2")
Salary = Sheet1.Range("B3")

s1 = InStr(InStr(1, FileContent, Name) + 1, FileContent, ",") + 1
s2 = InStr(s1, FileContent, vbCr)

'Replace Salary
FileContent = Replace(FileContent, Mid(FileContent, s1, s2 - s1), Salary)

TextFile = FreeFile

'Write new file
Open FilePath For Output As TextFile
  Print #TextFile, FileContent
Close TextFile

End Sub

or see attached file

You have to change the directory and filename in the code for it to work

 

[Kenneth Hobson]

You can use ADO. I don't have time to write an example for you just now. There should be several examples out there. You will want to look for the Update keyword as well. e.g. ADO CSV Update

Some posts say that Update does not work.

 

[Hui]

This is a more robust solution as it checks both the ID and Name befiore changing the salary

Sub Update_CSV_File()

Dim strFolder As String, strFile As String
Dim MyData As String, strData() As String
Dim FinalArray() As String
Dim StartTime As String, endTime As String
Dim n As Long, j As Long, i As Long

'File Path of Text File - Change as Required
strFolder = "C:\Users\PC_1\Desktop\Chandoo\"
strFile = "CSVFile.csv"

n = 0

Open strFolder & "\" & strFile For Binary As #1
  MyData = Space$(LOF(1))
  Get #1, , MyData
Close #1

'Import file and break into an array
On Error Resume Next
Dim aMyArray As Variant
Dim aMyArrayFinal As Variant
aMyArray = Split(MyData, vbCrLf)
FstDimension = UBound(aMyArray)
SndDimension = UBound(Split(aMyArray(0), ","))
ReDim myArrayFinal(1 To FstDimension + 1, 1 To SndDimension + 1)
For Idx1 = LBound(aMyArray) To UBound(aMyArray)
  For Idx2 = 0 To SndDimension
  myArrayFinal(Idx1 + 1, Idx2 + 1) = Split(aMyArray(Idx1), ",")(Idx2)
  Next
Next

'Get next file number
TextFile = FreeFile

ID = Sheet1.Range("B1")
Name = Sheet1.Range("B2")
Salary = Sheet1.Range("B3")

'Write new file
Open strFolder & "\" & strFile For Output As TextFile
  For Idx1 = 1 To FstDimension + 1
   
  'Check if Record matches Sheet1 and Update if required
  If myArrayFinal(Idx1, 1) = CStr(ID) And Trim(myArrayFinal(Idx1, 2)) = Name Then
  myArrayFinal(Idx1, 3) = Salary
  End If
   
  'Write line to file
  If myArrayFinal(Idx1, 1) <> "" Then
  Print #TextFile, myArrayFinal(Idx1, 1) & "," & myArrayFinal(Idx1, 2) & "," & myArrayFinal(Idx1, 3)
  End If
  Next
Close TextFile

End Sub

see attached file

 

[rolo]

Thanks HUI, you are really an Excel ninja!