Click here to Skip to main content
15,171,053 members
Articles / Desktop Programming / Windows Forms
Posted 4 Nov 2008


204 bookmarked

C# - CSV Import Export

Rate me:
Please Sign up or sign in to vote.
4.83/5 (62 votes)
4 Nov 2008CPOL2 min read
This article introduces import and export functions between a flat CSV file and a SQL server.


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:


  • 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


  • 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



  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);
  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();
		// 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());

		//Creates the select command text
		if (numberOfRows == -1)
			sql_select = "select * from [" + 
					this.FileNevCSV.Trim() + "]";
			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
	catch (Exception e) //Error
		MessageBox.Show(e.Message, "Error - LoadCSV",
	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()
		if (fileCheck())
			// select format, encoding, and write the schema file

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

			//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

			// 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 = 

			// 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
				// 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.

				// Closes the SqlBulkCopy instance

			// Writes the number of imported rows to the form
			this.lblProgress.Text = "Imported: " + 
				this.rowCount.ToString() + "/" + 
				this.rowCount.ToString() + " row(s)";
			// Notifies user
	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)
		// 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:

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

		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.

         // Clears the ListBox

         // 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;
	this.btnExportToCSV.Enabled = true;

	for (int i = 0; i < dt.Rows.Count; i++)
	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);
    // 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;

    // Closes the text stream and the database connection.

    // Notifies the user.



  • 5th November, 2008: Initial post


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


About the Author

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

Comments and Discussions

QuestionModifying ORACLE Server to MSSQL Server (MSSQLLocalDB) Pin
Member 1488845815-Jul-20 9:32
MemberMember 1488845815-Jul-20 9:32 
QuestionC-Code - CSV-Importexport für Visual Studio 2019 Professional mit MSSQLLocalDB Pin
Member 1488845815-Jul-20 7:57
MemberMember 1488845815-Jul-20 7:57 
AnswerRe: C-Code - CSV-Importexport für Visual Studio 2019 Professional mit MSSQLLocalDB Pin
Nelek15-Jul-20 7:58
protectorNelek15-Jul-20 7:58 
QuestionHow can I use with import date from excel file and export to access Pin
michael nabil7-Apr-18 21:57
Membermichael nabil7-Apr-18 21:57 
QuestionHow can I use WHERE with different date format Pin
yogendarji8-Jan-18 22:48
Memberyogendarji8-Jan-18 22:48 
QuestionInserts rare characters at beginning of first column name Pin
Member 126708246-Aug-16 10:59
MemberMember 126708246-Aug-16 10:59 
Questioni code a parametric method base on "András Slyuch" project Pin
Member 1165240217-Aug-15 11:59
MemberMember 1165240217-Aug-15 11:59 
GeneralMy vote of 4 Pin
Aakash Kelkar25-Apr-15 0:38
MemberAakash Kelkar25-Apr-15 0:38 
QuestionVery good and useful project. Congratulations. My 5. Pin
Antonio Barros17-Feb-15 4:39
professionalAntonio Barros17-Feb-15 4:39 
Questionhow convert this project into web? Pin
Member 1013455911-Aug-13 5:15
MemberMember 1013455911-Aug-13 5:15 
QuestionDownload Project Pin
Member - Code.Monkey2-Aug-13 10:02
MemberMember - Code.Monkey2-Aug-13 10:02 
QuestionAccessing Files on ftp site Pin
sai krishna kumboju10-Jul-13 12:42
Membersai krishna kumboju10-Jul-13 12:42 
QuestionHow to load all the column data as string data type? Pin
Member 840468528-Sep-12 14:19
MemberMember 840468528-Sep-12 14:19 
Questionerror Pin
DaigleStyle25-Jul-12 1:51
MemberDaigleStyle25-Jul-12 1:51 
AnswerRe: error Pin
stankotr25-Jul-13 2:22
Memberstankotr25-Jul-13 2:22 
QuestionUsing SamrtDevice Application Pin
Member 914079826-Jun-12 22:30
MemberMember 914079826-Jun-12 22:30 
QuestionHow to make a copy of the selected file and give it a .txt extension. Pin
devenv.exe20-Jun-12 22:31
professionaldevenv.exe20-Jun-12 22:31 
AnswerRe: How to make a copy of the selected file and give it a .txt extension. Pin
András Slyuch20-Jun-12 23:10
MemberAndrás Slyuch20-Jun-12 23:10 
GeneralRe: How to make a copy of the selected file and give it a .txt extension. Pin
devenv.exe20-Jun-12 23:26
professionaldevenv.exe20-Jun-12 23:26 
QuestionAm unable to open connection Pin
David Blay12-May-12 22:56
MemberDavid Blay12-May-12 22:56 
QuestionWhat is this line? Pin
IrishChieftain20058-Feb-12 10:04
MemberIrishChieftain20058-Feb-12 10:04 
GeneralThanks Pin
HMSaad30-Apr-11 18:17
MemberHMSaad30-Apr-11 18:17 
QuestionUnable to Load CSV File Pin
rsalantry24-Apr-11 7:12
Memberrsalantry24-Apr-11 7:12 
AnswerRe: Unable to Load CSV File Pin
richardcainglet14-Nov-11 1:08
Memberrichardcainglet14-Nov-11 1:08 
AnswerRe: Unable to Load CSV File Pin
tomyxd25-Jan-12 19:10
Membertomyxd25-Jan-12 19:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.