Title: Create CSV Reader - CSV Parser and Load Dataset from Comma Separated Values TextFile using TextFieldParser (Microsoft.VisualBasic.FileIO.TextFieldParser in C#)
Author:      Greg Dubinovskiy 
Email:       [email protected]
Environment: c# .NET
Keywords:    Load Dataset from CSV File, CSV Reader, CSV PArser, Open Dataset from Text File, Load ListView, Load GridView, TextFile, System.Data.OleDb Namespace, OledbConnection, OleDbCommand
Level:       Intermediate
Description: Create CSV Reader - CSV Parser and Load Dataset from Comma Separated Values TextFile using TextFieldParser (Microsoft.VisualBasic.FileIO.TextFieldParser in C#)
Section      Miscellaneous
SubSection   General

Siccolo Development Articles -Create CSV Reader - CSV Parser and Load Dataset from Comma Separated Values TextFile using TextFieldParser (Microsoft.VisualBasic.FileIO.TextFieldParser in C#)
Google

Open Dataset from CSV (Comma Separated Values) File

The article presented bellow, shows how to open Dataset from a CSV (Comma Separated Values) Text File using TextFieldParser (Microsoft.VisualBasic.FileIO.TextFieldParser in C#).
(see Siccolo Artciles - How to Open Dataset from Text File using OleDB and OleDbConnection)
(see Siccolo miscellaneous development tips - How to Open Dataset from Text File) .


In one of my recent projects, I needed to allow a user to upload Outlook and/or Outlook Express Contacts CSV file, and display it on the page. (to allow user file upload, I used <asp:FileUpload /> control). I did not want to save uploaded CSV file for processing. Instead, I wanted to read it, parse it and display it (see Loaded Dataset from Text File using OleDB and OleDbConnection) .



To accomplish such, I wanted to be able open CSV text file, read, parse it, and load Dataset from opened CSV file, so I can manupulate rows and columns easily. Because I didn't want to save the CSV file, I cound't use OleDbConnection to open Dataset from OleDB-connected CSV file. Instead, I populated Dataset manually using TextFieldParser Class:

TextFieldParser Class: - Provides methods and properties for parsing structured text files.
Parsing a text file with the TextFieldParser is similar to iterating over a text file, while the ReadFields method to extract fields of text is similar to splitting the strings.
The TextFieldParser can parse two types of files: delimited or fixed-width. Some properties, such as Delimiters and HasFieldsEnclosedInQuotes are meaningful only when working with delimited files, while the FieldWidths property is meaningful only when working with fixed-width files. 



	using Microsoft.VisualBasic.FileIO;

	private bool ProcessOutlookCSVFile(Stream inputStream,
                                    string csvType,
                                    out string contactList,
                                    out string errorInfo)
	{
		contactList = String.Empty;
		errorInfo = String.Empty;

		try
		{

			Microsoft.VisualBasic.FileIO.TextFieldParser parser =
				                new TextFieldParser(inputStream);

			parser.TextFieldType = FieldType.Delimited;
			parser.SetDelimiters(",");

			//convert to dataset:
			DataSet contactsData = new DataSet("Contacts");
			contactsData.Tables.Add("ContactList");

			String[] stringRow = parser.ReadFields();
			foreach (String field in stringRow)
			{
				contactsData.Tables[0].Columns.Add(field, Type.GetType("System.String"));
			}

			//populate with data:
			while (!parser.EndOfData)
			{
				stringRow = parser.ReadFields();
				contactsData.Tables[0].Rows.Add(stringRow);
			}
		
			parser.Close();
            
			if (csvType == "Outlook")
			{
				foreach (DataRow dr in contactsData.Tables[0].Rows)
				{
					if (String.IsNullOrEmpty(dr["E-mail Address"].ToString())) { continue; }

					if (contactList != String.Empty) { contactList += ";"; }
					contactList += dr["Last Name"].ToString() + ", " + dr["First Name"].ToString();
				        contactList += "|" + dr["E-mail Address"].ToString();
				}
			}

			if (csvType == "OutlookExpress")
			{
				foreach (DataRow dr in contactsData.Tables[0].Rows)
			        {
			                    if (String.IsNullOrEmpty(dr["E-mail Address"].ToString())) { continue; }

					    if (contactList != String.Empty) { contactList += ";"; }
			                    try
			                    {
			                        contactList += dr["Last Name"].ToString() + ", " + dr["First Name"].ToString();
			                    }
			                    catch
			                    {
			                        contactList += dr["Name"].ToString();
			                    }
                    
					    contactList += "|" + dr["E-mail Address"].ToString();
				}
			}
            
            	        return true;
		}

		catch (Exception ex_process_csv)
		{
			errorInfo = ex_process_csv.Message;
			return false;
		}
	}


Let me try to explain the above code.
Given the Stream object - inputStream - derived from <asp:FileUpload/> .PostedFile.InputStream - I'm opening Microsoft.VisualBasic.FileIO.TextFieldParser - new TextFieldParser(inputStream).
Because, inputStream is a comma separated values (comma delimeted) file - or so I hope - I need to set TextFieldParser accordingly: 

	...
	Microsoft.VisualBasic.FileIO.TextFieldParser parser =
				                new TextFieldParser(inputStream);

     	parser.TextFieldType = FieldType.Delimited;
	parser.SetDelimiters(",");
	...

Next, I read the first line from open file and create columns in a dataset: 
	...
     	DataSet contactsData = new DataSet("Contacts");
     	contactsData.Tables.Add("ContactList");

	
     	String[] stringRow = parser.ReadFields();
	foreach (String field in stringRow)
	{
		contactsData.Tables[0].Columns.Add(field, Type.GetType("System.String"));
	}
	
	...

Here, ReadFields() method allows me to reads all fields on the current line, returns them as an array of strings, and advances the cursor to the next line containing data.
Once Dataset is created and columns are created, then dataset is popualted with data from TextFieldParser: 
	...
     	while (!parser.EndOfData)
	{
		stringRow = parser.ReadFields();
		contactsData.Tables[0].Rows.Add(stringRow);
	}
	...






And that's it. This article shows to parse a simple CSV (comma separated values) file. This is the sort of file produced by spreadsheets and other programs when a text-only, portable format is required for exporting data.
By using TextFieldParser, we can read and bulk load CSV file, have access to field data by index or by field name.
Article keywords: Parse comma delimited file, CSV Reader, CSV File, CSV Parser, Comma Separated Values File, Dataset from CSV File, Load Dataset from Comma Separated Values Text File, TextFieldParser, Microsoft.VisualBasic.FileIO.TextFieldParser , ReadFields(), String[], OleDB namespace, OleDbConnection, OleDbCommand, OleDbDataAdapter, OleDbDataAdapter.Fill(), DataSet, DataColumn, DataRow, ListView, ColumnHeader, GridView, DataSource
Back To Articles Page
Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to [email protected]    Greg Dubinovsky © 2008
or share your thoughts at Siccolo Blog
Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.
Siccolo - SQL Server Management Tool For Mobile Devices is packed with built-in functionality and tools. Siccolo delivers a rich set of management tools for both DBAs and sys admins. SQL Server management has always been an area of DBA concern. The new Management Tool For Mobile Devices - Siccolo - has simple "Enterprise Manager" and the "Query Analyzer". Siccolo is a management tool for the MS SQL Server with administration capabilities and a database query tool. The administration features provide users the ability to browse database structures. An integrated query tool allows users to quickly create, edit and execute SQL queries and scripts. Siccolo also provides an export tool to allow users to easily save and email execution results. Siccolo helps database professionals save time and increase their productivity by utilizing a more efficient approach to database management - use their Windows Mobile empowered device while sipping margarita on the beach For increased security, Siccolo is configured to run under SSL with IIS authentication.