C# Adding rows to a DataTable inside a foreach loop

c# csv datatable dataset datarow

32,873

You need to declare datatable and add data columns only once outside foreach loop.

       //Prepare Datatable and Add All Columns Here
        dataTable = new DataTable();
        column = new DataColumn();
        column.DataType = System.Type.GetType("System.Int32");
        column.ColumnName = "Machine Number";
        column.ReadOnly = false;
        column.Unique = true;
        column.AutoIncrement = false;

   //Excel Input and Dex File Data Marriage
    foreach (string Line in ExcelLines)
    {
     //Add new row and assign values to columns, no need to add columns again and again in loop which will throw exception
     row = dataTable.NewRow();

     //Map all the values in the columns
     row["ColumnName"]= value;

     //At the end just add that row in datatable
     dataTable.Rows.Add(row );

      }

32,873

Author by

Mark Buckley

Updated on September 19, 2020

Comments

Mark Buckley over 3 years

I'm trying to add variables from a text file into a datatable to be converted into a CSV file but I keep getting this error: "A column named 'Machine Number' already belongs to this DataTable" Im not sure what to do, any help would be appreciated, Thanks :)

Code:

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

namespace Ispy
{
public partial class Form1 : Form
{
    private DataGridView dexRead;
    DataColumn column;
    DataTable Data = new DataTable("ISpy");
    DataSet dataSet = new DataSet();
    DataRow row;

    public Form1()
    {
        InitializeComponent();
        ReadFiles();
    }

    private void ReadFiles()
    {
        DataTable dataTable;



        //Location of Dex Files
        DirectoryInfo DexFiles = new DirectoryInfo(Properties.Settings.Default.Dex_File_Path);

        //List of file names in Dex File folder
        List<string> DexNames = new List<string>();

        //Location of Input File
        string ExcelFile = Properties.Settings.Default.Excel_File_Path;

        //Read Input File
        string[] ExcelLines = File.ReadAllLines(ExcelFile);

        //Add names of each file to a list
        foreach (FileInfo DexFile in DexFiles.GetFiles("*.dex"))
        {
            DexNames.Add(DexFile.Name);
        }

        //Excel Input and Dex File Data Marriage
        foreach (string Line in ExcelLines)
        {
            row = Data.NewRow();
            dataTable = new DataTable();

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.ColumnName = "Machine Number";
            column.ReadOnly = false;
            column.Unique = true;
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Customer";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.ColumnName = "MEI Total Vend Count";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.ColumnName = "DEX Total Vend Count";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.ColumnName = "Stock Sold";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.ColumnName = "Capacity";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.DateTime");
            column.ColumnName = "Next Scheduled Visit Date";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Scheduled Visit In:";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Scheduled Visit Day";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Next Visit Stock Prediction";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Route Number";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Route Driver Name";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Current Stock %";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.DateTime");
            column.ColumnName = "Date/Time of DEX";
            column.AutoIncrement = false;

            Data.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Telemetry Provider";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Days since last refill";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            //column = new DataColumn();
            //column.DataType = System.Type.GetType("System.String");
            //column.ColumnName = "Machine @40% Stock in";
            //column.AutoIncrement = false;

            //Data.Columns.Add(column);

            //column = new DataColumn();
            //column.DataType = System.Type.GetType("System.String");
            //column.ColumnName = "Machine @30% Stock in";
            //column.AutoIncrement = false;

            //Data.Columns.Add(column);

            //column = new DataColumn();
            //column.DataType = System.Type.GetType("System.String");
            //column.ColumnName = "Optimal Fill Date";
            //column.AutoIncrement = false;

            //Data.Columns.Add(column);

            //column = new DataColumn();
            //column.DataType = System.Type.GetType("System.String");
            //column.ColumnName = "Optimal Fill Date In:";
            //column.AutoIncrement = false;

            //Data.Columns.Add(column);

            //column = new DataColumn();
            //column.DataType = System.Type.GetType("System.String");
            //column.ColumnName = "Optimal Fill Day";
            //column.AutoIncrement = false;

            //Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Sector";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Products";
            column.AutoIncrement = false;

            Data.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Machine Type";
            column.AutoIncrement = false;

            Data.Columns.Add(column);


            string[] LineInfo = Line.Split(',');

            //Input File Variables    (.Trim('"') is to remove artifacts leftover from Input File)
            string MachineNumber = LineInfo[0].Trim('"');
            string MachineLocation = LineInfo[1].Trim('"');
            string TelemetryProvider = LineInfo[7].Trim('"');
            string Capacity = LineInfo[9].Trim('"');
            string MEIVendCount = LineInfo[10].Trim('"');
            string MEICashCount = LineInfo[11].Trim('"');
            string LastVisitDate = LineInfo[12].Trim('"');
            string MachinePHYSID = LineInfo[13].Trim('"');
            string NextScheduledVisit = LineInfo[14].Trim('"');
            string RouteName = LineInfo[16].Trim('"');
            string DriverName = LineInfo[17].Trim('"');
            string MachineModel = LineInfo[18].Trim('"');
            string MachineType = LineInfo[19].Trim('"');
            string MachineSector = LineInfo[20].Trim('"');
            string DEXVendCount = "";
            string DEXCashCount = "";
            string Difference = "";
            string NextScheduledVisitDays = "";
            string NextScheduledVisitDay = "";
            string NextVisitStockPrediction = "";
            string MachineStockSold = "";
            string DexNameDate = "";
            string DaysSinceLastFill = "";
            string MachineStockAt30In = "";
            string OptimalFillDate = "";
            string OptimalFillDay = "";



            //Read each Dex File and retrieve data
            foreach (string DexName in DexNames)
            {
                string[] DexNameData = DexName.Split('_', '.');
                int DexPHYSID = Int32.Parse(DexNameData[0]);
                string dexNameDate = DexNameData[1] + DexNameData[2];

                try
                {
                    //Marriage of Excel File Data and Dex File Data
                    if (Int32.Parse(MachinePHYSID) == DexPHYSID)
                    {
                        //Dex File Variable's
                        string MeterLine = "";

                        //Calculate location of each Dex File
                        string DexFilePath = DexFiles.ToString() + DexName;

                        //Read all of the Dex File's lines and add to an array
                        string[] DexLines = File.ReadAllLines(DexFilePath);

                        //Find Meter Read line and add to an array
                        foreach (string DexLine in DexLines)
                        {
                            MeterLine = Array.Find(DexLines,
                        element => element.StartsWith("VA1", StringComparison.Ordinal));
                        }

                        //Split data from Meter Read line
                        if (MeterLine != null)
                        {
                            string[] MeterReads = MeterLine.Split('*');
                            //Assign Dex values to Dex variables
                            DEXCashCount = MeterReads[1];
                            DEXVendCount = MeterReads[2];
                        }



                        DateTime creationDate = DateTime.ParseExact(dexNameDate, "yyyyMMddHHmmss", CultureInfo.InvariantCulture);
                        DateTime nextScheduledVisit = DateTime.ParseExact(NextScheduledVisit, "ddMMyy", CultureInfo.InvariantCulture);
                        TimeSpan scheduleDays = DateTime.Today - nextScheduledVisit;
                        TimeSpan LastVisitDays = DateTime.Today - DateTime.ParseExact(LastVisitDate, "ddMMyy", CultureInfo.InvariantCulture);

                        int Differential = Int32.Parse(DEXVendCount) - Int32.Parse(MEIVendCount);
                        int stockSold = Int32.Parse(Capacity) - Differential;
                        double percent = 0;
                        if (stockSold != 0)
                        {
                            percent = (double)(stockSold * 100) / Int32.Parse(Capacity);
                        }
                        else
                        {
                            percent = 0;
                        }


                        row["Machine Number"] = Int32.Parse(MachineNumber);
                        row["Customer"] = MachineLocation;
                        row["MEI Total Vend Count"] = Int32.Parse(MEIVendCount);
                        row["DEX Total Vend Count"] = Int32.Parse(DEXVendCount);
                        row["Stock Sold"] = Differential;
                        row["Capacity"] = Int32.Parse(Capacity);
                        row["Next Scheduled Visit Date"] = DateTime.ParseExact(NextScheduledVisit, "ddMMyy", CultureInfo.InvariantCulture);
                        row["Scheduled Visit In:"] = scheduleDays.Days.ToString() + " Days";
                        row["Scheduled Visit Day"] = nextScheduledVisit.DayOfWeek.ToString();
                        row["Next Visit Stock Prediction"] = "N/A";
                        row["Route Number"] = RouteName;
                        row["Route Driver Name"] = DriverName;
                        row["Current Stock %"] = percent.ToString() + " %";
                        row["Date/Time of DEX"] = creationDate;
                        row["Telemetry Provider"] = TelemetryProvider;
                        row["Days since last refill"] = LastVisitDays.Days.ToString() + " Days";
                        row["Sector"] = MachineSector;
                        row["Products"] = MachineModel;
                        row["Machine Type"] = MachineType;


                        dataTable.ImportRow(row);


                    }
                }
                catch(Exception e)
                {

                }
            }

        }
        dataSet.Tables.Add(Data);

        StringBuilder sb = new StringBuilder();

        IEnumerable<string> columnNames = Data.Columns.Cast<DataColumn>().
                                          Select(column1 => column.ColumnName);
        sb.AppendLine(string.Join(",", columnNames));

        foreach (DataRow row1 in Data.Rows)
        {
            IEnumerable<string> fields = row1.ItemArray.Select(field => field.ToString());
            sb.AppendLine(string.Join(",", fields));
        }

        File.WriteAllText(@"\\DC01\Dev\Dexr\Excel Files\Output\test.csv", sb.ToString());
    }

    private void Load_Properties()
    {
        string configFile = "config.cfg";
        string path = Path.Combine(Environment.CurrentDirectory, @"Data\", configFile);
    }
}

}

Stephen Wilson almost 7 years

You only need to add the columns once, then all the lines below it. You're adding the columns for every line.

Mark Buckley almost 7 years

Doing this gives me a csv file with just headers all marked machine type
Mark Buckley almost 7 years

Thanks, it was the Data.ImportRow that was messing with the rows, using Data.Rows.Add worked