How to edit csv and text files with VBA (Excel macros)

Publication Date：July 5, 2022

summary

I would like to describe how to edit csv/text files with VBA (Excel macros). This article is intended for those who wish to program the following tasks

• I want to add lines to an already existing csv or text file. I want to add text in the middle of a line.
• I want to delete lines from an already existing csv or text file.

1. Programming Method

As it turns out, FileSystemObject does not have the ability to append to the middle of a line or delete a specific line, so you will have to program it on your own.

The following is an example of programming reference.

Since it is created in Function, I hope you can bring it in by copy and paste as it is and call the method from Sub.

'Parameter Examples

'## filePath      C:\Users\user\Desktop\test\test.txt

'## mode          false:delete, true:write

'## targetRow     10

'## targetInput   If in write mode, any character

'## lineBreakType false:vbLf, true:vbCrLf

Function editFile(filePath As String, mode As Boolean, targetRow As Long, targetInput As String, lineBreakType As Boolean)

'File existence check

If Dir(filePath) = "" Then

  MsgBox "File does not exist!"

  Exit Function

End If

Dim tempText As String

Dim tempTextBefore As String

Dim tempTextNew As String

Dim tempTextAfter As String

Dim resultText As String

Dim i As Long

'File read

With CreateObject("ADODB.Stream")

  .Charset = "UTF-8"

  .Open

  .LoadFromFile filePath

  tempText = .ReadText

  .Close

End With

Dim tempTextBeforeRow As Long

tempTextBeforeRow = 0

For i = 1 To targetRow - 1

  If lineBreakType Then

    tempTextBeforeRow = InStr(tempTextBeforeRow + 1, tempText, vbCrLf)

  Else

    tempTextBeforeRow = InStr(tempTextBeforeRow + 1, tempText, vbLf)

  End If

Next i

If lineBreakType Then

  tempTextBeforeRow = tempTextBeforeRow + 1

End If

tempTextBefore = Left(tempText, tempTextBeforeRow)

'write

If mode Then

  tempTextAfter = Mid(tempText, tempTextBeforeRow + 1, Len(tempText))

  If lineBreakType Then

    tempTextNew = targetInput & vbCrLf

  Else

    tempTextNew = targetInput & vbLf

  End If

  resultText = tempTextBefore & tempTextNew & tempTextAfter

'delete

Else

  Dim tempTextAfterRow As Long

  tempTextAfterRow = 0

  For i = 1 To CInt(targetRow)

    If lineBreakType Then

      tempTextAfterRow = InStr(tempTextAfterRow + 1, tempText, vbCrLf)

    Else

      tempTextAfterRow = InStr(tempTextAfterRow + 1, tempText, vbLf)

    End If

  Next i

  If lineBreakType Then

    tempTextAfterRow = tempTextAfterRow + 1

  End If

  tempTextAfter = Mid(tempText, tempTextAfterRow + 1, Len(tempText))

  resultText = tempTextBefore & tempTextAfter

End If

With CreateObject("ADODB.Stream")

  .Charset = "UTF-8"

  If lineBreakType Then

    .LineSeparator = -1

  Else

    .LineSeparator = 10

  End If

  .Open

  .WriteText tempTextBefore & tempTextNew & tempTextAfter, 0

  .SaveToFile filePath, 2

  .Close

End With

End Function

To use it, pass arguments as follows.

• filePath・・・This will be the full path of the file. Example）C:\Users\user\Desktop\test\test.txt
• mode・・・Pass true,false. If True, add. If False, delete the row.
• targetRow・・・Specify the number of rows to be targeted. In the case of addition, it is the number of lines to be added, and in the case of deletion, it is the number of lines to be deleted.
• targetInput・・・In the case of an append, specify the value to be appended. It is not used in the case of deletion, so any value is fine. Empty letters are also acceptable.
• lineBreakType・・・Setting the character for newlines. If True, vbCrLf. vbLf if False. A quick explanation is that for files created in Windows, it is True.

The character encoding is UTF-8, but if you want to change it, please modify the code of Function directly. There are two places where ".Charset = "UTF-8"" is used.

An example implementation of an actual Function call is shown below.

Sub test()

'Example of appending test123 to line 5 of test.txt.（Files created in Windows）

editFile "C:\Users\user\Desktop\test\test.txt", True, 5, "test123", True

'Example of deleting line 5 of test.txt.（Files created in Windows）

editFile "C:\Users\user\Desktop\test\test.txt", False, 5, "", True

End Sub

If you want to append as a csv, you can make the argument of the value to be appended a comma-separated value, and the comma-separated value will be appended. Example）"test1,test2,test3"

2. Code Description

This is the end of the explanation of how to use the system, followed by an explanation of the code.

The details can be found in the code, but a rough flow of the process is as follows.

For postscript

• ①Reading text files in the path passed as argument
• ②Specify the number of characters up to the number of lines passed in the argument from the number of newlines. For example, in the case of line 5, there are four line breaks before line 5, so find the position where the fourth line break is found
• ③Obtain information up to the fourth line.
• ④Obtain information after the 5th line in the same manner as (2).
• ⑤The information in (3), the information to be appended passed as an argument, and the information in (4) are combined together.
• ⑥Create a file based on the information in ⑤ and overwrite the file.

Deletion

• ①Reading text files in the path passed as argument
• ②Specify the number of characters up to the number of lines passed in the argument from the number of newlines. For example, in the case of line 5, there are four line breaks before line 5, so find the position where the fourth line break is found
• ③Obtain information up to the fourth line.
• ④Obtain information from line 6 onward in the same manner as (2).
• ⑤The information in (3) and (4) are combined together. （Create a string with no fifth line）
• ⑥Create a file based on the information in ⑤ and overwrite the file.

In the case of appending and deleting, both are the same, but rather than editing the file, it is as if the necessary information is extracted from the original file, a new file is created, and then overwritten and saved.

3. summary

If you have trouble editing csv/text files with VBA (Excel macros), please refer to this page.

Thank you for taking the time to read this to the end.