topic OLEDB Issue in Product Forum

OLEDB Issue

faheemsd — Thu, 30 Mar 2023 14:54:00 GMT

Hi ,

I'm using the SQL query in OLEDB to get the Excel data but when I read the excel data in Blue prism I'm getting the difference in Date and Time values and I'm not getting the exact Excel Data in the Blue Prism Collection

Please find the below excel screenshot and Blue prism collection Screenshot

Could you please provide the exact solution for this ?

Thank you.

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

RE: OLEDB Issue

PvD_SE — Fri, 31 Mar 2023 05:21:00 GMT

Hi Syed,

That looks strange indeed!

Some questions:
- What does the SQL you use to extract the XL data look like?
- What data type are the three columns in XL?
- Did you predefine the collection in BP? If so: what does it look like?

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

RE: OLEDB Issue

faheemsd — Fri, 31 Mar 2023 07:52:00 GMT

Hello Paul,

Thanks for your response..
What does the SQL you use to extract the XL data look like? - it's simple like Select * from [Sheet1$]
What data type are the three columns in XL? -

Transaction Date

Transaction Time

Settlement Date

the above columns are generated by default from the other source(other team sharing this excel to us via email) and send to us for the validations.
Did you predefine the collection in BP? If so: what does it look like? - No, Collection in Bp is not predefined.

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

RE: OLEDB Issue

Chankotra1998 — Fri, 31 Mar 2023 08:45:00 GMT

Hi Syed,
we can change the excel column data type first and then we can use OLEDB to read the data.
To get the exact Excel data in the Blue Prism collection, you may need to ensure that the data types and formats of the columns in the Excel sheet match the data types and formats of the columns in the Blue Prism collection. You may also need to adjust any formatting or conversion settings in the OLEDB query or Blue Prism to ensure that the data is transferred accurately.

------------------------------
Sahil Chankotra
------------------------------

RE: OLEDB Issue

PvD_SE — Fri, 31 Mar 2023 08:56:00 GMT

Hi Syed,

The problem seems to be that when your XL dates and time arrive in your collection, they have been converted to data type DateTime. This is probably a result of XL being stubborn - as is usual the case.

Time for an experiment:
Experiment 1:
- Change the data type of the dates and time in your XL to Text
- Format the values of the dates and time in the cells so they look like dates and time, but still are data type Text (eg write 3/29/2023 as '3/29/2023)
- Run the SQL again
- Check the results in the collection
If this works better, you might want to test again with adjusting the SQL to force dates to be interpreted as dates and times as times. For that, you cannot do a 'select * ...' but have to specify each column and assign the data type of each column so that they will land with a correct data type in your collection.

If the above went nowhere, or is too much work, try experiment 2.
Experiment 2:
- Convert the (original) XL to a CSV
- Run the SQL again
- Check the results in the collection
If this is better, you may want to consider changing the XL to CSV in the process or having the CSV delivered instead of the XL.

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

RE: OLEDB Issue

faheemsd — Fri, 31 Mar 2023 12:05:00 GMT

Hi Paul,

Just to give some background about the input file...we are actually receiving the input file in the CSV format and converting it to xlsx to work with OLEDB because I'm not sure about the connection string to with CSV file for OLEDB.

Could you please share the connection string for the CSV files to work with OLEDB and also please mention any dependencies that need to be installed in the server to work with the CSV files with the help of OLEDB.

Appreciate your support on this.

Thank you.

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

RE: OLEDB Issue

Mukeshh_k — Fri, 31 Mar 2023 12:33:00 GMT

Hi SYED FAHEEM- it should be pretty straight forward if you are facing difficulties with how data is being read or stored in your collection, below are the options for CSV and MS Excel and I am not sure if CSV files are being supported by OLEDB provider.

CSV File:

First Option: to read CSV files in Blue Prism is to use the "Get CSV as Collection" action from the "File Management" object. This action will allow you read all the CSV Data and later on you remove empty rows from collection if there are any by manipulating the collection data.

MS Excel File:
First option: would be to use a Pre-defined collection with all field kept as Text as it follows basic stuff which is If the collection field is defined as a text field, then the data returned by OLEDB will be stored in the collection field as text. The format of the text will depend on the data type of the corresponding column in the result set
If you dont wish to proceed with first option of defining a collection with predefined column format, use can use below method.

Second option :
Just add an action in one of your MS Excel extended VBO to format columns as Text - for your incoming Excel workbook sheet.

This Column Text formatting will ensure that all the data from collection are pasted in its in Simple Text without any auto formatting.

1) Open MS Excel Extended VBO - Add an Action - Format to Text

Add Handle, Workbook Name, SheetName, Cell Range as input and add below code to code stage as shown in the image
Note : Cell Range for your case should be - "A:E" or "A:F" depending on how many more columns you have in the excel sheet (You can see snippets below I have passed "AI:AI" as I needed one column to be formatted as text - if not sure of range then simply pass "A:Z" for the entire sheet and save workbook post formatting it as per defined range.

Dim ws As Object = GetWorksheet(handle, workbookname, worksheetname)

ws.Range(CellRange).NumberFormat = "@"

Once you have formatted the Columns - Continue with your OLEDB Operations and Data will be exactly how it is on the Excel sheet, let me know if you find any difficulties implementing this.

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS England, United Kingdom, GB
------------------------------

RE: OLEDB Issue

RamónRequena_L1 — Fri, 31 Mar 2023 12:39:00 GMT

If you are receiving the Input file as CSV, you can also query this directly with OLEDB. I don't recall the exact connection string, but something like this:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & [in_CSVPath] & "; Extended Properties=""text;HDR=NO;FORMAT=Delimited"""

In case it gives you any issues, try to play with the quation marks. The key function is adding 'Format= Delimited" to your connection string.

------------------------------
Ramón Requena López
RPA Developer
Magenta Telekom
------------------------------

RE: OLEDB Issue

michaeloneil — Sat, 01 Apr 2023 21:47:00 GMT

Hi @SYED FAHEEM

You can alter your script to get the values as date for the specified column e.g. SELECT TO_DATE (TRANSACTION DATE, 'DD.MON.YYYY') FROM [SHEET1$]
You might need to play around with this to get it working with the excel file but alternatively if the original file is csv but you are converting to xlsx just so you can use oledb to get the data then you could possibly just use Utility - Strings with action Get CSV as collection. It would simplify the process by removing unnecessary file conversion actions remove the need to use the oledb action.

Hope this helps 🙂

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: OLEDB Issue

Babjee24 — Sun, 02 Apr 2023 05:40:00 GMT

Hi Syed,

You can also use conversions available in BP for formatting at the run time. The difference in time is because BP will convert to UTC thinking the data source is in local timing, if you add/subtract(depending on the time zone data coming from) the UTC timing difference it should be giving you the correct value.

Hoping this will be giving you a solution without any formatting changes in your input.

------------------------------
Babjee Vangipurapu
Senior RPA Developer
Wonderbotz
India
------------------------------

RE: OLEDB Issue

michaeloneil — Sun, 02 Apr 2023 08:48:00 GMT

Hi @Babjee Vangipurapu

I didnt realise the utc issue also applied to excel extraction with OLEDB i've seen it with oracle databases but I've not used OLEDB much for excel. You can also handle the conversion of utc in the sql query rather than trying to amend the data after extraction. If you use the following statement for the columns your datetime exists in it will convert to utc so it gets the correct date.

SELECT * TO_CHAR(fROM_TZ( CAST(MYCOLUMNNAME AS TIMESTAMP ), 'UTC' ) AT LOCAL, 'YYYY-MM-DD') AS MYCOLUMNNAME FROM MYDATABASE

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: OLEDB Issue

plnarayana777 — Sun, 02 Apr 2023 12:14:00 GMT

if you are still facing issue

a simple work around is

use "Utility - File Management"::"Read All Text from File" to read data from csv file without any conversion
next "Utility - Strings"::"Get CSV As Collection"

simple you will get as it is without any changes in text format

Hope this helps

Regards

------------------------------
Lakshmi Narayana
------------------------------

RE: OLEDB Issue

faheemsd — Sun, 02 Apr 2023 13:57:00 GMT

Hi @Lakshmi Narayana

The above method giving the expected results , however what if the Collection data exceeds more than 30k rows or 40k rows or 1lack rows? Does the collection support in Blue Prism? I'm I able to write the data in the Excel File?

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

RE: OLEDB Issue

faheemsd — Sun, 02 Apr 2023 14:03:00 GMT

Hi @mukesh kumar

I have tried the code stage to convert the excel columns to Text , even converting the values to text I'm getting the result as shown in the below screenshot and as expected on from the excel

Before Converting to Text:

After Converting into text:
Could you please help me with the right approach?

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

RE: OLEDB Issue

plnarayana777 — Sun, 02 Apr 2023 14:18:00 GMT

Just to know about your requirement that as you are simply using the query to get all the data previously the same applies to this

when it comes to Blue Prism handling the data, again it depends on the memory allocation and not sure about large collections like 1lakh and above

Regards

------------------------------
Lakshmi Narayana
------------------------------

RE: OLEDB Issue

PvD_SE — Sun, 02 Apr 2023 14:55:00 GMT

Hi Syed,

Nice to hear you got it working now!

According to my experience, OleDB does not cause any memory problems while addressing XL files with the sizes you mention. This number of rows, combined with many columns would certainly be a problem if you were using the regular VBO for Excel. There seems to be however a new action on the VBO for Excel that will handle this better - or so I'm told.

Summarizing you could say that MS XL does have a well-deserved bad reputation for formatting and handling dates and times. Additionally, I'm not too happy about the way these are handled in BP either, sometimes BP is assuming an unsuitable timezone and format when in doubt.

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

RE: OLEDB Issue

faheemsd — Sun, 02 Apr 2023 16:25:00 GMT

Hi Paul,

My issue still not resolved, the above mentioned solution is not suitable for my requirement as I'm dealing with large volume of data

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

RE: OLEDB Issue

plnarayana777 — Mon, 03 Apr 2023 02:33:00 GMT

if your requirement is processing of all data then try below approach

>> getting the data in parts by using "Utility - File Management"::"Read Lines From File" defining a range of lines (10000 or more) at a time based on your requirement
>> Renaming the collection field to "Item Value"
>> combining the lines into text "Utility - Strings"::"Join Lines"
>> use "Utility - Strings"::"Get CSV As Collection"
>> iterate the the process until you get end of the file flag as true by increasing the range.

but if your requirement is getting specific data by filtering then you have to go for oledb defining the filter and later either you can use code stage to change the datetime values to local for whole collection or each value in loop with calculation stage by using datetime function again that depends on you requirement

so for better solution give the exact requirement so that somebody can provide you a better solution

Note : connection string of OLEDB for CSV file "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&[Path]&";Extended
Properties=""text;HDR=YES;FMT=Delimited"";" in the path you have provide until folder only end with "\"
query would be like "Select * From [Book1.csv];" where Book1 is file name

Regards

------------------------------
Lakshmi Narayana
------------------------------

RE: OLEDB Issue

faheemsd — Mon, 03 Apr 2023 07:37:00 GMT

Hi @Lakshmi Narayana

I'm trying with the OLEDB string but getting the below error
Internal : Could not execute code stage because exception thrown by code stage: Could not find installable ISAM.

Could you please help me how to resolve this error?

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

RE: OLEDB Issue

plnarayana777 — Mon, 03 Apr 2023 08:38:00 GMT

Hi

Can you please check the connect string if possible paste the old connection which was working for excel file and the new connection string you got error
path used for csv file in connection string

Regards

------------------------------
Lakshmi Narayana
------------------------------