C# .net ODBC connection to text file

Question

Hi there,

I saw a thread and tried the proposed solution:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/1b74978c-6f1b-4e1a-86c2-a458f8a310eb/csvtab-delimited-excel-file-data-importbulk-using-c?forum=csharpgeneral

within 64bit, the C# code like:

string connectionText = @"Driver={Microsoft Access Text Driver (*.txt; *.csv)};Dbq = C:\LTC\ad_hoc\;Extensions=asc,csv,tab,txt;";
string commandText = String.Format("SELECT TOP 100 * FROM [{0}]", FileName);
OdbcConnection connection= new OdbcConnection(connectionText.Trim());
connection.Open();

the error message:

System.Data.Odbc.OdbcException: 'ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'

thanks a lot.

Answer 1

thank you all, it looks like that oledb provider with schema.ini works with tab-delimited file; (2) ODBC however is reading tab-delimited as csv. I will post a new thread asking for suggestions.

Answer 2

It is not

 {Microsoft Access Text Driver (.txt; .csv)}

but :

{Microsoft Text Driver (*.txt; *.csv)}

Answer 3

Hi Dong-6152,
Except what Castorix31 said, you also need to note that your ODBC driver's executable and linking format (ELF) is the same as your application. In other words, you need a 32-bit driver for a 32-bit application or a 64-bit driver for a 64-bit application.
More details you can refer to this document.
Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Have you considered using OleDb to read your text file into a DataTable? If not see if this would work

Schema file

In this case we want a text file named data.txt with three columns comma-delimited. If tabbed delimited change Format to TabDelimited. If no column/headers change ColNameHeader to False.

[Data.txt]  
ColNameHeader=True  
Format=CSVDelimited  
Col1=FirstName Text  
Col2=MiddleInitial Text  
Col3=LastName Text  
CharacterSet=65001  

Sample text file (Encoding is set to UTF-8)

FirstName,MiddleInitial,LastName  
Karen,S,Payne  
Jim,X,Gallagher  
. . .  

Data operations

namespace CodeSample  
{  
    public class DataOperations  
    {  
        public static DataTable ReadTextFile()  
        {  
             
            var connectionString = $@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={AppDomain.CurrentDomain.BaseDirectory}; " +   
                                   "Extended Properties=\"text;HDR=YES;FMT=TabDelimited;\"";  
            var selectStatement = "select top 10 * from " + "Data.txt";  
              
            using (var cn = new OleDbConnection() {ConnectionString = connectionString })  
            {  
                try  
                {  
                    cn.Open();  
  
                    var da = new OleDbDataAdapter(selectStatement, cn);  
                    var dt = new DataTable { TableName = "Data" };  
                    da.Fill(dt);  
                    return dt;  
                }  
                catch (Exception ex)  
                {  
                    var dt = new DataTable();  
                    dt.Columns.Add(new DataColumn() { ColumnName = "Error"});  
                    dt.Rows.Add(ex.Message);  
                    return dt;  
                }  
            }  
        }  
    }  
}  

Form code

private void ReadTextFileButton_Click(object sender, EventArgs e)  
{  
    dataGridView1.DataSource = DataOperations.ReadTextFile();  
}  

Answer 5

Hi

using System.Data.Odbc;
var conn = new OdbcConnection();
conn.ConnectionString = 
 "Driver={Microsoft Text Driver (*.txt; *.csv)};" + 
 "Dbq=C:\MyPath\;" + 
 "Extensions=asc,csv,tab,txt;"; 
conn.Open();
// Use: sql = "Select * From MyTextFile.csv"
// Use: sql = "Select * From MyTextFile.txt"

Best Regards.
--please don't forget to Accept as answer if the reply is helpful--