Load a CSV file in Excel in C#

[CSV file]

Loading a CSV file (comma-separated value file) into Excel isn’t too hard once you know the tricks. Using the same tricks you can load a file that uses any delimiter, not just a comma.

First at design time, add a reference to the Microsoft.Office.Interop.Excel library.

To make using the library easy, add the following using statement at the top of your code.

using Excel = Microsoft.Office.Interop.Excel;

This lets you use the word Excel to mean Microsoft.Office.Interop.Excel in your code.

The example uses the following code to open the CSV file.

// Open a delimited file in Excel.
private void btnLoad_Click(object sender, EventArgs e)
{
    // Get the Excel application object.
    Excel.Application excel_app = new Excel.ApplicationClass();

    // Make Excel visible (optional).
    excel_app.Visible = true;

    // Open the file.
    excel_app.Workbooks.Open(
        txtFile.Text,               // Filename
        Type.Missing,
        Type.Missing,
        Excel.XlFileFormat.xlCSV,   // Format
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        txtDelimiter.Text,          // Delimiter
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing);
}

The code first creates an instance of the Excel application. It then uses that object’s Workbooks.Open method to open the file. The tricks here are:

  • Set the format to xlCSV
  • Indicate the delimiter (commas, tabs, semicolons, or whatever)
  • Use Type.Missing for any parameter that you want to omit from the method call

That’s all there is to this example. Once you have the file open, you could use other Excel methods to manipulate the data. For example, you could copy values into other workbooks, save the data in a new file, or load the data into your C# program.

If you try to type a Tab into the delimiter text box, the program moves the focus to the button and ignores the Tab character. To use Tab as a delimiter, copy a Tab from Word or some other program and then paste it into the text box.


Download Example   Follow me on Twitter   RSS feed   Donate




This entry was posted in Excel, files, Office and tagged , , , , , , , , , , , , . Bookmark the permalink.

5 Responses to Load a CSV file in Excel in C#

  1. I am a huge fan of using the Visual Basic TextFieldParser object in C# to read CSV files – well worth a look although you will probably tell me that the C# team have now developed equivalent technology. (Works for other text file formats as well – so a good generic tool).

    Then there is the OpenXml SDK that you can use to read and write Excel files (well modern ones) without having a copy of Excel on the “current” machine. Could be a nice demo.

  2. miniil says:

    Ca fonctionne avec le framework 3.5, même en spécifiant un délimiteur autre que celui présent dans le fichier, ou en utilisant la fonction Open avec le nom de fichier comme seul paramètre.
    Mais au-delà du framework 3.5 🙁 🙁

    • RodStephens says:

      Google Translate:

      It works with the 3.5 framework, even specifying a delimiter other than that present in the file, or by using the Open function with the filename as the only parameter.
      But beyond the 3.5 framework 🙁 🙁

      • RodStephens says:

        Try using Add Reference. Click on Extensions and then search for “Excel.” You should find the interop assemblies there.

        You’ll also have to change this statement:

        Excel.Application excel_app =
            new Excel.ApplicationClass();

        To this statement:

        Excel.Application excel_app =
            new Excel.Application();

        Then it should work.

Leave a Reply

Your email address will not be published. Required fields are marked *