I wanted to read the data from the particular excel sheet using the protocol. And display that excel sheet data as a table in dataminer. It will be great help to me if someone guide me here.
The file is stored in local/SharePoint we need to dump that data to dataminer.
Hi,
the following question/answer provides some pointers on how to do read an Excel sheet:
https://community.dataminer.services/question/reading-writing-to-an-excel-file-in-net/answer/105057/
Is the exception thrown when opening the connection or when executing the query? (in case of the latter, can you check if the SELECT query is valid? I believe this should contain the name of the sheet and not the file name.
Alternatively, you could try to use the DocumentFormat.OpenXml NuGet package instead. An example of how to open and read the contents of an Excel file:
// filePath contains the full file path to the .xlsx file.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false, new OpenSettings()))
{
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;
Spreadsheet.SheetData sheetData = worksheet.GetFirstChild();
// Iterate through each row in the sheet
foreach (Spreadsheet.Row row in sheetData.Elements())
{
// process data here using e.g. row.ElementAt(0); to retrieve the first column
}
}
Hi
I have tried this, I am getting following issue which I was getting earlier also.
When I run this code with normal windows application it works perfectly. But issue is with dataminer protocol code. Is there anything specific access/permission to be granted from the Dataminer to access the files from local.
Exception:
System.IO.FileNotFoundException: Could not find document
File name: ‘C:Skyline DataMinerDocumentsSample.xlsx’
at DocumentFormat.OpenXml.Packaging.PackageLoader.OpenCore(String path, Boolean readWriteMode)
at QAction.ReadXlsx(String path, SLProtocol protocol)
at QAction.Run(SLProtocolExt protocol)
Hi,
The QAction should be able to open the file. To troubleshoot, can you test the following:
– verify that the file is present on the Agent
– in the QAction enumerate the files of the folder where te Excel file is located. If it is not included and you’re in a cluster setup, can you verify if the element that executes the QAction is on the same Agent as the Agent where the document is located.
– Test if the file exists via an Automation script
Thank you Pedro, it is working now.
Hi
You could also achieve this with an ad hoc data source. This effectively enables you to pull data in to DataMiner from anywhere (API, folder, excel sheet...).
You can find documentation on how to create an ad hoc data source over here: Configuring an ad hoc data source in a query | DataMiner Docs.
If you want to try it out with an example we also have some tutorials that you can try:
- Forwarding dummy data to the GQI | DataMiner Docs
- Building a GQI data source that fetches satellites | DataMiner Docs
Hope this information helps you forward.
When I am checking for the file, it is saying not found always from the local system folders. File.Exist().
Is there any specific reason why the driver is not able to find the file ?
Shiva_Excel_DataSource_Demo
Shiva_Excel_DataSource_Demo DataMiner driver
1.0.0.1
DMS-DRV-0001
skyline
deloitte
1.3.6.1.4.1.8813.2.01
1
Information Platform
snmpv2
auto
Added my protocol metadata here.
using (var conn = new OleDbConnection(connString))
{
conn.Open();
var query = “SELECT * FROM [” + Path.GetFileName(filename) + “]”;
using (var adapter = new OleDbDataAdapter(query, conn))
{
}
}
Exception:
System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Jet database engine could not find the object ‘DTCData.csv’. Make sure the object exists and that you spell its name and the path name correctly.