Howto: load a text file quickly into a DataTable

If you will be developing for a 64-bit Windows 7 box, please see this post as well -> TheMicrosoftJetOLEDB40ProviderIsNotRegisteredOnTheLocalMachineErrorOnWindows7

Someone asked about a quick way to load a text file and since the code is a bit lengthy I have included it before.

Basically one of the quickest ways (rather than opening and reading it line by line by line by line) is to use the OleDb connection to quickly push it into a Dataset. You can do this with any delimiter...

There are two procedures below - the first one UpdateSchemaIni does what it says - it updates the Schema.Ini so that the OleDbConnection knows what to parse on. This is necessary for non-csv files. The second - LoadDataFile - loads up the file and sticks it into a table of your naming and then returns a DataSet for you to play with.

Enjoy!!!

============ code below

private char m_delimiter

/// <summary>
/// Required to write the Schema.Ini in case the delimiter for the files are not simply CSV
/// </summary>
/// <param name="m_delimiter">System.Char representing the delimter to use in this file</param>
/// <param name="filename">Filename to pass, helping to locate the Schema.Ini and give the header for the section</param>
private static void UpdateSchemaIni(char m_delimiter, string filename)
{
    string _schemaFile = String.Format(@"{0}\{1}", Path.GetDirectoryName(filename), "Schema.ini");
    string _filenameHeader = String.Format("[{0}]", Path.GetFileName(filename)).ToLower();
    string _delimiterLine = String.Format("Format=Delimited({0})", m_delimiter);

    StreamReader _reader;
    StreamWriter _writer;

    string _tempFile = String.Format("{0}.{1}", _schemaFile, DateTime.Now.Ticks);
    while (File.Exists(_tempFile))
    {
        _tempFile += "0";
    }

    if (!File.Exists(_schemaFile))
    {
        using (_writer = new StreamWriter(_schemaFile))
        {
            _writer.WriteLine(_filenameHeader);
            _writer.WriteLine(_delimiterLine);
            _writer.Close();
        }
    }
    else
    {
        bool _successfullyUpdated = false;
        using (_writer = new StreamWriter(_tempFile))
        {
            using (_reader = new StreamReader(_schemaFile))
            {
                bool _entryOnNextLine = false;
                while (_reader.Peek() > 0)
                {
                    string _currentLine = _reader.ReadLine();
                    if (_currentLine.Trim().ToLower() == _filenameHeader)
                    {
                        _writer.WriteLine(_currentLine);
                        _entryOnNextLine = true;
                    }
                    else if (_entryOnNextLine)
                    {
                        _writer.WriteLine(_delimiterLine);
                        _entryOnNextLine = false;
                        _successfullyUpdated = true;
                    }
                    else
                    {
                        _writer.WriteLine(_currentLine);
                    }
                }
                _reader.Close();
            }
            if (!_successfullyUpdated)
            {
                _writer.WriteLine(_filenameHeader);
                _writer.WriteLine(_delimiterLine);
                _successfullyUpdated = true;
            }
            _writer.Close();
        }
        if (_successfullyUpdated)
        {
            File.Copy(_tempFile, _schemaFile, true);
            File.Delete(_tempFile);
        }
        else
        {
            throw new ApplicationException("Unable to updated Schema.Ini file");
        }
    }
}

private static DataTable LoadDataFile(string filename, string tableName)
{
    if (File.Exists(filename))
    {
        UpdateSchemaIni(m_delimiter, filename);
        FileInfo _fileInfo = new FileInfo(filename);

        string _connectionString =
             String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Text;HDR=YES;FMT=Delimited({1})';",
            _fileInfo.DirectoryName, m_delimiter);
        OleDbConnection _connection;
        using (_connection = new OleDbConnection(_connectionString))
        {
            DataTable _dataTable = new DataTable(tableName);
            _connection.Open();
            OleDbCommand _selectCommand = new OleDbCommand(String.Format("SELECT * FROM [{0}]", _fileInfo.Name), _connection);
            OleDbDataAdapter _adapter = new OleDbDataAdapter();
            _adapter.SelectCommand = _selectCommand;
            try
            {
                _adapter.Fill(_dataTable);
            }
            catch (Exception exc)
            {
                throw new ApplicationException(String.Format("Error when trying to create a table from file {0}. {1}", filename, exc.Message));
            }
            return _memberTable;
        }
    }
    else
    {
        throw new ApplicationException(String.Format("Specified file does not exist, unable to load file - {0}", filename));
    }
}