Export .iqy SharePoint file into SQL server DB table automatically

Can any one please provide any solution on the below requirement.

1. Once we export .iqy file from Sharepoint site and if any one update from sharepoint site.

2. I need current live data from .iqy file downloaded from sharepoint site with out refresh using C# code

3. I need that live data from .iqy file(downloaded from sharepoint site) and export to excel with out clicking on refersh button.

Once i downloaded .iqy file from sharepoint what ever data updated in the sharepoint i need that data automatically save into my MS SQL server database using c# code with out click on referesh button in .iqy file.

Currently i am doing manual process in the below steps:
1. Downloaded the copy .iqy file from sharepoint
2. open the excel sheet(.xls) and load that .iqy file in my excel

3. once loaded the data in the excel and clicked on the refresh all option in excel to get live data .

4.Next I am storing this excel data into my SQL server DB.

Pain area every time i am doing manually to click on refresh butto to get live data to excel and how to over come this manual process to automatally that .iqy file live data pull stored in excel /any other data source.

Please help us on this requirement.

Automating the process of updating data from an IQY file downloaded from SharePoint and then saving it to a SQL Server database without manually refreshing the data can be achieved through a combination of C# code and Excel automation. Here's a step-by-step guide:

Step 1: Download the IQY File from SharePoint You can use C# to programmatically download the IQY file from SharePoint. You may need to use SharePoint REST APIs or libraries like SharePoint CSOM (Client-Side Object Model) or PnP PowerShell for SharePoint to achieve this.

Step 2: Load IQY Data into Excel Use C# to automate Excel and load the IQY file into an Excel workbook. You can use the Microsoft.Office.Interop.Excel library for this purpose. Here's a simplified example:

using Excel = Microsoft.Office.Interop.Excel;
class Program {
  static void Main() {
    Excel.Application excelApp = new Excel.Application();
    Excel.Workbook workbook = excelApp.Workbooks.Open("path_to_iqy_file.iqy");
    // You may need to customize this part to work with your specific IQY file 
    // Close Excel without saving changes since we'll save it later 
    workbook.Close(false);
    excelApp.Quit();
  }
}

Step 3: Refresh Excel Data To refresh the data in Excel without clicking the refresh button manually, you can use the RefreshAll method in the Excel API:

workbook.RefreshAll();

Step 4: Extract Data from Excel After refreshing the data, you can use C# to extract the data from the Excel workbook. You can use the Microsoft.Office.Interop.Excel library or other libraries like EPPlus for Excel data manipulation.

Step 5: Store Data in SQL Server Once you've extracted the data from Excel, you can use C# code to insert or update the data in your SQL Server database. You'll need to establish a connection to your database using ADO.NET or Entity Framework, depending on your preferred method of data access.

Here's a simplified example using ADO.NET to insert data into SQL Server:

using System.Data;
using System.Data.SqlClient;
string connectionString = "your_connection_string";
using(SqlConnection connection = new SqlConnection(connectionString)) {
  connection.Open();
  // Perform SQL INSERT or UPDATE operations here with your extracted data 
  connection.Close();
}

Step 6: Automate the Process You can schedule this C# code to run at specific intervals using a task scheduler like Windows Task Scheduler or a job scheduler like Hangfire to automate the entire process. This way, you won't have to manually download the IQY file, refresh Excel, or update the database.

Remember to handle errors and edge cases in your code to ensure robustness and reliability in the automated process.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Manoj Tyagi3y ago
Automating the process of updating data from an IQY file downloaded from SharePoint and then saving it to a SQL Server database without manually refreshing the data can be achieved through a combination of C# code and Excel automation. Here's a step-by-step guide:

Step 1: Download the IQY File from SharePoint You can use C# to programmatically download the IQY file from SharePoint. You may need to use SharePoint REST APIs or libraries like SharePoint CSOM (Client-Side Object Model) or PnP PowerShell for SharePoint to achieve this.

Step 2: Load IQY Data into Excel Use C# to automate Excel and load the IQY file into an Excel workbook. You can use the Microsoft.Office.Interop.Excel library for this purpose. Here's a simplified example:
```
using Excel = Microsoft.Office.Interop.Excel;
class Program {
  static void Main() {
    Excel.Application excelApp = new Excel.Application();
    Excel.Workbook workbook = excelApp.Workbooks.Open("path_to_iqy_file.iqy");
    // You may need to customize this part to work with your specific IQY file 
    // Close Excel without saving changes since we'll save it later 
    workbook.Close(false);
    excelApp.Quit();
  }
}
```
Step 3: Refresh Excel Data To refresh the data in Excel without clicking the refresh button manually, you can use the RefreshAll method in the Excel API:
```
workbook.RefreshAll();
```
Step 4: Extract Data from Excel After refreshing the data, you can use C# to extract the data from the Excel workbook. You can use the Microsoft.Office.Interop.Excel library or other libraries like EPPlus for Excel data manipulation.

Step 5: Store Data in SQL Server Once you've extracted the data from Excel, you can use C# code to insert or update the data in your SQL Server database. You'll need to establish a connection to your database using ADO.NET or Entity Framework, depending on your preferred method of data access.

Here's a simplified example using ADO.NET to insert data into SQL Server:
```
using System.Data;
using System.Data.SqlClient;
string connectionString = "your_connection_string";
using(SqlConnection connection = new SqlConnection(connectionString)) {
  connection.Open();
  // Perform SQL INSERT or UPDATE operations here with your extracted data 
  connection.Close();
}
```
Step 6: Automate the Process You can schedule this C# code to run at specific intervals using a task scheduler like Windows Task Scheduler or a job scheduler like Hangfire to automate the entire process. This way, you won't have to manually download the IQY file, refresh Excel, or update the database.

Remember to handle errors and edge cases in your code to ensure robustness and reliability in the automated process.