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 =>
TheCSV
file contains a Header: ifYes
, 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 andHDR=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 (theC
inCSV
means comma), there must be a Schema.ini file in theCatalog
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;
}