Split Data Table on basis of columns


#1

Hi,
I am following citrix automation where only limited options are there…There I need to Open an Excel and search for a value and some other conditions are there. So What I thought like i can copy all excel data into String and then I can split it…and then i can do the required comparision operations…
So can any one help me how to split a data based on column…


#2

Do you mean this way?

List Colval = new List(dt.Rows.Count);

foreach(DataRow row in dt.Rows)
ColVal.Add((String)row[“Col1”]);


#3

No Vinay…Here I am copying all data into String by Ctrl+a, Ctrl+c…and then I need to search for a value in it.else it is there or not …? If there how many times it is there…if multiple times there i need to get the next column values from string…
So, any logic?


#4

This is not the fastest way, but why not just paste it into Excel on workstation side and read that as a DataTable?
It might add a couple seconds compared to operating on the copied info directly, but it will be much much easier.

For actually splitting, you’d need to examine the structure and find proper delimiters. This can sometimes be tricky, depending on what is in the data (f.e. there can be tabs, line breaks etc. within the cells in excel).


#5

Correct… @andrzej.kniola… But Here Data Privacy of Client will not let us to copy data to local system… But still I will try with other Logic… Here I am trying by copying data by ctrl’s then splitting based on new line so that we will get each row data and I will get count based on condition …etc… Thanks for ur logic…


#6

Don’t won’t to spoil it, but if you have it in the shared clipboard, which you do in this case, you are copying it to the local system anyway. For someone who knows what he’s looking for, having it in the clipboard is as easy to access as it is to get to the files (or even easier, as it’s only one thing to monitor for and clipboard is one of the first things to keep an eye out for).
If the robot operates on the local system, data is there, one way or another - only difference is where and for how long. So if their policy explicitly states that no copy can be made to external network, that outrules the robots working over Citrix as well. If it states that no data can be saved outside their network, you’re good to go.
Now if it’s sensible for a policy like this is another thing and everyone can have their opinions, but if I’d be you, I’d double check (internally first, as there’s no point in making customers panic :wink: ).

Either way it’s a thing to keep in mind wrt security - it’s not easy :wink:


#7

Very Good explanation @andrzej…But with out using Ctrl keys no body can complete the work…As I have mentioned saving to local system they will not allow… For that only I am just Copying Data into Clipboard… Anyway I will discuss with them once… we can see…:slight_smile:


#8

Fyi…I used something similar to paste the CSV to datagrid , it might be helpful to you

 private void dgBulk_KeyDown(object sender, KeyEventArgs e)
        { 
            char _delimit;
            dt = new DataTable();
            ds = new DataSet();
            dt.Rows.Clear();
            if (e.Control && e.KeyCode == Keys.V)
            {

                if (rbComma.Checked)
                {
                    _delimit = char.Parse(",");
                }
                else if (rbTab.Checked)
                {
                    _delimit = char.Parse("\t");
                }
                else
                {
                    MessageBox.Show("Choose a Delimiter.");
                    return;
                }
                DataObject o = (DataObject)Clipboard.GetDataObject();
                if (o.GetDataPresent(DataFormats.Text))
                {
                    if (dt.Rows.Count > 0)
                        dt.Rows.Clear();

                    if (dt.Rows.Count > 0)
                        dt.Columns.Clear();

                    bool columnsAdded = false;
                    string[] pastedRows = Regex.Split(o.GetData(DataFormats.Text).ToString().TrimEnd("\r\n".ToCharArray()), "\r\n");
                    int j = 0;

                    dt.Columns.Add("NAME", typeof(string));
                    dt.Columns.Add("AGE", typeof(string));
                    foreach (string pastedRow in pastedRows)
                    {
                        string[] pastedRowCells = pastedRow.Split(new char[] { _delimit });

                        
                       RowCells[0].ToUpper().Trim() != "NAME")
                        {
                            dt.Rows.Add(pastedRowCells[0].ToUpper().Trim(), pastedRowCells[1].ToUpper().Trim());
                        }
                      
                        j++;
                    }

                    ds.Tables.Add(dt);
                    dgBulk.AutoGenerateColumns = true;
                    dgBulk.DataSource = ds.Tables[0];
                    btnSubmit.Enabled = true;
                }
            }
            else
            {
                //MessageBox.Show("Press Ctrl+V to paste data.");
            }
        }

#9

Wouldn’t this make Columns.Clear() never fire?
As far as I can tell it doesn’t make a difference anyway, as you’re declaring a new DT every time, but it looks like a bug to me - probably second if was supposed to be dt.Columns.Count > 0 ?


#10

Good catch @andrzej.kniola. Yes it is dt.Columns.Count >0, i digged it from some old code.


#11

Help me on divide the Datatable to Separate datatables for every 1000 rows one table headers should be fixed.
please help me on these