Read DataTable Using Invoke Code

Help Studio
, , ,
1

I have excel sheet that contains huge amount of data in the sense that excel contains 7000+ rows and whenever I’m reading with the help of read renge it’s taking time.if anyone having invoke code for reading data from excel sheet and store it in the datatable so please help me

Thank you,

Prathamesh Hulavale

2

A lot of data is going to take time to load, it’s just that simple and there is no way around it. Are you using Excel Read Range or workbook Read Range? The workbook activity is generally faster.

3

Hi

Welcome to UiPath forum

There could be so many reasons behind

  1. Make sure u r not running bot in debug mode
    If u r running in debug mode it will usually execute slower
  2. Usually workbook READ RANGE activity is good compared to excel activity
    Try with that activity
  3. Even after using that if it’s bots working then try with this Component from UiPath
    https://marketplace.uipath.com/listings/read-extra-large-spreadsheets

Cheers @Prathamesh_Trimurti_Hulav

4

Hey @Prathamesh_Trimurti_Hulav , If your excel contains huge data then you can try using Database activities

  1. Build the connection string
  2. Run query → Select * From [Sheet1$]

So the above query will read all the data present in Sheet1

Hope it helps you !

5

Hello Vikas,
Thanks for Replying But I dont Know How to Build a Connection String If You have any Idea Or Related Videos Can You Please Share with Me ??

6

Hello Palaniyappan,
Thanks for Replaying !!
But I want Invoke Code Solution.

7

Try with this

Dim conStr As String = ""

conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
conStr = String.Format(conStr, Path)
Dim connExcel As New OleDbConnection(conStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
cmdExcel.Connection = connExcel
'Get the name of First Sheet
connExcel.Open()
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = ""
If dtExcelSchema.Rows.Count > 0 Then
    SheetName = dtExcelSchema.Rows(dtExcelSchema.Rows.Count - 1)("TABLE_NAME").ToString()
End If
connExcel.Close()
'Read Data from First Sheet
connExcel.Open()
cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
oda.SelectCommand = cmdExcel
oda.Fill(dt)
dt.TableName = SheetName.ToString().Replace("$", "")
connExcel.Close()
Return dt

Cheers @Prathamesh_Trimurti_Hulav

8

Hello @Palaniyappan I tried with connection string and its working I’m Posting whole solution. I tried the 12000 rows Data Reading and its Reading in just 6 sec.

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.