How to read CSV files using OdbcConnection?

Question

I have a docker container with .NET core as the base image. I have few CSV files mounted under app and I am trying to read them by establishing an OdbcConnection.

I have the following installed

unixODBC msodbcsql17 mssql-tools

This is what I have currently

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");
      }
   }
}

I am getting the error:

 ---> 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)

Below are my odbc settings:

[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

I am not really sure how the odbc settings should be. Can someone help me with this?

Why, when you can just read them as files without needing a database driver, using any one of the tens of popular csv reading libraries or even file.readlines and string split if you're desperate/they're simple — Caius Jard, Oct 12 '21 at 03:19
@CaiusJard Thank you. Yeah, that's the first path I took, and unfortunately, I was having issues using CsvHelper when loading the data into DataTable for nullable GUIDs. I thought I would try this way so that I get everything as strings that could be bulk inserted. — oyeesh, Oct 12 '21 at 12:35
Did you ask a question about the nullable guid issue? What about just reading those guids as strings? Or writing your own value converter (very easy to do ) — Caius Jard, Oct 12 '21 at 15:07
Yeah, I did (https://github.com/JoshClose/CsvHelper/discussions/1874). Do you mind elaborating on the value converter? I want to try that out. — oyeesh, Oct 12 '21 at 19:39

score 0 · Answer 1 · answered Oct 12 '21 at 08:52

To add to @CaiusJard: ODBC for CSV in Docker containers is taking the log way around.

Simply use(I am lazy) FileHelpers from nuget to read you csv into an object list or even a datatable. I have also added a link to use Filehelpers if the csv layout file is unknown. Datatable example is in second link also.

Filehelpers Example reading delimited file

Read unknown csv format

How to read CSV files using OdbcConnection?

1 Answers1