//initialising a StreamReader type variable and will pass the file location
StreamReader oStreamReader = new StreamReader("CustomerDetails.csv");
DataTable oDataTable = null;
int RowCount = 0;
string[] ColumnNames = null;
string[] oStreamDataValues=null;
//using while loop read the stream data till end
while (!oStreamReader.EndOfStream)
{
String oStreamRowData = oStreamReader.ReadLine().Trim();
if (oStreamRowData.Length > 0)
{
oStreamDataValues = oStreamRowData.Split(',');
//Bcoz the first row contains column names, we will poluate
//the column name by
//reading the first row and RowCount-0 will be true only once
if (RowCount == 0)
{
RowCount = 1;
ColumnNames = oStreamRowData.Split(',');
oDataTable = new DataTable();
//using foreach looping through all the column names
foreach (string csvcolumn in ColumnNames)
{
DataColumn oDataColumn
= new DataColumn(csvcolumn.ToUpper(), typeof(string));
//setting the default value of empty.string to newly created column
oDataColumn.DefaultValue = string.Empty;
//adding the newly created column to the table
oDataTable.Columns.Add(oDataColumn);
}
}
else
{
//creates a new DataRow with the same schema as of the
oDataTable
DataRow oDataRow = oDataTable.NewRow();
//using foreach looping through all the column names
for (int i = 0; i < ColumnNames.Length; i++)
{
oDataRow[ColumnNames[i]] = oStreamDataValues[i]
== null ? string.Empty : oStreamDataValues[i].ToString();
}
//adding the newly created row with data to the oDataTable
oDataTable.Rows.Add(oDataRow);
}
}
}
//close the oStreamReader object
oStreamReader.Close();
//release all the resources used by the oStreamReader object
oStreamReader.Dispose();
//Looping through all the rows in the Datatable
foreach (DataRow oDataRow in oDataTable.Rows)
string RowValues=string.Empty;
//Looping through all the columns in a row
foreach (string csvcolumn in ColumnNames)
{
//concatenating the values for display purpose
RowValues += csvcolumn + "=" + oDataRow[csvcolumn].ToString()+"; ";
}
//Displaying the result on the console window
Console.WriteLine(RowValues);
}
1. public static DataTable ConvertCSVtoDataTable(string
strFilePath)
2. {
3. StreamReader sr = new StreamReader(strFilePath);
4. string[] headers = sr.ReadLine().Split(',');
5. DataTable dt = new DataTable();
6. foreach (string header in headers)
7. {
8. dt.Columns.Add(header);
9. }
10. while (!sr.EndOfStream)
11. {
12. string[] rows = Regex.Split(sr.ReadLine(),
",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
13. DataRow dr = dt.NewRow();
14. for (int i = 0; i < headers.Length; i++)
15. {
16. dr[i] = rows[i];
17. }
18. dt.Rows.Add(dr);
19. }
20. return dt;
21. }
22.
Call the ConvertCSVtoDataTable function like below by passing path of the CSV file.
1. static void Main(string[] args)
2. {
3. string filepath = "d://ConvertedFile.csv";
4. DataTable res = ConvertCSVtoDataTable(filepath);
5. }
6.
using System.IO;
static void Main(string[] args)
{
using(var fs = File.OpenRead(@"C:\test.csv"))
using(var reader = new StreamReader(fs))
{
List<string> listA = new List<string>();
List<string> listB = new List<string>();
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(';');
listA.Add(values[0]);
listB.Add(values[1]);
}
}
}
var column1 = new List<string>();
var column2 = new List<string>();
using (var rd = new StreamReader("filename.csv"))
{
while (!rd.EndOfStream)
{
var splits = rd.ReadLine().Split(';');
column1.Add(splits[0]);
column2.Add(splits[1]);
}
}
// print column1
Console.WriteLine("Column 1:");
foreach (var element in column1)
Console.WriteLine(element);
// print column2
Console.WriteLine("Column 2:");
foreach (var element in column2)
Console.WriteLine(element);
public DataTable CsvFileToDatatable(string path, bool IsFirstRowHeader)
{
string header = "No";
string sql = string.Empty;
DataTable dataTable = null;
string pathOnly = string.Empty;
string fileName = string.Empty;
try
{
pathOnly = Path.GetDirectoryName(path);
fileName = Path.GetFileName(path);
sql = @"SELECT * FROM [" + fileName + "]";
if (IsFirstRowHeader)
{
header = "Yes";
}
using (OleDbConnection connection = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
pathOnly +
";Extended Properties=\"Text;HDR=" + header + "\""))
{
using (OleDbCommand command = new OleDbCommand(sql, connection))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
dataTable = new DataTable();
dataTable.Locale = CultureInfo.CurrentCulture;
adapter.Fill(dataTable);
}
}
}
}
finally
{
}
return dataTable;
}
Parse CSV Text File to Data Table Using C#
Find below a code that parses the CSV text file and give you the records as a Data Table
Using Section
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
Methods for Parsing CSV
public static DataTable ReadCSV(String filename) {
var csvData = new DataTable();
StreamReader csvFile = null;
try {
csvFile = new StreamReader(filename);
// Parse header
var headerLine = csvFile.ReadLine();
var columns = ParseCSVLine(headerLine);
columns.ForEach(c => csvData.Columns.Add(c, typeof(String)));
var line = "";
while ((line = csvFile.ReadLine()) != null) {
if (line == "") // Skip empty line
continue;
csvData.Rows.Add(
ParseCSVLine(line) // Parse CSV Line
.OfType<Object>() // Convert it to Object List
.ToArray() // Convert it to Object Array, so that it
can be added to DataTable
); // Add Csv Record to Data Table
}
}
finally {
if (csvFile != null)
csvFile.Close();
}
return csvData;
}
private static List<String> ParseCSVLine(String line) {
var quoteStarted = false;
var values = new List<String>();
var marker = 0;
var currPos = 0;
var prevChar = '\0';
foreach (Char currChar in line) {
if (currChar == ',' && !quoteStarted) {
AddValue(line, marker, currPos - marker, values);
marker = currPos + 1;
quoteStarted = false;
}
else if (currChar == '\"')
quoteStarted = (prevChar == '\"' && !quoteStarted)
? true
: !quoteStarted;
currPos++;
prevChar = currChar;
}
AddValue(line, marker, currPos - marker, values);
return values;
}
private static void AddValue(String line, Int32 start, Int32 count,
List<String> values) {
var val = line.Substring(start, count);
if (val == "")
values.Add("");
else if (val[0] == '\"' && val[val.Length - 1] == '\"')
values.Add(val.Trim('\"'));
else
values.Add(val.Trim());
}
