C# - CSV Import Export

•  Download source - 88.67 KB

Introduction

This article introduces and implements import and export functions between a flat CSV file and a SQL server. This project implemented in Visual Studio C# 2005, and uses the .NET Framework 2.0.

The base of this project was a former Code Project article: Importing CSV Data and saving it in database.

This project contains new functionalities, for example: export functions, creating table in the database and using bulk copy, which is new in .NET 2.0. (The former project uses .NET 1.1.)

This example gives commented code samples to the following processes:

Import:

• Connect to a CSV file through an ODBC driver, and read it as a database table (based on referenced article)
• Use different encodings and separators (based on referenced article)
• Load a CSV file into a DataSet (based on referenced article)
• How to show a preview of a CSV file (based on referenced article)
• Transfer data with a SqlBulkCopy instance to a SQL server
  Source: a DataSet instance
• Transfer data with a SqlBulkCopy instance to a SQL server
  Source: an ODBC connection
• Create a new table in a SQL database based on the CSV file using schema tables
• Write the progress of the bulk copy operation using an event

Export:

• Browse user tables in a SQL database
• Use different encodings and separators
• Read data from SQL server using a SqlDataReader, and transfer data to a flat file using a StreamWriter

Using

1. Download the project.

2. Make a new database on a SQL server, or use an existing database.

3. Modify the connection string in the application. You can find it in prop.cs:

   C#

   public static string sqlConnString = "server=(local);
   	database=Test_CSV_impex;Trusted_Connection=True";

4. Run the project.

Some Piece of Code

Loading data from CSV into a DataSet:

/*
 * Loads the csv file into a DataSet.
 * 
 * If the numberOfRows parameter is -1, it loads all rows, otherwise it
 * loads the first specified number of rows (for preview)
 */

public DataSet LoadCSV(int numberOfRows)
{
	DataSet ds = new DataSet();
	try
	{
		// Creates and opens an ODBC connection
		string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};
			Dbq=" + this.dirCSV.Trim() + ";
			Extensions=asc,csv,tab,txt;Persist Security Info=False";
		string sql_select;
		OdbcConnection conn;
		conn = new OdbcConnection(strConnString.Trim());
		conn.Open();

		//Creates the select command text
		if (numberOfRows == -1)
		{
			sql_select = "select * from [" + 
					this.FileNevCSV.Trim() + "]";
		}
		else
		{
			sql_select = "select top " + numberOfRows + 
				" * from [" + this.FileNevCSV.Trim() + "]";
		}

		//Creates the data adapter
		OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
				
		//Fills dataset with the records from CSV file
		obj_oledb_da.Fill(ds, "csv");
				
		//closes the connection
		conn.Close();
	}
	catch (Exception e) //Error
	{
		MessageBox.Show(e.Message, "Error - LoadCSV",
				MessageBoxButtons.OK,MessageBoxIcon.Error);
	}
	return ds;
}

Transfer data from an ODBC connection into the SQL server with SqlBulkCopy:

/*
 * Imports data to the database with SqlBulkCopy.
 * This method doesn't use a temporary dataset, it loads
 * data immediately from the ODBC connection
 */

private void SaveToDatabaseDirectly()
{
	try
	{
		if (fileCheck())
		{
			// select format, encoding, and write the schema file
			Format();
			Encoding();
			writeSchema();

			// Creates and opens an ODBC connection
			string strConnString = "Driver={Microsoft Text Driver 
				(*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";
				Extensions=asc,csv,tab,txt;
				Persist Security Info=False";
			string sql_select;
			OdbcConnection conn;
			conn = new OdbcConnection(strConnString.Trim());
			conn.Open();

			//Counts the row number in csv file - with an SQL query
			OdbcCommand commandRowCount = new OdbcCommand
				("SELECT COUNT(*) FROM [" + 
				this.FileNevCSV.Trim() + "]", conn);
			this.rowCount = System.Convert.ToInt32
					(commandRowCount.ExecuteScalar());

			// Creates the ODBC command
			sql_select = "select * from [" + 
					this.FileNevCSV.Trim() + "]";
			OdbcCommand commandSourceData = 
				new OdbcCommand(sql_select, conn);

			// Makes on OdbcDataReader for reading data from CSV
			OdbcDataReader dataReader = 
				commandSourceData.ExecuteReader();

			// Creates schema table. 
                           // It gives column names for create table command.
			DataTable dt;
			dt = dataReader.GetSchemaTable();

			// You can view that schema table if you want:
			//this.dataGridView_preView.DataSource = dt;

			// Creates a new and empty table in the sql database
			CreateTableInDatabase(dt, this.txtOwner.Text, 
				this.txtTableName.Text, prop.sqlConnString);

			// Copies all rows to the database from the data reader.
			using (SqlBulkCopy bc = new SqlBulkCopy
				("server=(local);database=Test_CSV_impex;
				Trusted_Connection=True"))
			{
				// Destination table with owner - 
				// this example doesn't
				// check the owner and table names!
				bc.DestinationTableName = "[" + 
					this.txtOwner.Text + "].[" + 
					this.txtTableName.Text + "]";

				// User notification with the SqlRowsCopied event
				bc.NotifyAfter = 100;
				bc.SqlRowsCopied += 
				   new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

				// Starts the bulk copy.
				bc.WriteToServer(dataReader);

				// Closes the SqlBulkCopy instance
				bc.Close();
			}

			// Writes the number of imported rows to the form
			this.lblProgress.Text = "Imported: " + 
				this.rowCount.ToString() + "/" + 
				this.rowCount.ToString() + " row(s)";
			this.lblProgress.Refresh();
					
			// Notifies user
			MessageBox.Show("ready");
		}
	}
	catch (Exception e)
	{
		MessageBox.Show(e.Message, "Error - SaveToDatabaseDirectly", 
				MessageBoxButtons.OK, MessageBoxIcon.Error);
	}
}

Creating table in the database based on a schema table:

/*
 * Generates the create table command using the schema table, and
 * runs it in the SQL database.
 */

private bool CreateTableInDatabase(DataTable dtSchemaTable, string tableOwner, 
		string tableName, string connectionString)
{
	try
	{
		// Generates the create table command.
		// The first column of schema table contains the column names.
		// The data type is nvarcher(4000) in all columns.

		string ctStr = "CREATE TABLE [" + tableOwner + "].[" + 
						tableName + "](\r\n";
		for (int i = 0; i < dtSchemaTable.Rows.Count; i++)
		{
			ctStr += "  [" + dtSchemaTable.Rows[i][0].ToString() + "]
					[nvarchar](4000) NULL";
			if (i < dtSchemaTable.Rows.Count)
			{
				ctStr += ",";
			}
			ctStr += "\r\n";
		}
		ctStr += ")";

		// You can check the SQL statement if you want:
		//MessageBox.Show(ctStr);

		// Runs the SQL command to make the destination table.		
		SqlConnection conn = new SqlConnection(connectionString);
		SqlCommand command = conn.CreateCommand();
		command.CommandText = ctStr;
		conn.Open();
		command.ExecuteNonQuery();
		conn.Close();

		return true;
	}
	catch (Exception e)
	{
		MessageBox.Show(e.Message, "CreateTableInDatabase", 
			MessageBoxButtons.OK, MessageBoxIcon.Error);
		return false;
	}
}

Getting user table names from the database:

/*
 * Loads list of user tables from the SQL database, and fills
 * a ListBox control with tatble names.
 */

private void loadTables()
{
         // Connects to database, and selects the table names.
         SqlConnection cn = new SqlConnection(prop.sqlConnString);
	SqlDataAdapter da = new SqlDataAdapter
			("select name from dbo.sysobjects where xtype = 'U' 
		and name <> 'dtproperties' order by name", cn);
	DataTable dt = new DataTable();
			
         // Fills the list to an DataTable.
	da.Fill(dt);

         // Clears the ListBox
	this.lbxTables.Items.Clear();

         // Fills the table names to the ListBox.
         // Notifies user if there is no user table in the database yet.
	if (dt.Rows.Count == 0)
	{
	MessageBox.Show("There is no user table in the specified database. 
		Import a CSV file first.", "Warning", 
		MessageBoxButtons.OK, MessageBoxIcon.Warning);
	this.lbxTables.Items.Add("< no user table in database >");
	this.btnExportToCSV.Enabled = false;
	}
	else
	{
	this.btnExportToCSV.Enabled = true;

	for (int i = 0; i < dt.Rows.Count; i++)
	{
	    this.lbxTables.Items.Add(dt.Rows[i][0].ToString());
	}
	this.lbxTables.SelectedIndex = 0;
	}
}

Writing data with a StreamWriter:

/*
 * Exports data to the CSV file.
 */

private void exportToCSVfile(string fileOut)
{
    // Connects to the database, and makes the select command.
    SqlConnection conn = new SqlConnection(prop.sqlConnString);
    string sqlQuery = "select * from " + this.lbxTables.SelectedItem.ToString();
    SqlCommand command = new SqlCommand(sqlQuery, conn);
    conn.Open();
			
    // Creates a SqlDataReader instance to read data from the table.
    SqlDataReader dr = command.ExecuteReader();

    // Retrieves the schema of the table.
    DataTable dtSchema = dr.GetSchemaTable();

    // Creates the CSV file as a stream, using the given encoding.
    StreamWriter sw = new StreamWriter(fileOut, false, this.encodingCSV);
			
    string strRow; // represents a full row

    // Writes the column headers if the user previously asked that.
    if (this.chkFirstRowColumnNames.Checked)
    {
	sw.WriteLine(columnNames(dtSchema, this.separator));
    }

    // Reads the rows one by one from the SqlDataReader
    // transfers them to a string with the given separator character and
    // writes it to the file.
    while (dr.Read())
    {
	strRow = "";
	for (int i = 0; i < dr.FieldCount; i++)
	{
	    strRow += dr.GetString(i);
	    if (i < dr.FieldCount - 1)
	    {
		strRow += this.separator;
	    }
	}
	sw.WriteLine(strRow);
    }

    // Closes the text stream and the database connection.
    sw.Close();
    conn.Close();

    // Notifies the user.
    MessageBox.Show("ready");
}

Reference

• Importing CSV Data and saving it in database (by Mukund Pujari)

History

• 5^th November, 2008: Initial post