Excel - define in which column to write data

#1

Hi all,

I have several excel files (one per ID) that looks like this:

ID: 2000 (2 different cells)
City: New York (2 different cells)
Address: High Street (2 different cells)
Number: 4 (2 different cells)

And another excel file that has the following headers:
ID CITY ADDRESS NUMBER

I added a for each file in folder loop and stored the values in the first excel.
Then, I added a filter to filter on the ID that is in excel 1 (2000 in this example).
Now, I want to fill in the columns in the second excel, based on the variables I stored from the first excel.
For some reason I am not able to find how…

Can anyone help? How do I select the column in the datatable for which I want to add the data, after I filtered on the ID?

Thanks!

#2

Hi @yannip

New Datatable = Datatable.Select("ColumnName = ‘StringValue’ ").CopyToDataTable
This expression is to filter the datatable and copied to another datatable.

Regards
JothyPrasanth M

#3

Hello @jothyprasanth.m

Thanks for your reply.
Imagine the “StringValue” is a variable called “ID” and the column name = ID NUMBER.
Should it work by using New Datatable = Datatable.Select(“ID NUMBER = ‘ID’”).CopyToDataTable or should I be using different syntaxes (+ or ’ for the ID) as it is a variable?

Currently I am getting an error:

Source: Assign
Message: Syntax error: Missing operand after ‘NUMBER’ operator.
Exception Type: SyntaxErrorException

Thanks in advance!

#4

Hi @jothyprasanth.m

In the meantime I got a bit further but have another error.
I have the following code:

dt = dt.Select("[ID NUMBER]=‘ID’").CopyToDataTable
ID is a variable

Error message:
BOT4 has thrown an exception

Source: Assign
Message: Cannot perform ‘=’ operation on System.Double and System.String.
Exception Type: EvaluateException

Can you please tell me what I’m doing wrong?

Thanks!

#5

Hi @yannip

Can you try this

dt = dt.Select("[ID NUMBER]=‘+ID.ToString()+’ ").CopyToDataTable

Regards
JothyPrasanth M

#6

Hi @jothyprasanth.m

Thanks again. However, I got another error now:

Source: Assign
Message: The expression contains undefined function call ID.ToString().
Exception Type: EvaluateException

Any other thoughts? Thanks!

#7

Hi @jothyprasanth.m,

I found the solution:

dt = dt.Select("[ID]=’"+ID+"’").CopyToDataTable()

Kind regards,
Yanni

#8

Ok Super

Regards
JothyPrasanth M