C# DataTable split a space separated field to create x new columns

Matt_Hirst · 4 Aug 2020 at 10:27

I have a DataTable, imported from a CSV file; and I need to split a reference field up

Reference

"Widget1 12x31x431x3256mm"
"Widget2 1265x1x31x56mm Back To Back"
"Widget1 432x3214x1x3mm"
"Widget1 4x32x15x3mm Back To Back"

The output I would like to see is

Code:

Reference                                        Product         Dim1     Dim2     Dim3     Dim4
Widget1 12x31x431x3256mm                         Widget1         12       31       431      3256
Widget2 1265x1x31x56mm Back To Back              Widget2-B2B     1265     1        31       56
Widget1 432x3214x1x3mm                           Widget1         432      3214     1        3
Widget1 432x3214x1x3mm Back To Back              Widget1-B2B     4        32       15       3

I initially set up a loop to iterate over each line and its fine for small files, but the files could be 5000-6000 lines long and a brief test of one at 2500 lines long and it takes around 5 mins to process.

Anyone offer advice on how to do this more efficiently?

Matt

Randomface74 · 4 Aug 2020 at 10:36

loop and split shouldn't take that long
check whether the time is taken by the loop or by loading the csv into the datatable
if it's the loop, post your loop code

Matt_Hirst · 4 Aug 2020 at 11:09

Hi,

I seperated the code out, so the load is on one button the processing of data on another so it is the processing that is the delay.

Theres a function which iterates over the datatable, which then calls a function to split the data based on the reference

Code:

for (int i = 0; i < dtData.Rows.Count; i++)
{
    processData()
}

This is the part of the code that processes for widgets, in this I was looking to see if I had a separate table with a product description in it I could pass the dimensions into the description - ultimately I need to separate the dimensions off and then pass them into a new text string, with my question I was trying to separate the problem into small steps. so appologies for any confusion.

Code:

if strRef.IndexOf("x_product")
{
}
else if (strRef.IndexOf("Widget") >= 0)
            {
                strProductDesc = "";

                string newstr = strRef;

                strProductDesc = GetproductDesc(strProductName);

                if (strRef.IndexOf("BACK") >= 0)
                {                                

                    newstr = strRef.Replace(strProductName, "");
                    newstr = newstr.Replace("mm", "");
                    newstr = newstr.Replace("BACK TO BACK", "");
                    newstr = newstr.Trim();

                }
                else
                {
                     newstr = strRef.Replace(strProductName, "");
                    newstr = newstr.Replace("mm", "");
                    newstr = newstr.Trim();
                }

                string[] strArray = newstr.Split('x');

                if(strArray.Length != 4)
                {
                    MessageBox.Show(strRef + "Error, not enough elements");
                    
                }
                else
                {

                    strProductDesc = strProductDesc.Replace("<<Dim1>>", strArray[0]);
                    strProductDesc = strProductDesc.Replace("<<Dim2>>", strArray[1]);
                    strProductDesc = strProductDesc.Replace("<<Dim3>>", strArray[2]);
                    strProductDesc = strProductDesc.Replace("<<Dim4>>", strArray[3]);
                }

            }

Matt_Hirst · 4 Aug 2020 at 11:20

Its currently processing around 5-6 lines a second, so hence was looking to try to split the reference field prior to any further processing.

Randomface74 · 4 Aug 2020 at 11:42

Does the processdata method take parameters? Make sure ur processing just the one row per row and not the whole thing per row.

Matt_Hirst · 4 Aug 2020 at 11:48

The Process data, processes data per row, it does different things depending on what the product is.

Currently it returns a string description of the product fully formatted with everything.

Matt_Hirst · 4 Aug 2020 at 12:27

Am going to strip it down into seperate sections and time each separate bit see where the bottleneck is.

Matt

Randomface74 · 4 Aug 2020 at 14:27

What does GetproductDesc() do? If it accesses a database cache the results.

Matt_Hirst · 4 Aug 2020 at 14:50

Thanks for the help.

Stripping everything back sorted the issue, I was calling the GetproductDesc for each line this wasn't needed as many lines were the same. Thus if I stored the description, if the new product was the same as the previous I could just reuse that description and not requery anything - also pre-sorting the datatable to be by description then reverting to the original order improved this no-end.

Matt

Slinky69 · 1 Sep 2020 at 22:54

Glad you got it sorted - 5/6 per second seemed painfully slow!