ADODB.Connection：分隔符分号不适用于csv文本文件 [英] ADODB.Connection: delimiter semicolon does not work for csv text files

问题描述

我使用ADODB.Connection和ADODB.Recordset从csv文件获取数据。我面临的问题是，在分号（或逗号以外）的情况下，定界符似乎不起作用。我正在使用分号作为分隔符。这是我的代码：

I use ADODB.Connection and ADODB.Recordset to get data from csv files. The problem I am facing is that the delimiter seems not to work in case of semicolon (or other than comma). I am working with a semicolon as a delimiter. This is my code:

Public Function getDataFromFile(path As String, filename As String) As ADODB.Recordset

    Dim cN As ADODB.Connection
    Dim RS As ADODB.Recordset

    Set cN = New ADODB.Connection
    Set RS = New ADODB.Recordset

    cN.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";" & _
             "Extended Properties=""text;HDR=NO;FMT=Delimited(;);IMEX=1;""")

    RS.ActiveConnection = cN
    RS.Source = "select * from " & filename
    Set getDataFromUrl = RS

End Function

当我将所有分号替换为csv文件中的逗号，一切正常（即使使用 FMT = Delimited（;），也可以使用相同的代码）。但是，不能使用分号作为分隔符。

When I replace all semicolons to commas in the csv file everything works fine (same code, even with "FMT=Delimited(;)"). But it doesn't work with semicolon as delimiter.

推荐答案

据我所知，您必须使用文件 Schema.ini 必须与该文件位于同一文件夹中。而且，更糟的是，它需要包含文件名。我制作了两个示例文件，一个用分号分隔，一个使用Tab：

As far as I know, you have to use a file "Schema.ini" that needs to exists in the same folder as the file. And, to make it worse, it needs to have the filename in it. I made two example files, one semicolon-separated, one with Tab:

[textfileWithSemicolon.csv]
ColNameHeader=True
Format=Delimited(;)

[textfileWithTAB.csv]
ColNameHeader=True
Format=TabDelimited

当您的例程获取路径和文件作为参数时，最简单的方法可能是将文件复制到具有固定名称的固定文件夹，然后将 Schema.ini 文件。

As your routine gets the path and file as parameter, maybe the easiest way for you is to copy the file to a fixed folder with a fixed name and put the Schema.ini file there.

另一种方法是动态创建 Schema.ini ，但这需要您具有写权限

An alternative would be to create the Schema.ini on the fly, but that require that you have write permission on the passed folder (and that noone else is doing the same on that folder in the same moment...)

有关已通过的文件夹的信息（并且没有其他人同时在该文件夹上执行相同的操作……）有关的更多信息Schema.ini ，请查看 Microsoft文档

For more information about the Schema.ini, look at Microsoft Docs

（似乎还有一种方法可以更改注册表，但我认为这不是一个好的解决方案）

(There seems to be also a way to change the default delimiter in the registry, but I don't think that this a good solution)

这篇关于ADODB.Connection：分隔符分号不适用于csv文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！