How to Read CSV Files and Save Data in Datatable

CSV (Comma Separated Values) is a simple file format used to store tabular data, such as a spreadsheet or a database. CSV files can be easily opened and manipulated using any text editor, making them a popular choice for data exchange. In this article, we will show you how to read CSV files and save the data in a DataTable using C#.

What is a DataTable?

A DataTable is an in-memory representation of a relational database table. It is a part of the ADO.NET framework, which allows you to interact with databases using C#. A DataTable can be used to store, manipulate, and query data. It is a powerful tool for data processing, and it can be used to bind data to a UI control, such as a GridView or a DataGrid.

Reading CSV Files

To read a CSV file, you can use the StreamReader class to read the file line by line. Each line of the file represents a row in the CSV file. The String.Split method can be used to split each line into an array of strings, where each string represents a column in the CSV file. The following code shows how to read a CSV file and store the data in a DataTable:

using System;
using System.Data;
using System.IO;

class Program
{
static void Main()
{
DataTable dt = new DataTable();

// Add columns to the DataTable
dt.Columns.Add("Column1", typeof(string));
dt.Columns.Add("Column2", typeof(int));
dt.Columns.Add("Column3", typeof(DateTime));

// Read the CSV file
string[] lines = File.ReadAllLines("data.csv");

// Loop through each line
foreach (string line in lines)
{
// Split the line into an array of strings
string[] columns = line.Split(',');

// Create a new DataRow
DataRow dr = dt.NewRow();

// Set the values of the DataRow
dr[0] = columns[0];
dr[1] = int.Parse(columns[1]);
dr[2] = DateTime.Parse(columns[2]);

// Add the DataRow to the DataTable
dt.Rows.Add(dr);
}

// Print the contents of the DataTable
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("{0}, {1}, {2}", dr[0], dr[1], dr[2]);
}
}
}

In the above code, we first create a new DataTable and add three columns to it. Then, we read the CSV file using the File.ReadAllLines method. This method returns an array of strings, where each string represents a line in the file. We then loop through each line and split it into an array of strings using the String.Split method. We use the values of the array to create a new DataRow and add it to the DataTable.

Saving Data in a DataTable

To save the data in a DataTable to a CSV file, you can use the StreamWriter class to write the data to a file. The following code shows how to save the data in a DataTable to a CSV file:

using System;
using System.Data;
using System.IO;

class Program
{
static void Main()
{
DataTable dt = new DataTable();

// Add columns to the DataTable
dt.Columns.Add("Column1", typeof(string));
dt.Columns.Add("Column2", typeof(int));
dt.Columns.Add("Column3", typeof(DateTime));

// Add rows to the DataTable
dt.Rows.Add("Value1", 1, DateTime.Now);
dt.Rows.Add("Value2", 2, DateTime.Now);
dt.Rows.Add("Value3", 3, DateTime.Now);

// Save the DataTable to a CSV file
using (StreamWriter sw = new StreamWriter("data.csv"))
{
// Loop through each row
foreach (DataRow dr in dt.Rows)
{
// Loop through each column
for (int i = 0; i < dt.Columns.Count; i++)
{
// Write the value of the column
sw.Write(dr[i].ToString());

// Write a comma if it is not the last column
if (i < dt.Columns.Count - 1)
{
sw.Write(",");
}
}

// Write a new line
sw.WriteLine();
}
}
}
}

In the above code, we first create a new DataTable and add three columns to it. We then add three rows to the DataTable. To save the data in the DataTable to a CSV file, we use the StreamWriter class to write the data to a file. We loop through each row and column in the DataTable and write the value of the column to the file. We also write a comma between each column, except for the last column. We write a new line after each row.

In this article, we have shown you how to read CSV files and save the data in a DataTable using C#. Reading and writing CSV files is a common task in data processing, and a DataTable is a powerful tool for data processing. By using these techniques, you can easily manipulate and process data in CSV files.

References