Solved Need to write a values in column wise in excel document from the collection in blue prism

[jaganrpa]

Hi,

I have a text file (e.g. ID|Fname|Lname) using "|" symbol.

I read the text in file and splitted the text using "Split text" action and stored in the collection.

I need to write a text in column wise in excel.

But currently, it write the collection("Write collection" action) in ROWS not in COLUMNS.

Can you please help me out how to do this in blue prism.

Regards,
Jagan

 

[VJR]

Hi Jagan,

I'm not getting the whole picture of the issue. Are you able to give a visual representation?
I am guessing that the split collection is not correctly formed and hence the result on the excel is not showing up as expected.
Can you share how the collection looks like and how it is coming up on the Excel even with dummy data?

 

[jaganrpa]

Hi,

Thanks for the reply.

I have shown the input text file format in the "Sample text file.png"

My expectation: The data from the text file should read and write in the excel which is shown in the "Expected - Excel data.png"

Currently, I have read the data in text file and splitting the data. While i'm writing the data in excel it writes in COLUMN wise which is shown in "Currently Writing in Excel.png"

Please help me how to write the data in ROW wise.

 

[VJR]

Hi Jagan,

I see what you are saying but can you share i. how your collection looks like after splitting and ii. the excel with data in the screenshot 'currently-writing-in-excel-png'. I'm trying to see if it can be continued in a simpler way after what you have already done rather than rewriting something new.

 

[sna]

use read collection field from utility collection manipulation

In input give parameter from the collection which have split text ( in rows)

 

[jaganrpa]

I have attached the actions,Collections & output screenshot.

Please find the attached document.

 

[VJR]

Hi jaganrpa,

Since you are already getting the data into Excel you can do minor changes by pasting the same data as Transpose.

Post #2 here is required to be done for having an action to paste as transpose.
On doing so your data will be pasted as follows
View attachment 1542946868460.png

For the column headers you are already having them as the first row in the Lines collection.
Use the Split Text action and then use Set Cell value action of Ms Excel VBO to write to the cells A1, B1, and so on......or directly use the Write Collection action from the split collection returned after using Split Text.

While pasting as transpose you need to paste starting from the second row so that 1st row is left blank for the header.
Also keep in mind that you cannot copy the cells from a location and paste it into the same cell location. Excel doesn't allow this and throws an error. So you can dump the collection in a temporary sheet (clear it later) or in some other location of the same sheet and then paste as Transpose in cell A1 of your Original Sheet where you want the data to be.
Refer post #6 here for additional instructions needed.

For clearing the temporary data you can either put a blank value in those cells or Select and Delete the entire rows.

 

[jaganrpa]

Thank you all for your detailed explanation.
Let me check and revert.

 

[sivagelli]

How are you getting text file content in to Collection?

 

[jaganrpa]

Hi jaganrpa,

Since you are already getting the data into Excel you can do minor changes by pasting the same data as Transpose.

Post #2 here is required to be done for having an action to paste as transpose.
On doing so your data will be pasted as follows
View attachment 2593

For the column headers you are already having them as the first row in the Lines collection.
Use the Split Text action and then use Set Cell value action of Ms Excel VBO to write to the cells A1, B1, and so on......or directly use the Write Collection action from the split collection returned after using Split Text.

While pasting as transpose you need to paste starting from the second row so that 1st row is left blank for the header.
Also keep in mind that you cannot copy the cells from a location and paste it into the same cell location. Excel doesn't allow this and throws an error. So you can dump the collection in a temporary sheet (clear it later) or in some other location of the same sheet and then paste as Transpose in cell A1 of your Original Sheet where you want the data to be.
Refer post #6 here for additional instructions needed.

For clearing the temporary data you can either put a blank value in those cells or Select and Delete the entire rows.

I have followed as per your comments, im getting the compile error(Im new to VB).

i have attached the follow of the actions and the compile error.

Can you please help me out of this compile error.

I have attached the screenshot for reference.

 

[jaganrpa]

How are you getting text file content in to Collection?

In column wise

 

[VJR]

I have followed as per your comments, im getting the compile error(Im new to VB).

i have attached the follow of the actions and the compile error.

Can you please help me out of this compile error.

I have attached the screenshot for reference.

The error is due to a copy not made of the existing paste action and it should be made inside the Ms Excel VBO.
Below are the instructions from the other post-

In order to do that,
- Make a duplicate copy of the 'Paste' action of the Ms Excel VBO.
- Give the Action and the Code stage a new name like 'Paste as Transpose'
- Go to the Code stage and simply make the below change (the one shown in bold).

From this:
range.PasteSpecial(Paste:=Paste_Option, Operation:=-4142, SkipBlanks:=False, Transpose:=False)

To this:
range.PasteSpecial(Paste:=Paste_Option, Operation:=-4142, SkipBlanks:=False, Transpose:=True)


- Save the Object, Reset, Refresh and call this new action from the Process.

 

[jaganrpa]

The error is due to a copy not made of the existing paste action and it should be made inside the Ms Excel VBO.
Below are the instructions from the other post-

In order to do that,
- Make a duplicate copy of the 'Paste' action of the Ms Excel VBO.
- Give the Action and the Code stage a new name like 'Paste as Transpose'
- Go to the Code stage and simply make the below change (the one shown in bold).

From this:
range.PasteSpecial(Paste:=Paste_Option, Operation:=-4142, SkipBlanks:=False, Transpose:=False)

To this:
range.PasteSpecial(Paste:=Paste_Option, Operation:=-4142, SkipBlanks:=False, Transpose:=True)


- Save the Object, Reset, Refresh and call this new action from the Process.

Thanks a lot...

Its worked for me...