本文介绍了VBA向CSV文件添加行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我搜索了很多,但大多数结果都是与EXCEL相关的。所以我找不到任何关于我答案的线索。
我正在尝试由MS Access2010执行此操作:
我有几个头在第一行的CSV文件。所有字段均以分隔,且均为字符串(双引号中有多个)。
我不知道每个CSV文件中有多少列。我要在第一行(标题)和第二行之间插入新行。
如何找出CSV文件有多少列?如何在第二行追加新文本行?推荐答案
FileSystemObject是执行以下任务的优秀工具:
Public Function InsertLine()
Const TmpExtension As String = ".tmp"
Const BakExtension As String = ".bak"
Dim FileSystemObject As Scripting.FileSystemObject
Dim SourceStream As Scripting.TextStream
Dim TargetStream As Scripting.TextStream
Dim Fields As Variant
Dim Values As Variant
Dim FieldCount As Long
Dim Item As Long
Dim Field As String
Dim FileName As String
Dim BaseName As String
Dim TempFileName As String
Dim Path As String
Dim Extension As String
Dim DotExtension As String
Dim TextLine As String
Set FileSystemObject = New Scripting.FileSystemObject
' File to modify.
FileName = "c: est imezone.csv"
BaseName = FileSystemObject.GetBaseName(FileName)
Extension = FileSystemObject.GetExtensionName(FileName)
Path = FileSystemObject.GetParentFolderName(FileName)
DotExtension = "." & Extension
TempFileName = BaseName & TmpExtension
Set SourceStream = FileSystemObject.OpenTextFile(FileName, ForReading)
Set TargetStream = FileSystemObject.CreateTextFile(TempFileName, ForWriting)
' Read header line.
TextLine = SourceStream.ReadLine
' Copy header line.
TargetStream.WriteLine TextLine
' Create line two.
Fields = Split(TextLine, ",")
' FYI.
FieldCount = UBound(Fields)
' Array to hold values.
Values = Fields
For Item = LBound(Fields) To UBound(Fields)
Select Case Item
Case 0
Field = "SomeValue"
Case 1
Field = "OtherValue"
Case 2
Field = "YetAValue"
' etc.
Case Else
Field = CStr(Item)
End Select
Values(Item) = Chr(34) & Field & Chr(34)
Next
' Write line two.
TargetStream.WriteLine Join(Values, ",")
' Copy remaining lines.
While Not SourceStream.AtEndOfStream
TargetStream.WriteLine SourceStream.ReadLine
Wend
' Close files.
SourceStream.Close
TargetStream.Close
' Rename files.
FileSystemObject.MoveFile FileName, FileSystemObject.BuildPath(Path, BaseName & BakExtension)
FileSystemObject.MoveFile TempFileName, FileSystemObject.BuildPath(Path, BaseName & DotExtension)
End Function
需要将引用设置为:Microsoft.Scripting.Runtime
这篇关于VBA向CSV文件添加行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!