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

  • 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;
        }
    Monday, October 3, 2011 2:07 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 3, 2011 4:12 PM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 3, 2011 4:12 PM
  • User1903088995 posted

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

    Thursday, October 6, 2011 12:07 PM