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

C# - CSV Import Export

, 4 Nov 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
This article introduces import and export functions between a flat CSV file and a SQL server.
CSV_import_export1.png

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

CSV_import_export2.png

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:

    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

History

  • 5th November, 2008: Initial post

License

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

Share

About the Author

András Slyuch
Hungarian Post Co. Ltd
Hungary Hungary
No Biography provided

Comments and Discussions

 
Questionhow convert this project into web? PinmemberMember 1013455911-Aug-13 5:15 
QuestionDownload Project PinmemberMember 27533332-Aug-13 10:02 
QuestionAccessing Files on ftp site Pinmembersai krishna kumboju10-Jul-13 12:42 
QuestionHow to load all the column data as string data type? PinmemberMember 840468528-Sep-12 14:19 
Questionerror PinmemberDaigleStyle25-Jul-12 1:51 
AnswerRe: error Pinmemberstankotr25-Jul-13 2:22 
QuestionUsing SamrtDevice Application PinmemberMember 914079826-Jun-12 22:30 
QuestionHow to make a copy of the selected file and give it a .txt extension. PinmemberEricCiz20-Jun-12 22:31 
Hi Slyuch, great article. I need some help here, am trying to customise this utility. When importing, i need to select a file, make a copy of the selected file and give it an extension of .txt (the file is not a text file but it does open in Notepad).
 
Now in every other place where the file i browsed to is need, i want to work with the copy which i gave the .txt extension.
 
Thanks.
"Coming soon"



AnswerRe: How to make a copy of the selected file and give it a .txt extension. PinmemberSlyuch, András20-Jun-12 23:10 
GeneralRe: How to make a copy of the selected file and give it a .txt extension. PinmemberEricCiz20-Jun-12 23:26 
QuestionAm unable to open connection PinmemberDavid Blay12-May-12 22:56 
QuestionWhat is this line? PinmemberIrishChieftain20058-Feb-12 10:04 
GeneralThanks PinmemberHMSaad30-Apr-11 18:17 
QuestionUnable to Load CSV File Pinmemberrsalantry24-Apr-11 7:12 
AnswerRe: Unable to Load CSV File Pinmemberrichardcainglet14-Nov-11 1:08 
AnswerRe: Unable to Load CSV File Pinmembertomyxd25-Jan-12 19:10 
AnswerRe: Unable to Load CSV File Pinmemberjoelsef12-Mar-12 17:39 
GeneralRe: Unable to Load CSV File PinmemberDavid Blay12-May-12 23:15 
GeneralCongratulations Pinmembermj_logan30-Mar-11 13:52 
QuestionIs the entire file loaded into memory? Pinmemberopc325-Feb-11 6:53 
GeneralMy vote of 5 PinmemberJackBo215-Dec-10 9:55 
GeneralError Message Pinmemberllcorso5-Dec-10 14:37 
GeneralRe: Error Message Pinmemberjasinthebasin4-Jan-12 2:10 
GeneralRe: Error Message Pinmemberjoelsef12-Mar-12 17:41 
GeneralMy vote of 5 Pinmemberstevef ferguson1-Dec-10 19:15 

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 | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 5 Nov 2008
Article Copyright 2008 by András Slyuch
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid