Convert column to string reading CSV file using Microsoft.Jet.OleDb.4.0

Question

User1903088995 posted

Hi all,

I am reading a CSV file into DataTable using Microsoft.Jet.OleDb.4.0. The csv file contains zip/postal codes for US and Canada. While reading the PostalCode column, it first encounters US zip codes and thus sets the default data type of the column in DataTable to System.Int32 accordingly. Later on when reading Canadian postal codes it sets them to Null as Canadian postal codes are varchar type but the DataTable column expects Int.

How for the love of God I let the Jet.OleDB engine know not to auto detect the column type and manually set the type to varchar/nvarchar. Please help, this looks like a simple task but I have been stuck for quite some time now. I have tried cast and convert functions but to no avail as from my search I have determined they are not supported.

Please help!

Here is my code,

public System.Data.DataTable GetDataTable(string FilePath)
    {
        # region Local Member Variables
        string folderPath = System.IO.Path.GetDirectoryName(FilePath);
        string fileName = System.IO.Path.GetFileName(FilePath);
        System.IO.FileInfo Info = new System.IO.FileInfo(FilePath);
        System.Data.DataTable ds = new System.Data.DataTable();
        #endregion

        // 
        if (Info.Extension.ToLower() == ".csv")
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection
            ("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + folderPath + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
            
            // Opens a database connection
            conn.Open();

            //SELECT *, cast(PostalCode as nvarchar) as PostalCode FROM [" + fileName + "]
            string strQuery = @"select CountryName, str(PostalCode) as PostalCode,
                                       PostalType, CityName, CityType, CountyName, CountyFIPS, ProvinceName, ProvinceAbbr, 
                                       StateFIPS, MSACode, AreaCode, TimeZone, UTC, DST, Latitude, Longitude
                                from [" + fileName + "]";

            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);

            // Adds or refreshes rows in the dataset
            adapter.Fill(ds);

            // Closes the connection
            conn.Close();
        }
        return ds;
    }

Answer 1

User509596457 posted

You can use a schema.ini file to specify columns and their data types. If you don't have them declared, the driver will try to auto-detect data types. The schema.ini file needs to be in the same folder as the import file.

MSDN reference: http://msdn.microsoft.com/en-us/library/ms709353.aspx

Answer 2

User1903088995 posted

Thanks! I did not create the ini file in the same directory. Thanks once again :)