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

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.

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

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