News Category

C#/VB.NET: Convert CSV to DataTable

2022-05-19 06:32:00 Written by  support iceblue
Rate this item
(0 votes)

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;
        }
    }
}

C#/VB.NET: Convert CSV to DataTable

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.

Additional Info

  • tutorial_title:
Last modified on Thursday, 19 May 2022 09:53