OpenFileDialog can't load CSV files but can load xls/xlsx Excel files

Question

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.

In your click handler, you set `sConnectionString` in two places, one if your split piece is "xls", and another if it's "xlsx". You don't set it otherwise. But in your `FillData` method, you check its length (which fires off the NRE). Consider using methods in the `System.IO.Path` class rather than `string.Split` to pick out paths — Flydog57, Jan 24 '19 at 15:07
Do you have Microsoft office installed ? ACE driver comes with office. If you do not have Office then you have to install ACE from msdn. I've never been successful in installing ACE, but other have claimed it works. — jdweng, Jan 24 '19 at 15:09
I'm a bit surprised that "xlsx" works. Your if/then/else logic is messed up. The only way to get to the "xlsx" check is if `Arr.Length` is <= 0, so your `else if` should throw. Have you stepped through this with a debugger at all? — Flydog57, Jan 24 '19 at 15:09
@Jimi What would i use for csv? I have tried both the ACE and the JET providers — Matt, Jan 24 '19 at 15:16
The version number (in your case 12) of ace need to be in following : C:\Program Files\Common Files\system\ado like msado12.dll — jdweng, Jan 24 '19 at 15:31

Jimi · Accepted Answer · 2021-08-23T20:31:35.287

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 (var ofd = new OpenFileDialog()) {
        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;
    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 conString = string.Empty;
    string query = string.Empty;

    switch (fileExtension)
    {
        // Can also use Microsoft.ACE.OLEDB.12 or Microsoft.ACE.OLEDB.16
        case ".xls":
            conString = $@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={userFileName};" +
                           "Extended Properties=\"Excel 8.0; HDR=Yes; IMEX=1\"";
            query = "SELECT * FROM [Sheet1$]";
            break;
        // Can also use Microsoft.ACE.OLEDB.16
        case ".xlsx":
            conString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={userFileName};" +
                           "Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=1\"";
            query = "SELECT * FROM [Sheet1$]";
            break;
        // Can also use Microsoft.ACE.OLEDB.16
        case ".csv":
            conString = $@"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(conString, query);
}

private DataTable FillData(string conString, string query)
{
    var dt = new DataTable();
    using (var con = new OleDbConnection(conString)) { 
        con.Open();
        using (var cmd = new OleDbCommand(query, con))
        using (var reader = cmd.ExecuteReader()) {
            dt.Load(reader);
        };
    }
    return dt;
}

OpenFileDialog can't load CSV files but can load xls/xlsx Excel files

1 Answers1

Linked