Add data to DataTable columns in multiple text files

Question

0.00/5 (No votes)

See more:

Hi. Please help me on this issue which needs to be solved urgently.

I'm trying to export data in multiple files to a single datatable. I was able to add data to the dataset but it doesn't provide the output that I need. I guess it should be an error in my loop and I tried in different ways but failed to get the correct output. Given below the output that I need:

HTML

column1   column2   column3   column4
-------   -------   -------   ------- 
val1      val2      val1      val2
val3      val4      val3      val4
val5      val6      val5      val6

this is the output that i receive:

HTML

column1   column2   column3   column4
-------   -------   -------   ------- 
val5      val6      val5      val6
val5      val6      val5      val6
val5      val6      val5      val6

given below is my coding:

C#

//first i create the columns
            for (int i = 1; i < files.Length; i++)
                {                    
                    DataColumn column1 = new DataColumn();
                    column1.DataType = Type.GetType("System.String");
                    column1.ColumnName = i + "0";
                    dt.Columns.Add(column1);

                    DataColumn column2 = new DataColumn();
                    column2.DataType = Type.GetType("System.String");
                    column2.ColumnName = i + "1";
                    dt.Columns.Add(column2);
                }
//then i create the rows
for (int i = 1; i < files.Length; i++)
                    {
                        string file2 = files[i];
                        using (System.IO.StreamReader file = new System.IO.StreamReader(file2))
                        {
                            string line;
                            while ((line = file.ReadLine()) != null)
                            {
                                //identify the lines containing "DISKXFER" 
                                //and split them one by one

                                if (line.Contains("DISKXFER"))
                                {
                                    string dataLine = line.ToString();
                                    string[] split = dataLine.Split(',');
                                    int result = split.Length;

                                    foreach (DataRow row in dt.Rows)
                                    {
                                        //Add the split 2 and 3 to the rows in columns
                                        row[i + "0"] = split[2];
                                        row[i + "1"] = split[3];
                                    }
                                }
                            }
                        }
                    }
                dataGridView1.DataSource = dt;
            }

Posted 13-May-13 20:56pm

hansikaat

Updated 13-May-13 21:10pm

Kenneth Haugland

v3

Add a Solution

2 solutions

Solution 1

Your problem is the foreach

C#

foreach (DataRow row in dt.Rows)
{
    //Add the split 2 and 3 to the rows in columns
    row[i + "0"] = split[2];
    row[i + "1"] = split[3];
}

This will alter each row, so you have in each row the last line of your last textfile
You need to loop the colums like

C#

DataRow row = // Create your new DataRow 
for (int j = 1; j < files.Length; j++)
{
    row[j + "0"] = split[2];
    row[j + "1"] = split[3];
}

Posted 13-May-13 21:34pm

Michael Ochmann

Updated 13-May-13 22:07pm

v2

Comments

hansikaat 14-May-13 3:48am

Hi Michael, i tried the above solution too. then i get an output like this:
column1 column2
------- -------
val1 val2
val3 val4
val5 val6
column3 column4
------- -------
val1 val2
val3 val4
val5 val6
PLease help

Michael Ochmann 14-May-13 4:06am

oh yes i made an error :)
we need to create the new row outside the loop

<pre lang="c#">
DataRow row = // Create your new DataRow
for (int j = 1; j < files.Length; j++)
{

row[j + "0"] = split[2];
row[j + "1"] = split[3];
}
</pre>

hansikaat 14-May-13 4:27am

when i use the above method, the coding should include "dt.Rows.Add(row);" to view the rows. Otherwise it doesn't show the output. but when i use that, it throws an error message called "the row already there".

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-05-13T22:23:00

[EDIT #1]
OK, i understand now. You need to add 2 columns for each file:

file1Col1 | file1Col2 | file2Col1 | file2Col2 | file3Col1 | file3Col2 ... fileNCol1 | fileNCol2

and load data into these columns.

Steps to do (logic):
1) create datatable object
2) in a loop -> add columns
3) in a loop -> open file
4) loop through the collection of lines
a) if first file then add rows and add values to the corresponding column (1, 2)
b) if next file then add values to the corresponding column (3,4... n) till line no. < datatable.rows.count else add new row and add values to the corresponding column
5) close file
6) go to the step 3 (open next file)
[/EDIT]

[EDIT #2]
I've made an example for you. I've tested it on 3 files with the structure like this:

line1val1,line1val2,file1line1val3,file1line1val4
line2val1,line2val2,file1line2val3,file1line2val4
line3val1,line3val2,file1line3val3,file1line3val4
line4val1,line4val2,file1line4val3,file1line4val4
line5val1,line5val2,file1line5val3,file1line5val4
line6val1,line6val2,file1line6val3,file1line6val4

In the second and the third file the structure differs as follow:

//3.
line1val1,line1val2,file2line1val3,file2line1val4
...
//4.
...
line6val1,line6val2,file3line6val3,file3line6val4

Here is my code (not well optimized):

C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.dataGridView1.DataSource = LoadData();

        }

        private DataTable  LoadData()
        {

            int k = 0;
            string[] files = { "E:\\4columns1.txt", "E:\\4columns2.txt", "E:\\4columns3.txt" };
            DataTable dt = new DataTable();
            DataRow dr = null;
            for (int i = 0; i <= files.GetUpperBound(0); i++)
            {
                string file = files[i];
                using (System.IO.StreamReader sr = new System.IO.StreamReader(file))
                {
                    string line = String.Empty ;
                    int lineno = 0;
                    while ((line = sr.ReadLine()) != null)
                    {
                        string[] split = line.Split(',');
                        int result = split.Length;
                        if (lineno == 0)
                        {
                            k += 1;
                            DataColumn dc = new DataColumn("File" + (i+1).ToString()  + "Col" + k.ToString(), Type.GetType("System.String"));
                            dt.Columns.Add(dc);
                            k += 1;
                            dc = new DataColumn("File" + (i+1).ToString() + "Col" + k.ToString(), Type.GetType("System.String"));
                            dt.Columns.Add(dc);
                            if (dt.Rows.Count <= lineno)
                            {
                                dr = dt.NewRow();
                                dt.Rows.Add(dr);
                            }
                            dr = dt.Rows[lineno];
                            dr[k-2] = split[2];
                            dr[k-1] = split[3];
                        }
                        else
                        {
                            if (dt.Rows.Count <= lineno)
                            {
                                dr = dt.NewRow();
                                dt.Rows.Add(dr);
                            }
                            dr = dt.Rows[lineno];
                            dr[k-2] = split[2];
                            dr[k-1] = split[3];
                            
                        }
                        lineno += 1;
                    }
                }
            }
            return dt;   
        }
    }
}

Test it and change it to your needs.

Note:
You need to increase lineno variable inside if(line.Contains("DISKXFER")) block code.

[/EDIT]