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;
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();
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
object
s 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!
Dr. Brian Hart obtained his Ph.D. in Astrophysics from the University of California, Irvine, in 2008. Under Professor David Buote, Dr. Hart researched the structure and evolution of the universe. Dr. Hart is an Astrodynamicist / Space Data Scientist with Point Solutions Group in Colorado Springs, CO, supporting Space Operations Command, United States Space Force. Dr. Hart is a Veteran of the U.S. Army and the U.S. Navy, having most recently served at Fort George G. Meade, MD, as a Naval Officer with a Cyber Warfare Engineer designator. Dr. Hart has previously held positions at Jacobs Engineering supporting Cheyenne Mountain/Space Force supporting tests, with USSPACECOM/J58 supporting operators using predictive AI/ML with Rhombus Power, and with SAIC supporting the Horizon 2 program at STARCOM. Dr. Hart is well known to the community for his over 150 technical publications and public speaking events. Originally from Minneapolis/Saint Paul, Minnesota, Dr. Hart lives in Colorado Springs with his Black Lab, Bruce, and likes bowling, winter sports, exploring, and swimming. Dr. Hart has a new movie coming out soon, a documentary called "Galaxy Clusters: Giants of the Universe," about his outer space research. The movie showcases the Chandra X-ray Observatory, one of NASA’s four great observatories and the world’s most powerful telescopes for detecting X-rays. The movie has been accepted for screening at the U.S. Air Force Academy ("USAFA" for short) Planetarium and will highlight how scientists use clusters of galaxies, the largest bound objects in the Universe, to learn more about the formation and evolution of the cosmos --- as well as the space telescopes used for this purpose, and the stories of the astronauts who launched them and the scientists who went before Dr. Hart in learning more about the nature of the Universe.