英文:
Read a csv file into an array using Excel VBA
问题
I suspect both RowsArray and ColumnsArray need to be redimensioned, but how do I know the dimensions before splitting them?
我怀疑RowsArray和ColumnsArray都需要重新调整维度，但在拆分它们之前如何知道维度呢？
英文:
I am unable to figure out how to read in a simple csv file using Excel VBA.
If I want to open and read a csv file, this statement should suffice, as long as the filepath is a valid string?
' Open file and read contents
Open FilePath For Input As #1
FileContent = Input(LOF(1), 1)
Close #1
Then, I want to create a two dimensional array, with rows and columns. I would think this should do the job, but it does not.
' Split file content into rows
RowsArray = Split(FileContent, vbCrLf)
' Split rows into columns
Dim i As Long
For i = LBound(RowsArray) To UBound(RowsArray)
ColumnsArray = Split(RowsArray(i), ",")
Next i
It does not give an error, but the columns array is empty,
The whole function is here:
Public Function ReadCSVFileInToArray(FilePath)
' Define variables
Dim FileContent As String
Dim RowsArray() As String
' Open file and read contents
Open FilePath For Input As #1
FileContent = Input(LOF(1), 1)
Close #1
' Split file content into rows
RowsArray = Split(FileContent, vbCrLf)
' Split rows into columns
Dim i As Long
For i = LBound(RowsArray) To UBound(RowsArray)
ColumnsArray = Split(RowsArray(i), ",")
Next i
ReadCSVFileInToArray = ColumnsArray
End Function
I suspect both RowsArray and ColumnsArray need to be redimensioned, but how do I know the dimensions before splitting them?
It seems like this should be easy, so I'm clearly not understanding something. I can't even find an intelligible explanation on the web.
答案1
得分: 2
以下是您要翻译的内容：
返回CSV文件的值为2D数组
示例（用法）
Sub Test()
Const FILE_PATH As String = "C:\Test\Test.csv"
Const ROW_DELIMITER As String = vbCrLf ' vbLf
Const COL_DELIMITER As String = "," ' ";"
Dim sArr: sArr = TextFileToArray(FILE_PATH, ROW_DELIMITER)
If IsEmpty(sArr) Then Exit Sub
Dim Data(): Data = GetSplitArray(sArr, COL_DELIMITER)
' 打印到即时窗口（Ctrl+G）。
PrintData Data
' 写入工作表。
'With Sheet1.Range("A1")
' .Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
'End With
End Sub
- 您可以在这里找到
PrintData过程。
将行转换为1D数组
' Purpose: 返回文本文件的每一行作为一维零基数组的元素。
Function TextFileToArray( _
ByVal FilePath As String, _
Optional ByVal LineSeparator As String = vbLf) _
As Variant
Dim TextFile As Long: TextFile = FreeFile
Dim sArr() As String
Open FilePath For Input Access Read As TextFile
On Error Resume Next
sArr = Split(Input(LOF(TextFile), TextFile), LineSeparator)
On Error GoTo 0
Close TextFile
Dim n As Long
For n = UBound(sArr) To LBound(sArr) Step -1
If Len(sArr(n)) > 0 Then Exit For
Next n
If n < LBound(sArr) Then Exit Function
If n < UBound(sArr) Then ReDim Preserve sArr(0 To n)
TextFileToArray = sArr
End Function
将1D数组拆分为2D数组
' Purpose: 返回一维数组的每个元素的拆分值
' 在二维基于一的数组的一行中。
Function GetSplitArray( _
ByVal SourceArray As Variant, _
Optional ByVal ColumnDelimiter As String = ",") _
As Variant
Dim rDiff As Long: rDiff = 1 - LBound(SourceArray)
Dim rCount As Long: rCount = UBound(SourceArray) + rDiff
Dim cCount As Long: cCount = 1
Dim Data(): ReDim Data(1 To rCount, 1 To cCount)
Dim rArr() As String, r As Long, c As Long, cc As Long, rString As String
For r = 1 To rCount
rString = SourceArray(r - rDiff)
If Len(rString) > 0 Then
rArr = Split(rString, ColumnDelimiter)
cc = UBound(rArr) + 1
If cc > cCount Then
cCount = cc
ReDim Preserve Data(1 To rCount, 1 To cCount)
End If
For c = 1 To cc
Data(r, c) = rArr(c - 1)
Next c
End If
Next r
GetSplitArray = Data
End Function
请告诉我您需要进一步的翻译或其他帮助。
英文:
Return the Values of a CSV File in a 2D Array
An Example (Usage)
Sub Test()
Const FILE_PATH As String = "C:\Test\Test.csv"
Const ROW_DELIMITER As String = vbCrLf ' vbLf
Const COL_DELIMITER As String = "," ' ";"
Dim sArr: sArr = TextFileToArray(FILE_PATH, ROW_DELIMITER)
If IsEmpty(sArr) Then Exit Sub
Dim Data(): Data = GetSplitArray(sArr, COL_DELIMITER)
' Print to the Immediate window (Ctrl+G).
PrintData Data
' Write to the worksheet.
'With Sheet1.Range("A1")
' .Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
'End With
End Sub
- You can find the
PrintDataprocedure here.
Rows to 1D Array
<!-- language: lang-vb -->
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose: Returns each line of a text file in an element
' of a 1D zero-based array.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function TextFileToArray( _
ByVal FilePath As String, _
Optional ByVal LineSeparator As String = vbLf) _
As Variant
Dim TextFile As Long: TextFile = FreeFile
Dim sArr() As String
Open FilePath For Input Access Read As TextFile
On Error Resume Next
sArr = Split(Input(LOF(TextFile), TextFile), LineSeparator)
On Error GoTo 0
Close TextFile
Dim n As Long
For n = UBound(sArr) To LBound(sArr) Step -1
If Len(sArr(n)) > 0 Then Exit For
Next n
If n < LBound(sArr) Then Exit Function
If n < UBound(sArr) Then ReDim Preserve sArr(0 To n)
TextFileToArray = sArr
End Function
Split 1D Array to 2D Array
<!-- language: lang-vb -->
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose: Returns the split values of each element of a 1D array
' in a row of a 2D one-based array.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetSplitArray( _
ByVal SourceArray As Variant, _
Optional ByVal ColumnDelimiter As String = ",") _
As Variant
Dim rDiff As Long: rDiff = 1 - LBound(SourceArray)
Dim rCount As Long: rCount = UBound(SourceArray) + rDiff
Dim cCount As Long: cCount = 1
Dim Data(): ReDim Data(1 To rCount, 1 To cCount)
Dim rArr() As String, r As Long, c As Long, cc As Long, rString As String
For r = 1 To rCount
rString = SourceArray(r - rDiff)
If Len(rString) > 0 Then
rArr = Split(rString, ColumnDelimiter)
cc = UBound(rArr) + 1
If cc > cCount Then
cCount = cc
ReDim Preserve Data(1 To rCount, 1 To cCount)
End If
For c = 1 To cc
Data(r, c) = rArr(c - 1)
Next c
End If
Next r
GetSplitArray = Data
End Function
答案2
得分: 1
让Excel来完成这项工作
Public Function ReadCSVFileInToArray(FilePath)
Dim wb As Workbook
Application.ScreenUpdating = False
Set wb = Workbooks.Open(FilePath, ReadOnly:=True)
ReadCSVFileInToArray = wb.Sheets(1).UsedRange.Value
wb.Close
Application.ScreenUpdating = True
End Function
将第一行拆分以获取第二维度。
Public Function ReadCSVFileInToArray(FilePath)
' 定义变量
Dim FileContent, RowsArray, ColumnsArray
Dim ar()
' 打开文件并读取内容
Open FilePath For Input As #1
FileContent = Input(LOF(1), 1)
Close #1
' 拆分文件内容为行
RowsArray = Split(FileContent, vbCrLf)
' 拆分标题行为列
ColumnsArray = Split(RowsArray(0), ",")
ReDim ar(1 To UBound(RowsArray) + 1, 1 To UBound(ColumnsArray) + 1)
' 拆分行为列
Dim i As Long, j As Long
For i = LBound(RowsArray) To UBound(RowsArray)
ColumnsArray = Split(RowsArray(i), ",")
For j = 0 To UBound(ColumnsArray)
ar(i + 1, j + 1) = ColumnsArray(j)
Next
Next i
ReadCSVFileInToArray = ar
End Function
英文:
Let excel do the work
Public Function ReadCSVFileInToArray(FilePath)
Dim wb As Workbook
Application.ScreenUpdating = False
Set wb = Workbooks.Open(FilePath, ReadOnly:=True)
ReadCSVFileInToArray = wb.Sheets(1).UsedRange.Value
wb.Close
Application.ScreenUpdating = True
End Function
Split the first line to get the 2nd dimension.
Public Function ReadCSVFileInToArray(FilePath)
' Define variables
Dim FileContent, RowsArray, ColumnsArray
Dim ar()
' Open file and read contents
Open FilePath For Input As #1
FileContent = Input(LOF(1), 1)
Close #1
' Split file content into rows
RowsArray = Split(FileContent, vbCrLf)
' Split header row into columns
ColumnsArray = Split(RowsArray(0), ",")
ReDim ar(1 To UBound(RowsArray) + 1, 1 To UBound(ColumnsArray) + 1)
' Split rows into columns
Dim i As Long, j As Long
For i = LBound(RowsArray) To UBound(RowsArray)
ColumnsArray = Split(RowsArray(i), ",")
For j = 0 To UBound(ColumnsArray)
ar(i + 1, j + 1) = ColumnsArray(j)
Next
Next i
ReadCSVFileInToArray = ar
End Function
</details>
# 答案3
**得分**: 1
I adjusted your code and added explanations (see the comments) in the code as well:
```vba
Option Explicit
Public Function ReadCSVFileInToArray(FilePath)
' Define variables
Dim FileContent As String
Dim RowsArray() As String
Dim ColumnsArray() As String
Dim vDat As Variant
' Open file and read contents
Open FilePath For Input As #1
FileContent = Input(LOF(1), 1)
Close #1
' Split file content into rows
RowsArray = Split(FileContent, vbCrLf)
' Redim the 1st dimension to have space for all rows
Dim rowNo As Long
rowNo = UBound(RowsArray)
ReDim ColumnsArray(0 To rowNo, 0)
' Split rows into columns
Dim i As Long, j As Long
For i = LBound(RowsArray) To UBound(RowsArray)
vDat = Split(RowsArray(i), ";")
' This will skip lines with no data especially last one if it only contains a CRLF
If UBound(vDat) > 0 Then
' Redim the 2nd dimension to have space for all columns
Dim colNo As Long
colNo = UBound(vDat)
' Redim will preserve and fortunately we only have to change the last dimension
' If you use the Preserve keyword, you can resize only the last array dimension
' and you can't change the number of dimensions at all.
ReDim Preserve ColumnsArray(rowNo, colNo)
' you have to copy element by element
For j = 0 To colNo
ColumnsArray(i, j) = vDat(j)
Next j
End If
Next i
ReadCSVFileInToArray = ColumnsArray
End Function
```
You can test it with:
```vba
Sub testIt()
Dim vDat As Variant
vDat = ReadCSVFileInToArray("filepath")
Dim rg As Range
Set rg = Range("A1")
' Resize the range to the size of the array
Set rg = rg.Resize(UBound(vDat, 1), UBound(vDat, 2))
rg = vDat
End Sub
```
The better way to import text files into Excel is Power Query though because you have more control regarding data types, etc.
[1]: https://learn.microsoft.com/en-us/power-query/connectors/text-csv
[2]: https://learn.microsoft.com/en-us/power-query/
<details>
<summary>英文:</summary>
I adjusted your code and added explanations (see the comments) in the code as well
Option Explicit
Public Function ReadCSVFileInToArray(FilePath)
' Define variables
Dim FileContent As String
Dim RowsArray() As String
Dim ColumnsArray() As String
Dim vDat As Variant
' Open file and read contents
Open FilePath For Input As #1
FileContent = Input(LOF(1), 1)
Close #1
' Split file content into rows
RowsArray = Split(FileContent, vbCrLf)
' Redim the 1st dimension to have space for all rows
Dim rowNo As Long
rowNo = UBound(RowsArray)
ReDim ColumnsArray(0 To rowNo, 0)
' Split rows into columns
Dim i As Long, j As Long
For i = LBound(RowsArray) To UBound(RowsArray)
vDat = Split(RowsArray(i), ";")
' This will skip lines with no data especially last one if it only contains a CRLF
If UBound(vDat) > 0 Then
' Redim the 2nd dimension to have space for all columns
Dim colNo As Long
colNo = UBound(vDat)
' Redim will preserve and fortunately we only have to change the last dimension
' If you use the Preserve keyword, you can resize only the last array dimension
' and you can't change the number of dimensions at all.
ReDim Preserve ColumnsArray(rowNo, colNo)
' you have to copy element by element
For j = 0 To colNo
ColumnsArray(i, j) = vDat(j)
Next j
End If
Next i
ReadCSVFileInToArray = ColumnsArray
End Function
You can test it with
Sub testIt()
Dim vDat As Variant
vDat = ReadCSVFileInToArray("filepath")
Dim rg As Range
Set rg = Range("A1")
' Resize the range to the size of the array
Set rg = rg.Resize(UBound(vDat, 1), UBound(vDat, 2))
rg = vDat
End Sub
The better way to [import text files][1] into Excel is [Powerquery][2] though because you have more control regarding data type etc.
[1]: https://learn.microsoft.com/en-us/power-query/connectors/text-csv
[2]: https://learn.microsoft.com/en-us/power-query/
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论