How to Split Rows in a DataTable

You are given the assignment to create a CSV file report, and you say “No Problem, I just get the necessary data from SQL Server and create the CSV file”, however the columns have a max character length and Column13 has more characters than those allowed. How to handle this if you need to report all of the information in Column13?

The answer is to split that row into multiple rows. All columns will be duplicated (into the split rows) except for Column13 which will have the split information. Below I’ll show you how to accomplish this.






Column13 has a max character length of 45 and contains a list of names (which extends to more than 45 characters). You would want to split that list, however you don’t want to split a full name in half (first name in one row and last name in another row), you need to keep full names together.

The How To

First create three copies of the original DataTable. One will be your working copy, the second one will be used to store the removed rows and the third one the split rows.

C# Code

DataTable dtWorking = dtOriginal.Copy();
DataTable dtRemovedRows = dtOriginal.Clone();
DataTable dtSplittedRows = dtOriginal.Clone();
List<int> removeColsArr = new List<int>();

VB.Net Code

Dim dtWorking As DataTable = dtOriginal.Copy()
Dim dtRemovedRows As DataTable = dtOriginal.Clone()
Dim dtSplittedRows As DataTable = dtOriginal.Clone()
Dim removeColsArr As New List(Of Integer)

Then copy the rows that will be deleted from the working DataTable keeping their index number in an array, and later these will be the ones that will be split.

C# Code

int count = 0;
foreach (DataRow row in dtWorking.Rows) {
	if (row("DESCRIPTION3").ToString.ToArray.Length > 45) {
		removeColsArr.Add(count);
		dtRemovedRows.ImportRow(row);
	}

	count += 1;
}

VB.Net Code

Dim count As Integer = 0
For Each row As DataRow In dtWorking.Rows
    If row("DESCRIPTION3").ToString.ToArray.Length > 45 Then
        removeColsArr.Add(count)
        dtRemovedRows.ImportRow(row)
    End If

    count += 1
Next

Now remove the rows from the working DataTable

C# Code

int rCount = 0;
foreach (int row in removeColsArr) {
	if (rCount == 0) {
		dtWorking.Rows.RemoveAt(row);
	} else {
		dtWorking.Rows.RemoveAt(row - rCount);
	}

	rCount += 1;
}

VB.Net Code

Dim rCount As Integer = 0
For Each row As Integer In removeColsArr
    If rCount = 0 Then
        dtWorking.Rows.RemoveAt(row)
    Else
        dtWorking.Rows.RemoveAt(row - rCount)
    End If

    rCount += 1
Next

Now do all the heavy lifting. Working with the DataTable that holds the removed rows (from step two), the ones that will be split.

C# Code

string people = string.Empty;
int rowCount = 0;
foreach (DataRow row in dtRemovedRows.Rows) {
	int sum = 0;
	string[] arr = row("DESCRIPTION3").ToString.Split(",").ToArray;
	int counter = 0;

	foreach (void value_loopVariable in arr) {
		value = value_loopVariable;
		sum += (value.Length);

		if (sum <= 45) {
			people += arr(counter).Trim + ",";
		} else {
			dtSplittedRows.ImportRow(row);
			dtSplittedRows.Rows.Item(rowCount).Item("DESCRIPTION3") = people;

			sum = 0;

			people = arr(counter).Trim + ",";
			sum = people.Length;

			rowCount += 1;
		}

		counter += 1;
	}

	//Inserting the last people on the list
	dtSplittedRows.ImportRow(row);
	dtSplittedRows.Rows.Item(dtSplittedRows.Rows.Count - 1).Item("DESCRIPTION3") = people.Remove(people.Length - 1);

	rowCount += 1;
	people = string.Empty;
}

VB.Net Code

Dim people As String = String.Empty
Dim rowCount As Integer = 0
For Each row As DataRow In dtRemovedRows.Rows
    Dim sum As Integer = 0
    Dim arr() As String = row("DESCRIPTION3").ToString.Split(",").ToArray
    Dim counter As Integer = 0

    For Each value In arr
        sum += (value.Length)

        If sum <= 45 Then
            people += arr(counter).Trim + ","
        Else
            dtSplittedRows.ImportRow(row)
            dtSplittedRows.Rows.Item(rowCount).Item("DESCRIPTION3") = people

            sum = 0

            people = arr(counter).Trim + ","
            sum = people.Length

            rowCount += 1
        End If

        counter += 1
    Next

    'Inserting the last people on the list
    dtSplittedRows.ImportRow(row)
    dtSplittedRows.Rows.Item(dtSplittedRows.Rows.Count - 1).Item("DESCRIPTION3") = people.Remove(people.Length - 1)

    rowCount += 1
    people = String.Empty
Next

Lastly, merge the newly split rows into the working DataTable and return the Original DataTable wit the new rows

C# Code

dtWorking.Merge(dtSplittedRows);

dtOriginal.Clear();
dtOriginal = dtWorking.Copy();

VB.Net Code

dtWorking.Merge(dtSplittedRows)

dtOriginal.Clear()
dtOriginal = dtWorking.Copy()

Complete Code

C# Code

private void splittingRowsInDT(ref DataTable dtOriginal)
{
	//Splitting a row into multiple rows when 'DESCRIPTION3' column is > 45 characters
	DataTable dtWorking = dtOriginal.Copy();
	DataTable dtRemovedRows = dtOriginal.Clone();
	DataTable dtSplittedRows = dtOriginal.Clone();
	List<int> removeColsArr = new List<int>();

	//Copying rows with > 45 characters in 'DESCRIPTION3' column into a new table: dtRemovedRows 
	// and keep their row number
	int count = 0;
	foreach (DataRow row in dtWorking.Rows) {
		if (row("DESCRIPTION3").ToString.ToArray.Length > 45) {
			removeColsArr.Add(count);
			dtRemovedRows.ImportRow(row);
		}

		count += 1;
	}

	//Removing rows with > 45 characters in 'DESCRIPTION3' column
	int rCount = 0;
	foreach (int row in removeColsArr) {
		if (rCount == 0) {
			dtWorking.Rows.RemoveAt(row);
		} else {
			dtWorking.Rows.RemoveAt(row - rCount);
		}

		rCount += 1;
	}

	//Working with new dt that only holds the removed rows and splitting this into multiple rows
	// that will hold the 'DESCRIPTION3' column with < 45 characters
	string people = string.Empty;
	int rowCount = 0;
	foreach (DataRow row in dtRemovedRows.Rows) {
		int sum = 0;
		string[] arr = row("DESCRIPTION3").ToString.Split(",").ToArray;
		int counter = 0;

		foreach (void value_loopVariable in arr) {
			value = value_loopVariable;
			sum += (value.Length);

			if (sum <= 45) {
				people += arr(counter).Trim + ",";
			} else {
				dtSplittedRows.ImportRow(row);
				dtSplittedRows.Rows.Item(rowCount).Item("DESCRIPTION3") = people;

				sum = 0;

				people = arr(counter).Trim + ",";
				sum = people.Length;

				rowCount += 1;
			}

			counter += 1;
		}

		//Inserting the last people on the list
		dtSplittedRows.ImportRow(row);
		dtSplittedRows.Rows.Item(dtSplittedRows.Rows.Count - 1).Item("DESCRIPTION3") = people.Remove(people.Length - 1);

		rowCount += 1;
		people = string.Empty;
	}

	//Merging the new splitted rows into the working dt: dtWorking
	dtWorking.Merge(dtSplittedRows);

	//Transferring back to dtOriginal to continue processing in main method
	dtOriginal.Clear();
	dtOriginal = dtWorking.Copy();
}

VB.Net Code

Private Sub splittingRowsInDT(ByRef dtOriginal As DataTable)
    'Splitting a row into multiple rows when 'DESCRIPTION3' column is > 45 characters
    Dim dtWorking As DataTable = dtOriginal.Copy()
    Dim dtRemovedRows As DataTable = dtOriginal.Clone()
    Dim dtSplittedRows As DataTable = dtOriginal.Clone()
    Dim removeColsArr As New List(Of Integer)

    'Copying rows with > 45 characters in 'DESCRIPTION3' column into a new table: dtRemovedRows 
    ' and keep their row number
    Dim count As Integer = 0
    For Each row As DataRow In dtWorking.Rows
        If row("DESCRIPTION3").ToString.ToArray.Length > 45 Then
            removeColsArr.Add(count)
            dtRemovedRows.ImportRow(row)
        End If

        count += 1
    Next

    'Removing rows with > 45 characters in 'DESCRIPTION3' column
    Dim rCount As Integer = 0
    For Each row As Integer In removeColsArr
        If rCount = 0 Then
            dtWorking.Rows.RemoveAt(row)
        Else
            dtWorking.Rows.RemoveAt(row - rCount)
        End If

        rCount += 1
    Next

    'Working with new dt that only holds the removed rows and splitting this into multiple rows
    ' that will hold the 'DESCRIPTION3' column with < 45 characters
    Dim people As String = String.Empty
    Dim rowCount As Integer = 0
    For Each row As DataRow In dtRemovedRows.Rows
        Dim sum As Integer = 0
        Dim arr() As String = row("DESCRIPTION3").ToString.Split(",").ToArray
        Dim counter As Integer = 0

        For Each value In arr
            sum += (value.Length)

            If sum <= 45 Then
                people += arr(counter).Trim + ","
            Else
                dtSplittedRows.ImportRow(row)
                dtSplittedRows.Rows.Item(rowCount).Item("DESCRIPTION3") = people

                sum = 0

                people = arr(counter).Trim + ","
                sum = people.Length

                rowCount += 1
            End If

            counter += 1
        Next

        'Inserting the last people on the list
        dtSplittedRows.ImportRow(row)
        dtSplittedRows.Rows.Item(dtSplittedRows.Rows.Count - 1).Item("DESCRIPTION3") = people.Remove(people.Length - 1)

        rowCount += 1
        people = String.Empty
    Next

    'Merging the new splitted rows into the working dt: dtWorking
    dtWorking.Merge(dtSplittedRows)

    'Transferring back to dtOriginal to continue processing in main method
    dtOriginal.Clear()
    dtOriginal = dtWorking.Copy()
End Sub





Esau Silva
Software Engineer at Region One ESC
Microsoft Full Stack Application Developer
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed.
Share