vba 将带有标题的大型 CSV 文件拆分为每个带有标题的第 n 行的多个 CSV 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12642180/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split large CSV file with header into multiple CSV files for every nth row with a header
提问by Travis Smith
I have a large CSV file that I would like to split into multiple CSV files. I've tried numerous VBS scripts, but I cannot seem to get this.
我有一个很大的 CSV 文件，我想将其拆分为多个 CSV 文件。我已经尝试了许多 VBS 脚本，但我似乎无法理解。
This script does some of what I want but does not save them as CSV files:
这个脚本做了一些我想要的，但没有将它们保存为 CSV 文件：
Sub Split()
Dim rLastCell As Range
Dim rCells As Range
Dim strName As String
Dim lLoop As Long, lCopy As Long
Dim wbNew As Workbook
With ThisWorkbook.Sheets(1)
Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
For lLoop = 1 To rLastCell.Row Step 35
lCopy = lCopy + 1
Set wbNew = Workbooks.Add
.Range(.Cells(lLoop, 1), .Cells(lLoop + 35, .Columns.Count)).EntireRow.Copy _
Destination:=wbNew.Sheets(1).Range("A1")
wbNew.Close SaveChanges:=True, Filename:="Inventory_" & lLoop + 34
Next lLoop
End With
End Sub
结束子
采纳答案by nutsch
Added a saveas line to your code to specify the file format, you should be all set
在您的代码中添加了一条 saveas 行以指定文件格式，您应该已全部设置
Sub Split()
Dim rLastCell As range
Dim rCells As range
Dim strName As String
Dim lLoop As Long, lCopy As Long
Dim wbNew As Workbook
With ThisWorkbook.Sheets(1)
Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
For lLoop = 2 To rLastCell.Row Step 35
lCopy = lCopy + 1
Set wbNew = Workbooks.Add
.Cells(1, 1).EntireRow.Copy _
Destination:=wbNew.Sheets(1).range("A1")
.range(.Cells(lLoop, 1), .Cells(lLoop + 35, .Columns.Count)).EntireRow.Copy _
Destination:=wbNew.Sheets(1).range("A2")
wbNew.SaveAs FileName:="Inventory_" & format(lLoop + 34,"0000") & ".csv", FileFormat:=xlCSV, Local:=True
wbNew.Close SaveChanges:=False
Next lLoop
End With
End Sub
回答by Ansgar Wiechers
Off the top of my head:
在我的头顶：
Const ForReading = 1
Const ForWriting = 2
Set fso = CreateObject("Scripting.FileSystemObject")
maxRows = 35
i = 0
n = 0
Set out = Nothing
Set csv = fso.OpenTextFile("C:\PATH\TO\your.csv", ForReading)
header = csv.ReadLine
Do Until csv.AtEndOfStream
If i = 0 Then
If Not out Is Nothing Then out.Close
Set out = fso.OpenTextFile("out_" & Right("00" & n, 2) & ".csv", ForWriting)
out.WriteLine(header)
n = n + 1
End If
out.WriteLine(csv.ReadLine)
i = (i + 1) Mod maxRows
Loop
csv.Close
If Not out Is Nothing Then out.Close
