Stored database variable to array and output in columns

Help Activities
, ,
1

Hi,

I have a variable called amount from the SQL Server. I tried to writeline the variable that stores the amount and there’s the data inside it. The question is :

  1. How to store it into string array variable?
    Example :

Variable Amount = 1,2,3,4,5

I want to store it to string so it will be strArray = {1,2,3,4,5}

  1. If I use add data row, will it output columns by columns instead rows by rows? See image below

image

Any explanation for this? Thanks :slight_smile:

2

hELLO @Rhys18

Does that mean you are getting the output from sql db as datatable and you need to convert that to string array? IS that the requirement?

3

Yes. I’m trying to output is as “Expected output” that can be seen in the image. Is it possible o do it?

4

So it will be like transposing the rows to columns ryt.

5

Yes. I also refer to every post in uipath forum and output is not as I expected. Have a look at this :

image

If it’s not do able with uipath robot, can it be done with SQL Query?

6

You can do this with uipath. Check the post which I shared earlier. It should work

7

It is not. This is the output I got

image

Every data is written to each column, and what I want is like this :

image

8

Or maybe you can share workflow example? Thanks :slight_smile:

9

Hi!

I see you tried out the solution I offered in the other thread.
Your problem might be a little different. If I understand correctly you have a datatable with 3 columns:

Customer ID, Customer Name, Value(s)

Now you have varying requirements in your original post and your answers throughout the thread. You need to rethink and state clearly, what you actually need.

Ok, easy enough! Use Assign like this:
YourStringArrayVariable = YourStringVariable.Split(","c)

If you use add data row, it will add new rows to your datatable and have your values in new rows instead of in new columns… Am I missing something?

Ok now, you want your array of values in one row to be in separate columns of one row. That is possible but not pretty! What do you do, if the first array has 2 values but the second array has 3 values?
The problems are:

  1. You need to create new columns only once (2 values in the array, 2 extra columns)
  2. However you cannot be sure how many values are in one array, so you need to check if you created a column already by keeping track of the index

Do you really need all the values in separate columns? It would be much easier to store the values in an array in the column values.

10

Hi,

To be honest I am literally really stuck right now and can’t figure a solution for this.

   Ok, easy enough! Use Assign like this:
   YourStringArrayVariable = YourStringVariable.Split(","c)

This is how I declare a string Array variable and put inside it, am I right?

If you use add data row, it will add new rows to your datatable and have your values
in new rows instead of in new columns… Am I missing something?

To make I was thinking of using add data row with string variable inside it. Will it do
the job that the array of string will be putted in columns or still in rows?

Ok now, you want your array of values in one row to be in separate columns of one
row. That is possible but not pretty! What do you do, if the first array has 2 values
but the second array has 3 values?

The problems are:

You need to create new columns only once (2 values in the array, 2 extra columns)
However you cannot be sure how many values are in one array, so you need to
check if you created a column already by keeping track of the index

Do you really need all the values in separate columns? It would be much easier to
store the values in an array in the column values.

Yes this is the main problem. The value can be vary (can be between 1-60). See excel below :

Excel.xlsx (10.3 KB)

The data is not processed from excel. It’s from SQL Query. The excel I provided just a visual, so when it writes, it will write as I expect

I’m really at the dead end.

11

Your answers are all over the place, I am even more confused now.

The formatting in your excel looks good, you have column names year/month and you have values in the rows. But what is the problem?! Also no clue what you mean by the Maturity Date stuff.

You need to rethink and state clearly, what you actually need.

Try to provide context of the process or focus on the input and desired output.

Input: DataTable from SQL query with x columns and y rows
Output: Printing the DataTable to Excel??? What do you need to do with the values next?

I have no time to help any more today. But if you elaborate your problem, others might be able to help.

12

I was trying to explain the probability ways it can be done because I don’t know how to do it. But I guess I will make it more simple then

Input : Data table from SQL Query contains customer_id, customer_name, value, maturity date
Output : I have to write the datatable to excel that is customer_id, customer_name, and value but the value is in columns and not rows

13

Hey buddy,

any news on your problem?

14

Hey man,

Well, my project is done without any error, and the output is as I expected… I just need to simplify it right now. Currently my robot is running for 1 hour and 15 minute.

Thanks for asking Mr. Lukas

1 Like