Import From Database

Data from Table with partitioning

Goal: Setup a workbook formula that pulls the data from the database (use the given connection string), partition the data by "PIN" with 3 maximum data per partition and sort the result by "S_No." in ascending form.

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."dummy"

Hint:

  1. Use the UI DataFromDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Select the Table radio button and and then search for the given table by clicking on TableName [horizontal dotted button].

  3. Enter the Max rows and select the "in Partition" and then select the desired column for "Partition by" and "Sort by".

Data from Table with filtering and sorting

Goal: Setup a workbook formula that pulls the data from the database (use the given connection string), filter the data by applying condition with "PIN" less than equal to 15, import distinct data with maximum limit upto 5 rows of data and sort the result by "S_No." in ascending form.

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."dummy"

Hint:

  1. Use the UI DataFromDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Select the Table radio button and and then search for the given table by clicking on TableName [horizontal dotted button].

  3. Select the Conditions tab and provide relevant filter condition.

  4. Select the Distinct tab and enter data in Max rows.

  5. Select the Sort the result tab and apply the given sorting condition.

Data using Manual query

Goal: Setup a workbook formula that pulls the data from the database (use the given connection string), extract all the data using manual query from the given table, the data should have "PIN" greater than 11 and sorted according to the "Name".

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."dummy"

Hint:

  1. Use the UI DataFromDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Select the Manual query radio button and write manual query to extract the relevant data.

  3. Pre-requisites for writing a query.