How to execute SQL QUERY on DATABALE without connecting to DB

Off-Topic Discussions
, , ,
1

I have an excel file on which i need to get an out which is obtained from the sql query :
SELECT TOP 25
cs-uri-stem as Url,
COUNT(*) As Hits
FROM ‘[LOGFILEPATH]’
GROUP BY cs-uri-stem
ORDER By Hits DESC

or is it possible to run this is a query on excel Datatable?

2

@Mahesh_Nag If you have an Excel File , We can Use Linq Query to perform Such an Operation. But for the Correct Linq Query, you’ll need to provide the Correct Column Names as present in the Excel File.

3

Thanks @supermanPunch, will start looking into it.

4

Try this Custom Component. It uses SQLite. You can fire queries in SQL format.

Regards,
Nithin

5

@supermanPunch: Any code sample for my given query above???

6

@Mahesh_Nag You can Try Following these Steps :

  1. Use Build Datatable Activity with two Columns that you need as Above, say GroupByDT

  2. Read the Excel File and get the Output Datatable as InputDT.

  3. In An Assign Activity, Use this Linq Query:
    GroupByDT = (From p In InputDT.AsEnumerable() Group By x= New With { Key.a =p.Item(“YourColumnName”)} Into Grp = Group Select GroupByDT.LoadDataRow (New Object() {grp(0)(1),grp.Count},False)).CopyToDataTable

  4. The grp(0)(1) is the First Column value, So Depending on Where your Column is , you’ll need to Change the Index value

  5. Then Write the GroupByDT variable to an Excel File using Write Range Activity.

This Should give you the Output you needed.