更改已加载的csv文件中的定界符

本文介绍了更改已加载的csv文件中的定界符的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我已经在内存中加载了一个csv文件.我的csv文件使用;"作为字段定界符.

I have loaded a csv file in memory. My csv file uses ";" as the field delimiter.

似乎vba的默认分隔符是，"，因为当我尝试访问已加载的csv文件的某些行和列时，vba会优先浏览元素，直到使用的数字为，".

It seems vba default delimiter is "," because when I try to access certain row and column of the loaded csv file, vba advances through the elements with refrence to number of "," used.

示例:

数据的第10行有5列:aa 12,34 bb 5,678(此处的，"是小数点分隔符)

In the 10th row of my data there are five columns: aa 12,34 bb 5,678 (here "," is decimal separator)

在分隔符为;"的csv文件中看起来像这样:

in the csv file which the delimiter is ";" it looks like this:

aa; 12,34; bb; 5,678

aa;12,34;bb;5,678

所以当我写

MyData(10,2)

我期望得到12,34，但是vba返回34; bb; 5，因为它使用，"作为字段定界符.

I am expecting to get 12,34 but vba returns 34;bb;5 because it uses "," as field delimiter.

所以我的问题:

如何告诉vba相对于;"搜索已加载的csv文件?作为分隔符而不是，"?

How can I tell vba to search through the loaded csv file with respect to ";" as delimiter instead of ","?

谢谢.

推荐答案

与其尝试更改excel用于加载csv文件的定界符，不如自行完成

Instead of trying to change the delimiter which excel uses to load a csv file it might be more straightforward to do that on your own

首先，您使用函数将文本文件的行加载到集合中，然后访问该集合中的所需行并转到所需列.

First you use a function to load the lines of a text file into a collection and then you access the wanted line in that collection and go to the wanted column.

此代码

Option Explicit Function txtfileinCol(filename As String) As Collection ' loads the content of a textfile line by line into a collection Dim fileContent As Collection Set fileContent = New Collection Dim fileNo As Long Dim txtLine As String fileNo = FreeFile Open filename For Input As #fileNo Do Until EOF(fileNo) Line Input #fileNo, txtLine fileContent.Add txtLine Loop Close #fileNo Set txtfileinCol = fileContent End Function Sub Testit() Const DELIMITER = ";" Dim filename As String Dim col As Collection Dim vdat As Variant Dim colNo As Long Dim rowNo As Long filename = "C:\Temp\FILE.csv" Set col = txtfileinCol(filename) colNo = 2 rowNo = 10 vdat = col.Item(rowNo) 'here you get the line you want vdat = Split(vdat, DELIMITER) ' now you split the line with the DELIMITER you define Debug.Print vdat(colNo - 1) ' now you print the content of the column you want End Sub

更新:要访问行和列，您还可以使用一个功能.代码看起来像这样

Update: For accessing the row and column you could also use a function. The code would look like that

Option Explicit Function txtfileinCol(filename As String) As Collection ' loads the content of a textfile line by line into a collection Dim fileContent As Collection Set fileContent = New Collection Dim fileNo As Long Dim txtLine As String fileNo = FreeFile Open filename For Input As #fileNo Do Until EOF(fileNo) Line Input #fileNo, txtLine fileContent.Add txtLine Loop Close #fileNo Set txtfileinCol = fileContent End Function Function getColRow(fileLines As Collection, rowNo As Long, colNo As Long, Optional delimiter As String) As String Dim vdat As Variant On Error GoTo EH: If Len(delimiter) = 0 Then delimiter = ";" End If vdat = fileLines.Item(rowNo) 'here you get the line vdat = Split(vdat, delimiter) 'now you split the line with the delimiter getColRow = vdat(colNo - 1) 'now you retrieve the content of the column Exit Function EH: getColRow = "" End Function Sub Testit() Dim filename As String Dim col As Collection filename = "C:\Temp\FILE.csv" Set col = txtfileinCol(filename) Debug.Print getColRow(col, 10, 2, ";") End Sub