03-04-25 08:36 PM
How can I write data from a specific column in a collection and append it to a new Excel file in Blue Prism? Additionally, if the Excel file is created during the first run of the process, subsequent runs should append the column data to the existing file.
2 weeks ago - last edited 2 weeks ago
Hi @blazino17
Hope you are doing well,
First copy the columns of the main collection to a Temporary collection and follow below steps,
Delete the columns other than the "Required Column" from Temporary collection :
The output will be as follows :
<process name="__selection__Collection to Excel">
<subsheet subsheetid="c2bd6b04-e998-4527-be62-3cee03f70485" type="Normal" published="False">
<name>Collection to Excel</name>
<view>
<camerax>0</camerax>
<cameray>21</cameray>
<zoom version="2">1.25</zoom>
</view>
</subsheet>
<stage stageid="6d586406-461e-4ed7-85d1-30952c8aa9c3" name="Collection to Excel" type="SubSheetInfo">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<display x="-150" y="-105" w="150" h="90" />
</stage>
<stage stageid="81835a7e-a8b5-43b6-b57b-1498cc690b49" name="Start" type="Start">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<display x="15" y="-135" />
<onsuccess>9c407adc-80bd-473f-87bc-5f3af17e903a</onsuccess>
</stage>
<stage stageid="cb306ab7-2b6b-4485-aba4-4f05d806f48f" name="End" type="End">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<display x="15" y="225" />
</stage>
<stage stageid="14489c4b-121d-4c61-ae57-6068e33baa22" name="Temporary Collection" type="Collection">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<display x="-165" y="-15" w="120" h="30" />
<datatype>collection</datatype>
<private />
<alwaysinit />
<collectioninfo>
<field name="Required Column" type="text" />
<field name="Other Column 1" type="text" />
<field name="Other Column 2" type="text" />
</collectioninfo>
<initialvalue>
<row>
<field name="Required Column" type="text" value="adam" />
<field name="Other Column 1" type="text" value="1" />
<field name="Other Column 2" type="text" value="6" />
</row>
<row>
<field name="Required Column" type="text" value="sam" />
<field name="Other Column 1" type="text" value="2" />
<field name="Other Column 2" type="text" value="7" />
</row>
<row>
<field name="Required Column" type="text" value="mak" />
<field name="Other Column 1" type="text" value="3" />
<field name="Other Column 2" type="text" value="8" />
</row>
<row>
<field name="Required Column" type="text" value="cane" />
<field name="Other Column 1" type="text" value="4" />
<field name="Other Column 2" type="text" value="9" />
</row>
<row>
<field name="Required Column" type="text" value="sciro" />
<field name="Other Column 1" type="text" value="5" />
<field name="Other Column 2" type="text" value="0" />
</row>
</initialvalue>
</stage>
<stage stageid="e7642936-36ff-432e-8813-b6c83be14987" name="Utility - Collection Manipulation::Delete Column" type="Action">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<loginhibit onsuccess="true" />
<display x="15" y="105" w="90" h="30" />
<inputs>
<input type="collection" name="Input Collection" friendlyname="Input Collection" narrative="The collection from which you want to delete a column from" expr="[Temporary Collection]" />
<input type="text" name="Column Name" friendlyname="Column Name" narrative="The column number to delete" expr="[Collection Fields.Field Name]" />
</inputs>
<outputs>
<output type="collection" name="Output Collection" friendlyname="Output Collection" narrative="A collection with a column removed" stage="Temporary Collection" />
<output type="flag" name="Success" friendlyname="Success" stage="" />
<output type="text" name="Error Message" friendlyname="Error Message" stage="" />
</outputs>
<onsuccess>cace7e8c-9a32-45c6-8fd7-824fe4786cdf</onsuccess>
<resource object="Utility - Collection Manipulation" action="Delete Column" />
</stage>
<stage stageid="9c407adc-80bd-473f-87bc-5f3af17e903a" name="Utility - Collection Manipulation::Get Collection Fields" type="Action">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<loginhibit onsuccess="true" />
<display x="15" y="-75" w="90" h="30" />
<inputs>
<input type="collection" name="Input Collection" friendlyname="Input Collection" narrative="The collection to get the fields from." expr="[Temporary Collection]" />
</inputs>
<outputs>
<output type="collection" name="Collection Fields" friendlyname="Collection Fields" narrative="A collection containing the fields." stage="Collection Fields" />
</outputs>
<onsuccess>e4fe709a-d898-4e2f-b601-00f379614109</onsuccess>
<resource object="Utility - Collection Manipulation" action="Get Collection Fields" />
</stage>
<stage stageid="f33dc6eb-8751-4814-aaee-ad8d658f7e75" name="Collection Fields" type="Collection">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<display x="-165" y="15" w="120" h="30" />
<datatype>collection</datatype>
<private />
<alwaysinit />
<collectioninfo>
<field name="Field Name" type="text" />
<field name="Data Type" type="text" />
</collectioninfo>
</stage>
<stage stageid="b452368b-a77a-4fff-be50-a37921dcdbf4" name="Is Required Collection?" type="Decision">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<display x="15" y="45" w="90" h="30" />
<decision expression="[Collection Fields.Field Name]="Required Column"" />
<ontrue>9dd6a6f4-a5bf-452d-9a8b-effb433fe0aa</ontrue>
<onfalse>e7642936-36ff-432e-8813-b6c83be14987</onfalse>
</stage>
<stage stageid="e4fe709a-d898-4e2f-b601-00f379614109" name="Loop Collection Fields" type="LoopStart">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<loginhibit onsuccess="true" />
<display x="15" y="-15" w="90" h="30" />
<onsuccess>b452368b-a77a-4fff-be50-a37921dcdbf4</onsuccess>
<groupid>ead0c09a-f387-4dec-8a71-7329a4f00b04</groupid>
<looptype>ForEach</looptype>
<loopdata>Collection Fields</loopdata>
</stage>
<stage stageid="cace7e8c-9a32-45c6-8fd7-824fe4786cdf" name="Loop Collection Fields" type="LoopEnd">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<loginhibit onsuccess="true" />
<display x="15" y="165" w="90" h="30" />
<onsuccess>cb306ab7-2b6b-4485-aba4-4f05d806f48f</onsuccess>
<groupid>ead0c09a-f387-4dec-8a71-7329a4f00b04</groupid>
</stage>
<stage stageid="9dd6a6f4-a5bf-452d-9a8b-effb433fe0aa" name="Anchor3" type="Anchor">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<loginhibit onsuccess="true" />
<display x="120" y="45" w="10" h="10" />
<onsuccess>48d5a340-49bd-4c34-9e84-972b22ed57bd</onsuccess>
</stage>
<stage stageid="48d5a340-49bd-4c34-9e84-972b22ed57bd" name="Anchor3" type="Anchor">
<subsheetid>c2bd6b04-e998-4527-be62-3cee03f70485</subsheetid>
<loginhibit onsuccess="true" />
<display x="120" y="165" w="10" h="10" />
<onsuccess>cace7e8c-9a32-45c6-8fd7-824fe4786cdf</onsuccess>
</stage>
</process>
Best Regards,
Sayeed Bin Abdullah
2 weeks ago - last edited 2 weeks ago
Now to append data from the extracted collection to the column in the excel lets say "Excel Column" below steps can be followed:
Once you open excel workbook search for Excel Column
Go to next empty cell down in that column
Now write the data from collection in that column, mark Include Column Names flag as False to exclude the column name from collection
Best Regards,
Sayeed Bin Abdullah