A DataTable represents a table of in-memory relational data. It can be populated from a data source like Microsoft SQL Server or from a file like CSV or Excel. In this article, you will learn how to populate DataTable from CSV, or in other words, how to convert CSV to DataTable in C# and VB.NET using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Convert CSV to DataTable in C# and VB.NET
The following are the main steps to convert CSV to DataTable:
- Initialize an instance of Workbook class.
- Load a CSV file using Workbook.LoadFromFile() method and passing the file path and the delimiter/separator of the CSV file in the form of string as parameters.
- Get the desired worksheet by its index (zero-based) through Workbook.Worksheets[sheetIndex] property.
- Export data from the worksheet to a DataTable using Worksheet.ExportDataTable() method.
(The ExportDataTable() method has several overloads that can be used to control how the data will be exported, for example, ExportDataTable(CellRange range, bool exportColumnNames, bool computedFormulaValue): this overload allows you to specify the range to be exported along with whether to export columns names and calculated values of formulas.
- C#
- VB.NET
using Spire.Xls;
using System;
using System.Data;
using System.Windows.Forms;
namespace ConvertCsvToExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load a CSV file
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\Input.csv", ",");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Export data from the worksheet to a DataTable
DataTable dt = worksheet.ExportDataTable();
//This overload enables you to specify the range to be exported along with whether to export column names and calculated values of formulas
//DataTable dt = worksheet.ExportDataTable(worksheet.Range["A1:C10"], true, true);
//Show the DataTable in a DataGridView control (optional)
dataGridView1.DataSource = dt;
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
