CODEWITHSUNDEEP
c#sql-server-2008file-iooledboledbconnection
Samad
Samad

Reputation: 71

Microsoft Jet OLEDB 4.0 connection string for .txt file Format

Here is my connection string for .txt file and some piece of code

public class FileTransfers
{ 

    public void fileFromDrive(string filename)
    {
        FileInfo file = new FileInfo(filename);
        string fileConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
               file.DirectoryName + 
               "; Extended Properties='text;HDR=YES;FMT=Delimited(,)';";

        using (OleDbConnection con = new OleDbConnection(fileConnectionString))
        {
            using (OleDbCommand cmd = new OleDbCommand(
                string.Format("SELECT * FROM [{0}]", file.Name), con))
            {
                con.Open();



                using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                {
                    DataTable tbl = new DataTable("Attendance");
                    adp.Fill(tbl);
                }
            }
        }
    }
}

But the problem is when I debug records in tbl it shows me the data in only one column, but there are 7 multiple columns in my .txt file and hundreds of rows.

I have tried FMT=Delimited(,), FMT=TabDelimited,FMT=FiXed but didn't got multiple columns. I know every entry needs a (,) at is end, but I cant do that manually.

Upvotes: 2

Views: 5833

Answers (1)

Mehrad
Mehrad

Reputation: 4203

There are some details you need to consider doing this process as Jan Schreuder mentions in his article Using OleDb to Import Text Files,

The Jet engine makes assumptions about the content of the file. This can result in incorrect imports. For example, it might think a column contains date values. But in fact, your file should treat the columns as a string. In these cases, you should create a Schema.Ini file that describes the type of value for each column. The class creates a Schema.Ini file before it opens the delimited file, but only to specify what the delimiter is. You may want to change this to use pre-defined INI files that describe your input file.

So go ahead and create the schema.ini file as prescribed and you issue will be all gone. It's contents should be looking like this,

[FileName.csv]
ColNameHeader=True
Format=CSVDelimited

For more details on how tos refer to the following MSDN guide,

Schema.ini File (Text File Driver)

Upvotes: 1

Related Questions