CSV to Datatable-Which One is More Simpler?

CSV to datatable questions, as well as their answers, are frequently referred in programming forums. They are either simple or complex. In this post, I will display some csv/excel to datatable methods. And give a slight comment to all of them.

First let us see a question of CSV to Datatable in msdn. The first answer of this question introduces an MS Excel component. There is no method provided. But do not worry, you can see the detail methods below.

Using this .NET Excel component, you need not install MS Excel. The whole method of CSV to Datatable only needs two lines of key code. Two main methods are called: one is Workbook.LoadFromFile(string fileName, string separator)to load CSV file from system; the other is Worksheet.ExportDataTable() to export CSV data to datatable. Here is the whole code for CSV to datatable:

C#:

using Spire.Xls;
using Spire.Xls.Converter;
namespace ExportDataFromCSV
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = "CSV document(*.csv)|*.csv";
            DialogResult result = dialog.ShowDialog();
            if (result == DialogResult.OK)
            {
                string csvFile = dialog.FileName;
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"D:\michelle\my file\csvtodatatable.csv", ",");
                Worksheet worksheet = workbook.Worksheets[0];
                System.Data.DataTable t = worksheet.ExportDataTable();
                this.dataGridView1.DataSource = t;            
            }
        }
    }
}

 

VB.NET:

Imports Spire.Xls
 Imports Spire.Xls.Converter

 Namespace ExportDataFromCSV

     Public Class Form1
         Inherits Form

         Public Sub New()
             MyBase.New
             InitializeComponent
         End Sub

         Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
             Dim dialog As OpenFileDialog = New OpenFileDialog
             dialog.Filter = "CSV document(*.csv)|*.csv"
             Dim result As DialogResult = dialog.ShowDialog
             If (result = DialogResult.OK) Then
                 Dim csvFile As String = dialog.FileName
                 Dim workbook As Workbook = New Workbook
                 workbook.LoadFromFile("D:\michelle\my file\csvtodatatable.csv", ",")
                 Dim worksheet As Worksheet = workbook.Worksheets(0)
                 Dim t As System.Data.DataTable = worksheet.ExportDataTable
                 Me.dataGridView1.DataSource = t
             End If
         End Sub
     End Class
 End Namespace

Here goes an effect image of csv to datatable:

After testing, I find that excel to datatable is almost as easy as CSV to datatable, so I want to share excel to datatable with all of you:

C#:

using Spire.Xls;
using System.Data;
namespace Export
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a workbook
            Workbook workbook = new Workbook();
            //Load the file
            workbook.LoadFromFile("DataTableSample.xls");
            //Initailize worksheet
            Worksheet sheet = workbook.Worksheets[0];
            //Export datatable
            DataTable dataTable = sheet.ExportDataTable();
        }
    }
}

VB.NET:

Imports Spire.Xls
Imports System.data
Module Module1
    Sub Main()
        'Create a workbook
        Dim workbook As New Workbook()
        'Load the file
        workbook.LoadFromFile("DataTableSample.xls")
        'Initialize the worksheet
        Dim sheet As Worksheet = workbook.Worksheets(0)
        'Export datatable
        Dim dataTable As DataTable = sheet.ExportDataTable()
    End Function
End Module

Now let us see the third answer in the forum. The project is implemented in Visual Studio C# 2005, and uses the .NET Framework 2.0. You can see below csv to datatable code:

C#:

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

VB.NET:

     Public Function LoadCSV(ByVal numberOfRows As Integer) As DataSet
         Dim ds As DataSet = New DataSet
         Try 
             ' Creates and opens an ODBC connection
             Dim strConnString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)}"
             Dbq = " + this.dirCSV.Trim() + "
             Extensions = asc
             csv
             tab
             txt
             Dim Security As Persist
             Info = False
             ""
             Dim sql_select As String
             Dim conn As OdbcConnection
             conn = New OdbcConnection(strConnString.Trim)
             conn.Open
             'Creates the select command text
             If (numberOfRows = -1) Then
                 sql_select = ("select * from ["  _
                             + (Me.FileNevCSV.Trim + "]"))
             Else
                 sql_select = ("select top "  _
                             + (numberOfRows + (" * from ["  _
                             + (Me.FileNevCSV.Trim + "]"))))
             End If
             'Creates the data adapter
             Dim obj_oledb_da As OdbcDataAdapter = 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 e As Exception
             MessageBox.Show(e.Message, "Error - LoadCSV", MessageBoxButtons.OK, MessageBoxIcon.Error)
         End Try
         Return ds
     End Function
After comparison, you can find that solution 1 is very simple, while in solution 2, it built a OdbcConnection to be ready to transfer the connection into the SQL Server. You can choose either of them.See How to Export Datatable to CSV

Leave a Reply