Convert CSV to datatable

Rui Ruivo

Hello

I am trying to convert manually a CSV file into a datatable for posterior analysis and filtering.

The company was doing it manually in Excel but I’d figure it can be done programmatically and increase productivity.

I did try do use some existing classes and nuggets without success (Errors I can’t fix or don’t understand).

Could someone please look at my code and help me figure how to convert manually a CSV with '|' as divider between cells to datatable and apply filtering please. Or Just point me in the right direction.

I am trying this: https://www.c-sharpcorner.com/forums/c-sharp-importing-csv-files-into-datatables

I like to figure things by myself but this is taking me forever.

Best regards

Rui

private void ConvertCSVtoDataTable(string filePath)
{
LogMessage("(<-o->)");
// Counts lines in file
long lineCount = 0;
int columnsCount = 0;
char divider = '|';
// FIRST LINE - Read first Line for Columns Count
string firstLine = File.ReadLines(filePath).First();
// FIRST LINE - COUNT columns for DataTable using DIVIDER = "|" from First Line
foreach (char c in firstLine) if (c == divider) columnsCount++;
// CREATE DATATABLE
DataTable dt = new DataTable();
dt.Clear();
dt.Columns.Add("a"); // Colonne 0
dt.Columns.Add("b"); // Colonne 0
dt.Columns.Add("c"); // Colonne 0
dt.Columns.Add("d"); // Colonne 0
dt.Columns.Add("e"); // Colonne 0
dt.Columns.Add("f"); // Colonne 0
dt.Columns.Add("g"); // Colonne 0
dt.Columns.Add("h"); // Colonne 0
dt.Columns.Add("i"); // Colonne 0
dt.Columns.Add("j"); // Colonne 0
dt.Columns.Add("k"); // Colonne 0
dt.Columns.Add("l"); // Colonne 0
dt.Columns.Add("m"); // Colonne 0
dt.Columns.Add("n"); // Colonne 0
dt.Columns.Add("o"); // Colonne 0
dt.Columns.Add("p"); // Colonne 0
dt.Columns.Add("q"); // Colonne 0
dt.Columns.Add("r"); // Colonne 0
dt.Columns.Add("s"); // Colonne 0
dt.Columns.Add("t"); // Colonne 0
dt.Columns.Add("u"); // Colonne 0
dt.Columns.Add("v"); // Colonne 0
dt.Columns.Add("w"); // Colonne 0
dt.Columns.Add("x"); // Colonne 0
dt.Columns.Add("y"); // Colonne 0
dt.Columns.Add("z"); // Colonne 0
// READ LINE BY LINE
using (StreamReader r = new StreamReader(filePath))
{
string line;
// Reads line by line from the file
while ((line = r.ReadLine()) != null)
{
while (line.IndexOf(divider) != -1) // -1 is returned if not found
{
// START DIVIDING IN BLOCKS FOR DATATABLE
// Get divider position
int endBlock = line.IndexOf(divider);
// Get first Block of text
String firstBlock = line.Substring(0, endBlock);
LogMessage("Line: " + lineCount + " - Block: " + firstBlock);
// Deletes first text block
line = line.Remove(0, firstBlock.Length + 1);
// MOVE TO NEXT CELL/BLOCK UNTIL END OF LINE
// REPEAT IN EVERY LINE
lineCount++;
}
}
}
}

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rui Ruivo7y ago
It appears to work with:
string[] rows = Regex.Split(sr.ReadLine(), "|");
Thank you
+1
Rui Ruivo7y ago
Added headers but now i have another error: "System.IndexOutOfRangeException"

Blocks at:
dr[i] = rows[i];
Any idea please?

My current file is like this.

A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z|
0004250327|000013|C470978|07/05/2019|9782751408441|3|V|14,12|8,47|42,36|25,41|16,95||801005|0002050306|OLIVIER NICOLAS|S593774|B|||||N|||07/05/2019|
0004250327|000022|C470978|07/05/2019|9782751408311|1|V|9,38|5,63|9,38|5,63|3,75||801005|0002050306|OLIVIER NICOLAS|S593787|B|||||N|||07/05/2019|
0004250327|000023|C470978|07/05/2019|9782751409752|1|V|12,23|7,34|12,23|7,34|4,89||801005|0002050306|OLIVIER NICOLAS|888082|B|||||N|||07/05/2019|
+1
Manoj Bhoir7y ago
Yes. Try by adding headers.
+1
Rui Ruivo7y ago
Manoj Bhoir
I am trying to use your code but getting this error: "System.Data.DuplicateNameException"

I think its because my csv file has no headers and any other repetition is taken into error... Should i try to add a header line??

Thank you for your fast response.

Rui
+2
Rui Ruivo7y ago
How to download Spire?
+1
Manoj Bhoir7y ago
Hi,
Try this.
public static DataTable ConvertCSVtoDataTable(string strFilePath)
{
            StreamReader sr = new StreamReader(strFilePath);
            string[] headers = sr.ReadLine().Split(',');
            DataTable dt = new DataTable();
            foreach (string header in headers)
            {
                dt.Columns.Add(header);
            }
            while (!sr.EndOfStream)
            {
                string[] rows = Regex.Split(sr.ReadLine(), ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
                DataRow dr = dt.NewRow();
                for (int i = 0; i < headers.Length; i++)
                {
                    dr[i] = rows[i];
                }
                dt.Rows.Add(dr);
            }
            return dt;
}
How to use :
static void Main(string[] args)
{
      string filepath = "C://ConvertedFile.csv";
      DataTable res = ConvertCSVtoDataTable(filepath);
}
+1
Rui Ruivo7y ago
I already tried whiteout success; I'll try again.

Thank you.
+1
Leon D7y ago
You can use Free Spire.XLS NuGet package to convert csv to datatable.
Workbook workbook = new Workbook();
//Load csv File
workbook.LoadFromFile(@"..\csvtodatatable.csv", "|");

Worksheet worksheet = workbook.Worksheets[0];

//Convert the first worksheet to datatable
DataTable dt = worksheet.ExportDataTable();
Visit this link for more information:
https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/CSV-to-Datatable-Convert-CSV-to-Datatable-with-C-VB.NET.html
+1

Convert CSV to datatable

Manoj Bhoir