excel - 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:\test\timezone.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
推荐阅读
- reactjs - React Bootstrap 表单中的对齐错误
- c++ - LNK2019 未解析的外部符号 GetMonitorBrightness 在函数 main 中引用
- java - 我收到错误 java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
- networking - Corda 网络配置和 NAT
- sql - 由于 Group By 导致每日平均值计算不正确
- python - 使用什么编码来表示 DBF (CDX) 索引
- youtube - Youtube Data API 搜索未返回所有结果
- c# - 如何使用额外的 MVC Core 2 应用程序扩展 MVC Core 2 webapp
- r - 对变量的所有离散值使用 dplyr 过滤器
- python-3.x - python中的动态默认值