Put data from one table to other

Learn Academy Feedback
1

Dear Professionals,

I have simple case, but do not know how to solve it.

  1. I create DataTable with 4 columns “date”, “name”, “age”, “city” - dtEmployee. From the beginning rows are empty. (ok - it is easy)
  2. I have some excel file where are 3 columns…it can be ( “date”, “name”, “age”) or (“name”, “age”, “city”) or (“age”, “city”,“date”) - I know that inside of excel file will be 3 column with the same name but not with same place as in dtEmployee (TypeArgument: “system.datetime”, “string”,“int 32”,:string")
    I read this file: Excel application scope ->Read Range->Output data table and I have DataTable with Input tblReport (TypeArgument: “string”). (ok - it is easy too)
  3. I do not know what kind of activity I should use or how to write queue or…How I can transfer data from tblReport to dtEmployee…I understand that I need to check which columns has matches and I need to put “NULL” or it should be empty for columns which data does not exists in dtEmployee

Thank you!

2

@Aggi

ListA is the Colums of dtEmployee, List B is the Columns of Second Excel and let us take it as dtb

List A= (From p in dtEmployee.Columns.Cast(Of System.Data.DataColumn)
            Select Convert.ToString(p.ColumnName)).ToList

List B= (From p in dtb.Columns.Cast(Of System.Data.DataColumn)
            Select Convert.ToString(p.ColumnName)).ToList

Let us take stringA as the column name which is not there in dtb

stringA = ListA.Except(ListB).ToList(0).ToString

Now use Add DataColumn Activity to Add Column to dtb and and gave the column name as StringA

After that
DataTable dtc= dtb.DefaultView.ToTable(False,ListA.ToArray).CopyToDataTable

Now DataTable dtc will contain your Required OutPut.

Use Write Range Activity to Write in Excel

Regards,
Mahesh

3

Deare MAHESH1

I try to use your way and have some question…

stringA = dtEmployee.Except(dtb).ToList(0).ToString

I got “Except is not a member of member System.Data.DataTable”…can you explain me this part (stringA = dtEmployee.Except(dtb).ToList(0).ToString), please. And Except(dtb) you mean dtb like our second table with 3 columns?

Thank you!

5

@Aggi

Sorry I made one small mistake, Now I made one change please implement that.

Regards,
Mahesh

6

@MAHESH1

Thank you very much for help! I try to lern step by step and understand everything. Sorry if sometimes my questions can be naive…I am still have some mess in my mind and I need your explanation…

ListA = (FROM p in tblEmployee.Columns.Cast(Of System.Data.DataColumn) SELECT Convert.ToString(p.ColumnName)).ToString

ListB = (FROM p in tblReport.Columns.Cast(Of System.Data.DataColumn) SELECT Convert.ToString(p.ColumnName)).ToString

ListA or ListB - variables are “string”

  1. strA = ListA.Except(ListB).ToList(0).ToString

strA - variable is “string”

every thing is ok…When I run programm I got that Index was out of range…

mistake
mistake862×858 54.6 KB

7

@Aggi

Let us take if you are having an array of 3 elements, and if you try to access to 4th element you will get OutSide the bounds of Array Exceptions.

string() abc = new string() {“1”,“2”,“3”}

Now the maximum length of an array is 3.

If you try to Access string stra = abc(3).ToString then it will throw the exception, because the last index of the array is 2.

I think ListA.Except(ListB) is returning Empty List, And if you try to access ListA.Except(ListB).ToList(0) then it will throw an Exception.

Regards,
Mahesh