如何在VBA中使用@作为分隔符导入csv文件

问题描述

我正在尝试使用ADODB从带有VBA的Excel中的csv文件中加载数据.
我有一个返回Connection对象的函数.

I am trying to load data from a csv file in Excel with VBA using ADODB.
I have a function to return a Connection object.

Private Function OpenConnection(dataSource As String) As ADODB.Connection
    Set OpenConnection = CreateObject("ADODB.Connection")

    With OpenConnection
        .ConnectionTimeout = 5
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dataSource & ";" & _
            "Extended Properties=""Text;HDR=YES;FMT=Delimited(,)"";Persist Security Info=False"

        Debug.Print "trying to connect: " & .ConnectionString
        .Open
    End With
End Function

然后我只打印数据.

Public Sub Test_Import()
    Dim conn As ADODB.connection, records As ADODB.Recordset

    Set connection = OpenConnection(foldername)
    Set records = connection.Execute("Select * from data.txt")

    Debug.Print records.Fields(0)
End Sub

如果我使用逗号可以正常工作，但是最后我将不得不使用由'@'符号分隔的文件，并且不能使用'，'，因为缺少写权限.
不幸的是，在其他地方复制和更改文件也不是一种选择.

If I use commas it works fine but in the end I will have to use a file that is separated by '@' symbols and which I cannot convert to using ',' because of missing write permissions.
Copying and changing the file somewhere else is unfortunately also not an option.

现在，我在函数 OpenConnection 中将 FMT = Delimited(，)更改为 FMT = Delimited(@)，而不是返回第一个列值 a1 ，则返回整行 a1 @ b1 @ c1 .

Now I changed FMT=Delimited(,) to FMT=Delimited(@) in the function OpenConnection and instead of returning the first column value a1, the full line a1@b1@c1 is returned.

不支持将'@'作为分隔字符串吗?还是我错过了什么?

Is '@' not supported as a delimiting string? Or did I miss something?

推荐答案

感谢@SiddharthRout提供的解决方案以及 Windows开发人员中心的线程.

Thanks to @SiddharthRout for the solution and the link to a thread at windows dev center.

问题在于，在连接字符串中不能设置分隔字符串，而只能指定使用特定字符对文件进行分隔. FMT = Delimited(@)与 FMT = Delimited 或 FMT = Delimited(，)一样.

The problem was that in the connection string one cannot set the delimiting string but only specify that the file is delimited using a specific character. FMT=Delimited(@) is treated just the same as FMT=Delimited or FMT=Delimited(,).

我必须在包含csv文件( data.txt )的文件夹中创建一个文件 schema.ini ，我必须在其中输入该文件:

I had to create a file schema.ini in the folder that contains the csv-file (data.txt) where I had to enter:

[data.txt]
Format = Delimited(@)

将解析schema.ini文件，并正确读取定界符，并且一切正常(只要我不更改任何文件名).

The schema.ini file will be parsed and the delimiter is read correctly and everything works (as long as I don't change any file names).

我还找到了 msdn上的另一个来源，该解释了如何设置分隔字符串(使用注册表或提到的 schema.ini )，还包括Tabstop和固定长度分隔的文件.

I also found another source at msdn that explains how to set the delimiting string (using either the registry or the mentioned schema.ini) and also includes Tabstops and fixed length separated files.

这篇关于如何在VBA中使用@作为分隔符导入csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！