Excel VBO takes time to read data if data is more than 12K, how you can try OLEDB VBO to read data from excel file without opening it or you can use below code to read data from closed excel file and also from csv file.
Paste this below code in global code and call this function in code stage and necessary DLLs
Public Function ExcelSheetRead(ByVal filepath as String,sheetname as string, distinct as string, textformat as boolean) As DataTable
Dim dt As DataTable = New DataTable()
Dim Con As OleDbConnection
if textformat = true then
Con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & filepath & ";Extended Properties=" + ChrW(34) & "Excel 12.0 Xml;HDR=No;IMEX=1;ImportMixedTypes=Text;" + ChrW(34) & ";")
else
Con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & filepath & ";Extended Properties=Excel 12.0;")
end if
Con.Open()
Try
Dim SQLQuery as string
if distinct = "Yes" then
SQLQuery = "Select Distinct * from [" & sheetname & "$]"
else
SQLQuery = "Select * from [" & sheetname & "$]"
end if
Dim oCon As OleDbCommand = New OleDbCommand(SQLQuery,Con)
Dim adp As OleDbDataAdapter = New OleDbDataAdapter(OCon)
adp.Fill(dt)
Catch e_ As Exception
End Try
Con.Close()
Return dt
End Function
