如何使用OdbcConnection读取CSV文件

Question

我有一个以.NET核心为基础映像的docker容器。我在app下安装了一些CSV文件，我正试图通过建立OdbcConnection来读取它们。

我安装了以下

unixODBC msodbcsql17 mssql-tools

这就是我目前拥有的

fileSource = "/app/DataStore/FolderForCsvs";
string conSt = string.Format("Driver={{Microsoft Access Text Driver (*.txt, *.csv)}};Dbq={0};Extensions=asc,csv,tab,txt", fileSource);
var connectionString = new OdbcConnection(conSt);
string query = string.Format("SELECT * FROM `{0}`", tableName);
using (var odbcConnection = connectionString)
{
odbcConnection.Open(); // errror
using (var odbcCommand = new OdbcCommand(query, odbcConnection))
{
var dataReader = odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
Console.WriteLine("Inside");
}
}
}

我得到错误：

---> System.Exception: Exception in row row insert
---> System.Data.Odbc.OdbcException (0x80131937): ERROR [01000] [unixODBC][Driver Manager]Can't open lib 'Microsoft Access Text Driver (*.txt, *.csv)' : file not found
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

以下是我的odbc设置：

[root@d5dc11d8c347 app]# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[root@d5dc11d8c347 app]# cat /etc/odbcinst.ini
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
UsageCount=1

我真的不确定odbc设置应该如何。有人能帮我吗？

Answer 1

要添加到@CaiusJard:Docker容器中的ODBC for CSV，需要绕过日志。

只需使用nuget的FileHelpers(我很懒)就可以将csv读取到对象列表甚至数据表中。我还添加了一个链接，如果csv布局文件未知，可以使用Filehelpers。数据表示例也在第二个链接中。

Filehelpers读取分隔文件的示例

读取未知csv格式