Recipe 12.27. Creating a Comma-Separated-Values File

Problem

You need to output data for use in Excel, without automating Excel directly.

Solution

Sample code folder: Chapter 12\GenerateCSV

Create a CSV file, which is simply a text file with commas separating tabular values. The file will have a .csv file extension, which is a format already recognized by Excel.

Discussion

Visual Basic 2005 provides some new, enhanced, easy-to-use shared methods in the My namespace that simplify file reading and writing, among many other things. The sample code presented here uses My.Application.Info.DirectoryPath to get the full path to where the application's EXE file is located and then uses the My.Computer. FileSystem. OpenTextFileWriter() method to create a StreamWriter to write the CSV file at this location:

 ' ----- Create the new output file. Dim csvFile As String = My.Application.Info.DirectoryPath & _    "\Test.csv" Dim outFile As IO.StreamWriter = _    My.Computer.  FileSystem.OpenTextFileWriter(csvFile, False) ' ----- Build the output, including a header row. outFile.WriteLine("Column 1, Column 2, Column 3") outFile.WriteLine("1.23, 4.56, 7.89") outFile.WriteLine("3.21, 6.54, 9.87") outFile.Close( ) ' ----- Display the contents as a message. MsgBox(My.Computer.FileSystem.ReadAllText(csvFile)) ' ----- Display the contents in Excel (if installed). Process.Start(csvFile) 

The StreamWriter object's Write() and WriteLine() methods output lines of text to the file. The Write() method does not automatically append a newline with each call, but the WriteLine() does, so that's what is used in this code.

The StreamWriter's Close() method flushes all lines of text to the file and closes the StreamWriter object. However, when reading a file into a string, you can open, read, and close the file all in one command, as demonstrated by the call to My.Computer.FilesSystem.ReadAllText() in the previous sample code. Using this method to load and display the new file results in Figure 12-16.

Figure 12-16. The contents of the CSV file loaded into a single string

The last line uses Process.Start() to tell the operating system to load the CSV file, using whatever application is registered to process files with a .csv extension. If you have Excel installed, this line of code should open the tabular data in a new work-sheet, as shown in Figure 12-17.

Figure 12-17. The Process.Start( ) method loads and displays the new CSV file in Excel