OpenFileDialog can't load CSV files but can lo

Question

This question already has answers here:

What is a NullReferenceException, and how do I fix it? (28 answers)

Closed last year.

When loading Excel files in my Windows Form application, I can load .xls and .xlsx formats fine but when I select a .CSV I get the following error:

System.NullReferenceException: 'Object reference not set to an instance of an object.' sConnectionString was null.

The error happens on the line:

if (sConnectionString.Length > 0)

From the full section of code:

public string sConnectionString;
public void FillData()
{
    if (sConnectionString.Length > 0)
    {
        OleDbConnection cn = new OleDbConnection(sConnectionString);
        {
            cn.Open();
            DataTable dt = new DataTable();
            OleDbDataAdapter Adpt = new OleDbDataAdapter("select * from [sheet1$]", cn);
            Adpt.Fill(dt);
            dataGridView1.DataSource = dt;
        }
    }
}

Which is before the Button code:

private void Browse_Click(object sender, EventArgs e)
{
    OpenFileDialog op = new OpenFileDialog();
    op.InitialDirectory = @"C:\";
    op.Title = "Browse Excel Files";
    op.CheckFileExists = true;
    op.CheckPathExists = true;
    op.DefaultExt = "csv";
    op.Filter = "CSV Files (*.csv)|*.csv";
    op.FilterIndex = 2;
    op.RestoreDirectory = true;
    op.ReadOnlyChecked = true;
    op.ShowReadOnly = true;

    if (op.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        if (File.Exists(op.FileName))
        {
            string[] Arr = null;
            Arr = op.FileName.Split('.');
            if (Arr.Length > 0)
            {
                if (Arr[Arr.Length - 1] == "xls")
                    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    op.FileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            }
            else if (Arr[Arr.Length - 1] == "xlsx")
            {
                sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + op.FileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
            }
        }
        FillData();
        fileTextBox.Text = op.FileName;
    }
}

Edit

Added:

else if (Arr[Arr.Length - 1] == "csv")
    {
    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + op.FileName + 
                        ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
    }

Still get the same error.

Answer 1

About the reported error:

System.NullReferenceException: 'Object reference not set to an instance of an object.'
sConnectionString was null.

The exception is generated because the Connection string is declared as:

public string sConnectionString;

Since it's never initialized, because the initialization of the Connection string is performed only for some file types but not all those included in the OpenFileDialog.Filter. When the code tests the length of the string, the string is still null. This can be avoided setting an initial value:

public string sConnectionString = string.Empty;

About the Connection string required to oped a .CSV file with an OleDbConnection:

• All OleDb providers will do:
  • Microsoft.Jet.OLEDB.4.0
  • Microsoft.ACE.OLEDB.12.0
  • Microsoft.ACE.OLEDB.16.0

• if Microsoft.Jet.OLEDB.4.0 is required for some legacy formats (old Access .mdb files), the application must be compiled as 32Bit, so install the corresponding 32Bit version of the other providers:

  Microsoft Database Engine 2010 Redistributable
  Microsoft Database Engine 2016 Redistributable

To read a CSV file, the connection string - for all providers - is composed as:

{Provider};Data Source={Catalog}; Extended Properties="text; HDR=Yes; IMEX=1; FMT=Delimited;

Where:

• {Provider} => One of the OleDb providers. Any of them will do.
• {Catalog} => The Directory that contains the file to open.
• HDR=Yes/No => The CSV file contains a Header: if Yes, the Header is the first line of the file
• IMEX=1 => Import/Export Mode set to 1 (Export Mode = 0; Import Mode = 1, Linked Mode = 2), to ignore numeric values and use strings only. Not actually relevant here. Better keep it, as a general aid (in case there's no Header in the file and HDR=Yes).

• FMT=Delimited => File format: Delimited. The Header/Fields are separated by a delimiter. The recognized delimiter is a Comma (,). This setting may be System-dependant (a 3rd part app may have modified the Registry for it's own sake). To specify a delimiter different from the default (the C in CSV means comma), there must be a Schema.ini file in the Catalog folder that defines a specific delimiter for a specific file:

  [MyFile.csv]
  Format=Delimited(;)
  

• Since the Data Source is a directory name (consider it the Database), the file name of the file to open is specified in the query:

  SELECT * FROM MyFile.csv
  

Sample Connection string using Microsoft.ACE.OLEDB.12.0 as provider:

string connectionString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dirName};" +
                            "Extended Properties=\"text; HDR=Yes; IMEX=1; FMT=Delimited\";";

See The Connection Strings Reference web site for other available Connection string formats

Sample code to test the results (using Microsoft.Jet.OLEDB.4.0 in this case):

private void Browse_Click(object sender, EventArgs e)
{
    string userFileName = string.Empty;
    using (OpenFileDialog ofd = new OpenFileDialog())
    {
        ofd.RestoreDirectory = true;
        ofd.Filter = "CSV Files|*.csv|Excel '97-2003|*.xls|Excel 2007-2019|*.xlsx";
        if (ofd.ShowDialog(this) == DialogResult.OK)
            userFileName = ofd.FileName;
    }

    if (userFileName.Length == 0) return;
    this.dataGridView1.DataSource = GetData(userFileName);
}

private DataTable GetData(string userFileName)
{
    string dirName = Path.GetDirectoryName(userFileName);
    string fileName = Path.GetFileName(userFileName);
    string fileExtension = Path.GetExtension(userFileName);
    string connection = string.Empty;
    string query = string.Empty;

    switch (fileExtension)
    {
        case ".xls":
            connection = $@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={userFileName};" +
                           "Extended Properties=\"Excel 8.0; HDR=Yes; IMEX=1\"";
            query = "SELECT * FROM [Sheet1$]";
            break;
        case ".xlsx":
            connection = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={userFileName};" +
                           "Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=1\"";
            query = "SELECT * FROM [Sheet1$]";
            break;
        case ".csv":
            connection = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dirName};" +
                           "Extended Properties=\"text; HDR=Yes; IMEX=1; FMT=Delimited\"";
            query = $"SELECT * FROM {fileName}";
            break;
    }
    return FillData(connection, query);
}

private DataTable FillData(string conString, string query)
{
    var dt = new DataTable();
    using (var con = new OleDbConnection(conString))
    using (var adapter = new OleDbDataAdapter(query, con))
    {
        adapter.Fill(dt);
    };
    return dt;
}