OLEDB Issue

[faheemsd]

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
------------------------------

[michaeloneil]

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
------------------------------

[LakshmiNarayan3]

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
------------------------------

[faheemsd]

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...

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

[faheemsd]

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
------------------------------

[LakshmiNarayan3]

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
------------------------------

[PvD_SE]

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
------------------------------

[faheemsd]

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
------------------------------

[LakshmiNarayan3]

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
------------------------------

[faheemsd]

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 p...

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

[LakshmiNarayan3]

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
------------------------------