Click here to Skip to main content
Click here to Skip to main content

Tagged as

Import a CSV File Into an Excel Workbook Programmatically

, 19 Dec 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Code to call Excel Interop to take a Comma-Separated-Values (.CSV) file and suck it into your Excel workbook programmatically, and then Auto-Fit the resultant columns.

Version and System Requirements

You must add references for the Microsoft.Office.Core to your solution from the .NET tab of the Visual Studio Add Reference dialog box, and the Microsoft Excel 12.0 Object Library (you can use 14.0 if you want, too, but nothing lower).

This code has been tested with Office 2007 and higher interop libraries.
 

Background

Sometimes, you will have an SSIS package or other process which might, say, extract records from a Database and put them into a Comma-Separated-Values (CSV) file.
 
CSV files are very useful in that they contain data in a compact, storage-effective format and can be imported into many a spreadsheet and database program, such as Excel.
 
Oftentimes, programmers find themselves with a client requirement to automate the process of sucking some CSV file into Excel.

Now For the Code

I wrote a method to import CSVs into Excel, coded as shown in Listing 1.
 
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
 
/// <summary>
/// Takes a CSV file and sucks it into the specified worksheet of this workbook at the specified range
/// </summary>
/// <param name="importFileName">Specifies the full path to the .CSV file to import</param>
/// <param name="destinationSheet">Excel.Worksheet object corresponding to the destination worksheet.</param>
/// <param name="destinationRange">Excel.Range object specifying the destination cell(s)</param>
/// <param name="columnDataTypes">Column data type specifier array. For the QueryTable.TextFileColumnDataTypes property.</param>
/// <param name="autoFitColumns">Specifies whether to do an AutoFit on all imported columns.</param>
public void ImportCSV(string importFileName, Excel.Worksheet destinationSheet,
    Excel.Range destinationRange, int[] columnDataTypes, bool autoFitColumns)
{
    destinationSheet.QueryTables.Add(
        "TEXT;" + Path.GetFullPath(importFileName),
    destinationRange, Type.Missing);
    destinationSheet.QueryTables[1].Name = Path.GetFileNameWithoutExtension(importFileName);
    destinationSheet.QueryTables[1].FieldNames = true;
    destinationSheet.QueryTables[1].RowNumbers = false;
    destinationSheet.QueryTables[1].FillAdjacentFormulas = false;
    destinationSheet.QueryTables[1].PreserveFormatting = true;
    destinationSheet.QueryTables[1].RefreshOnFileOpen = false;
    destinationSheet.QueryTables[1].RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells;
    destinationSheet.QueryTables[1].SavePassword = false;
    destinationSheet.QueryTables[1].SaveData = true;
    destinationSheet.QueryTables[1].AdjustColumnWidth = true;
    destinationSheet.QueryTables[1].RefreshPeriod = 0;
    destinationSheet.QueryTables[1].TextFilePromptOnRefresh = false;
    destinationSheet.QueryTables[1].TextFilePlatform = 437;
    destinationSheet.QueryTables[1].TextFileStartRow = 1;
    destinationSheet.QueryTables[1].TextFileParseType = XlTextParsingType.xlDelimited;
    destinationSheet.QueryTables[1].TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote;
    destinationSheet.QueryTables[1].TextFileConsecutiveDelimiter = false;
    destinationSheet.QueryTables[1].TextFileTabDelimiter = false;
    destinationSheet.QueryTables[1].TextFileSemicolonDelimiter = false;
    destinationSheet.QueryTables[1].TextFileCommaDelimiter = true;
    destinationSheet.QueryTables[1].TextFileSpaceDelimiter = false;
    destinationSheet.QueryTables[1].TextFileColumnDataTypes = columnDataTypes;
 
    Logger.GetInstance().WriteLog("Importing data...");
    destinationSheet.QueryTables[1].Refresh(false);
 
    if (autoFitColumns==true)
        destinationSheet.QueryTables[1].Destination.EntireColumn.AutoFit();
 
    // cleanup
    this.ActiveSheet.QueryTables[1].Delete();
}
 
Listing 1. Inserting CSV data into Excel.
 

Usage Notes

Here's an example of how to call this method:
 
myOwnWorkbookClass.ImportCSV(
     @"C:\MyStuff\MyFile.CSV",
     (Excel.Worksheet)(MyWorkbook.Worksheets[1]),
     (Excel.Range)(((Excel.Worksheet)MyWorkbook.Worksheets[1]).get_Range("$A$7")),
     new int[] { 2, 2, 2, 2, 2 }, true);
 
Listing 2. How to call the ImportCSV method.
 

Things to Keep in Mind

Arrays in Excel Are One-Based

 
Keep in mind that the MyWorkbook object is a Excel.Workbook object and we are accessing the first Excel.Worksheet in the MyWorkbook.Worksheets array. Make sure you remember that in Excel, arrays are one-based, not zero-based; i.e., array[1] is the first element of Excel arrays.
 

Casting

Casting is very important, since stuff comes out of Excel Interop arrays and properties as objects most of the time. So you have to be sure and cast your parameters correctly.

Specifying the Data Types for Your Input Columns

Notice that I input the parameter value new int[] { 2, 2, 2, 2, 2 } in the call to the ImportCSV method in Listing 2. This parameter expresses the data types you want to format the destination Excel columns as. These are the types found under the Format > Cells menu item.
 
How do we know which numbers to put? First of all, there must be the same number of values in this array as there are columns in your CSV file. So my CSV file here has 5 columns, hence the array has 5 values.
 
Secondly, the parameter fills in the TextFileColumnDataTypes property of the QueryTable object representing the destination of the import. The hyperlinked text refers to the MSDN Library reference. In the reference, it gives you Xl* enumeration constant values. The 2's in the value above just happpen to stand for the xlTextFormat enumeration value in Excel 2007. Use this property only when your query table is based on data from a text file (with the QueryType property set to xlTextImport). If you put more values in this parameter than there are columns in your text file, they will be ignored.

 
How do I know which numbers (1,2,3, etc.) correspond to xlGeneralFormat, xlTextFormat, etc.? I don't know for sure, but my suspicion is that, if you look at the table of choices in the web page linked above, and number the rows of the table from 1 to 10, the number corresponding to the row you want should be put into your int[] array.
 
Happy Importing!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Brian C Hart
Software Developer (Senior) Corrugated Technologies, Inc.
United States United States
From Fridley, Minnesota and I like computer programming! When I got started, I was working mostly with Windows GUI programming in C/C++. Then later on I worked with COM/DCOM for a school internship. I used COM/DCOM to write an ad hoc cluster server and job-running environment for a cluster of 24 Windows-based high-end visualization workstations. I moved on to C# and have been working in C# and Windows Forms ever since. I have yet to embrace Silverlight Smile | :)
Follow on   Twitter

Comments and Discussions

 
GeneralVery useful PinmemberSivaji15653-Apr-14 0:45 
QuestionWhat if the csv contains German characters PinmemberNikhil Khurana20-Mar-14 17:33 
GeneralExcellent Pinmemberanil Madan20-Dec-10 8:20 
GeneralMy vote 5 for good work! PinmemberAbdul Quader Mamun17-Dec-10 5:19 
GeneralThanks for sharing. PinmemberSlacker00717-Dec-10 0:27 
GeneralThanks for the tip. PinsubeditorWalt Fair, Jr.16-Dec-10 13:15 
GeneralGood tips PinmemberAbdul Quader Mamun16-Dec-10 8:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 19 Dec 2010
Article Copyright 2010 by Brian C Hart
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid