3 weeks ago - last edited 3 weeks ago
Hi All,
I have a requirement to write a large set of collection data to an SQL server database table. Currently I am looping collection and execute insert query one by one.
Is there any method to insert all the collection data as a single unit or bulk in Blue Prism?
Thanks in advance!
Answered! Go to Answer.
3 weeks ago
Well,
Follow these steps.
Go to colection manipulation add a new action name it [Apply Function UTC To Local on column - CUSTOM], then drag and drop a code stage and name it UTC To Local CUSTOM and publish it like this :
Open the code stage and provide these for input :
Do the same for output
In Code stage put this :
--------------------------------------------------------
--------------------------------------------------------
Like this :
Your action should look like this :
Dont forget to put the inputs in start stage and the output in end stage.
Save and go to process now.
Lets make a test.
I have these csv file see below :
When i import it inside Blue Prism i have this :
Notice Blue Prism make the conversion to UTC by default.
Now i want to use my CUSTOM CODE, see below the parameters :
Notice that i leave the timezone empty because its already set by Blue Prism but you can provide
some of these inputs for timezone id :
"Eastern Standard Time" , "Central Standard Time" , "Mountain Standard Time" , "Pacific Standard Time" , "Alaskan Standard Time" , "Hawaiian Standard Time", Central Europe Standard Time", "India Standard Time" and there is more 🙂
Try to leave it blank at the begining and if its not working for you try to put your time zone id.
Your process should look like this :
Now run and you will have the same wanted value see below :
If i answered your request please put best answer
3 weeks ago - last edited 3 weeks ago
Hi @DipinDevP
Actually with one of my customer i work with Sql server and what we do is the following:
We loop all the collection and we insert the request in a text data item [query].
How?
With a calculation stage. Something like [query]&"your new insert request" and we store it in query text data item.
Then we make the execution with the data item [query] that contains our Sql insert request (in one time)
Is it clear for you ?
3 weeks ago
Hi @DipinDevP
For SQL server there is a bulkl insert query you can run but it will insert from a file rather than from a collection. e.g. BULK INSERT Sales.Orders FROM '\\SystemX\DiskZ\Sales\data\orders.dat'
If its a collection you need to use then likely you need to go through this line by line and insert each one using a loop or alternatively you can do this in a code stage using a for each loop after although this would do essentially the same thing it might work a little faster.
Hope this helps
3 weeks ago
Thanks @Mohamad_747 @michaeloneilfor your suggestions!
I am trying both the approaches.
I am facing another issue now related to Time Zone, while using looping approach.
I am using Get CSV Text As Collection action (File-Utility Management object) to get the CSV data as collection, but Date Time column values are changing to UTC time zone even I tried with datatype Text in collection.
Example: CSV value - 05-11-2024 14:15:00
Output Collection value - 05-11-2024 08:45:00
Any suggestions to get the DateTime value as exactly in the CSV file?
3 weeks ago
Hi @DipinDevP
There is a lot of way to do it.
If you are looking for rapidity we coded a custom code that do this. But before using jt you need to ensure that there is no row without a date type in your column.
If you are confortable with this i can give you the code.
3 weeks ago
@Mohamad_747 There will be values always in DateTime column, which is mandatory to have value in it.
3 weeks ago - last edited 3 weeks ago
I mean value that are not a date time datatype for instance first row is ok '23/03/2022 09:12:10' and second row is (-----). The code will not work because of the (----)
You see what i mean ?
3 weeks ago - last edited 3 weeks ago
Yes. I understand. All the rows wiil have values in DateTime format in our case.
3 weeks ago
Well,
Follow these steps.
Go to colection manipulation add a new action name it [Apply Function UTC To Local on column - CUSTOM], then drag and drop a code stage and name it UTC To Local CUSTOM and publish it like this :
Open the code stage and provide these for input :
Do the same for output
In Code stage put this :
--------------------------------------------------------
--------------------------------------------------------
Like this :
Your action should look like this :
Dont forget to put the inputs in start stage and the output in end stage.
Save and go to process now.
Lets make a test.
I have these csv file see below :
When i import it inside Blue Prism i have this :
Notice Blue Prism make the conversion to UTC by default.
Now i want to use my CUSTOM CODE, see below the parameters :
Notice that i leave the timezone empty because its already set by Blue Prism but you can provide
some of these inputs for timezone id :
"Eastern Standard Time" , "Central Standard Time" , "Mountain Standard Time" , "Pacific Standard Time" , "Alaskan Standard Time" , "Hawaiian Standard Time", Central Europe Standard Time", "India Standard Time" and there is more 🙂
Try to leave it blank at the begining and if its not working for you try to put your time zone id.
Your process should look like this :
Now run and you will have the same wanted value see below :
If i answered your request please put best answer
3 weeks ago
@Mohamad_747 It worked for me!
Thank you very much!