Openfiledialog Can't Load CSV Files But Can Load Xls/Xlsx Excel Files

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

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;
}

Open File Dialog, One Filter for Multiple Excel Extensions?

Use a semicolon

OpenFileDialog of = new OpenFileDialog();
of.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";

Import Data from a xls file to datagridview?

You have to work with office interop libaries.

https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/interop/how-to-access-office-onterop-objects

Why can't I load an Excel file automatically?

All of your code seems to be running in the constructor for the form. The form fields have not been created yet.

Move your code into the form_load event and your code will work better.

Explanation: You will often get strange behavior with this programming approach. The threading model for winforms is hard to explain sometimes. Which is why it may work with your "manual" approach because of the delays, which allow the background thread to catch-up.

Loading CSV file in C# Winform get's older version

i Found the issue, it was different then i thought.
Seeing older versions of the file were also in the folder i didn't notice that it wasn't taking an older version of itself but it was simply merging all CSV files in to the output.

I searched for this instead and found the following post:
c# reading csv file gives not a valid path

i added an if clause to see if the file is a csv file and then use the following:
Because the difference between opening an Excel or a CSV file is is that the Excel file asked for path and file name while CSV only wants the path and later on a query to select the file...a bit odd but ok.

MyCommand = new OleDbDataAdapter("SELECT * FROM [" + Path.GetFileName(openFileDialog1.FileName) + "]", MyConnection);

How can I add 2 cells in a CSV file in WPF?

There you go:

....
while ((line = file.ReadLine()) != null)
{
    if (counter == 0)
    {
        line += ",column1,column2";
    }
    else
    {
        line += $",{value1},{value2}";
    }

    counter++;
}
...

---

Related Topics