|
|
| (5 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| {{InProgress}}{{DISPLAYTITLE:Synthesis Data Warehouse (SDW) Tutorial}}{{Template:API}}
| | #REDIRECT[[Transfer_Data_to_the_Synthesis_Data_Warehouse_(SDW)]] |
| <div style="border:1px solid #AAA; background:#f9f9f9; border-radius:10px; width:auto; margin:0 0 1em 1em; padding:1em; float:right;">
| |
| <nowiki><<</nowiki> [[APIQuickStartGuide|Back to Quick Start Guide]]
| |
| | |
| '''Synthesis Data Warehouse (SDW) Tutorial'''
| |
| *[[APISDWTutorialVBA|VBA]]
| |
| *VB.NET
| |
| </div>
| |
| | |
| This VB.NET tutorial will demonstrate one approach to read data from an Excel file and extract it for the SDW. Note that this tutorial is for demonstration purposes only; it does not include any exception handling and its performance may be slow. To complete this tutorial successfully, please use the [[APISDWTutorial|sample Excel data]] that was provided for this tutorial.
| |
| | |
| | |
| | |
| | |
| | |
| == Transfer Data from an Excel File to the SDW ==
| |
| | |
| 1. We'll begin with the <code>RawDataSet</code> class, which represents an SDW data collection. We'll then use the <code>ExtractedType</code> property to specify that the data collection is for use with Weibull++.
| |
| | |
| Create a new form and start with the following code to create an SDW data collection.
| |
| | |
| '''VB.NET'''
| |
|
| |
| {{APIComment|'Declare a new instance of the RawDataSet class.}}
| |
| {{APIPrefix|Dim}} DataCollection {{APIPrefix|As New}} RawDataSet
| |
|
| |
| {{APIComment|'Name the data collection "New Data Collection," and then specify that}}
| |
| {{APIComment|'it is for use with Weibull++.}}
| |
| DataCollection.ExtractedName = {{APIString|"New Data Collection"}}
| |
| DataCollection.ExtractedType = RawDataSetType.Weibull
| |
| | |
| 2. Next, we'll use the <code>RawData</code> class to represent a single row of data, and then use the <code>AddDataRow</code> method to add the row to the data collection.
| |
| | |
| The following code demonstrates one way to extract a row of data from the Excel file. First, the code opens a connection to the Excel file and specifies the active sheet (in this case, sheet #1). Then <code>For i = 2</code>, the code creates a new <code>RawData</code> object and sets its properties by using the values from row #2 of the Excel file. After the code finishes reading the row of data, it adds the object to the data collection. When the code reaches <code>Next i</code>, it increases <code>i</code> with 1 and jumps back to the <code>For</code> statement, which adds another new <code>RawData</code> object, sets its properties with the data from row #3 of the Excel sheet, and adds the row to the data collection. The loop continues until <code>i</code> reaches the specified number of rows (MaxRow). Finally, the code closes the connection to the Excel file.
| |
| | |
| '''VB.NET'''
| |
|
| |
| {{APIComment|'Declare a new instance of the RawData class.}}
| |
| {{APIPrefix|Dim}} Row {{APIPrefix|As New}} RawData
| |
|
| |
| {{APIComment|'Open the Excel file. The following code assumes that your}}
| |
| {{APIComment|'sample data is saved in the C drive.}}
| |
| {{APIPrefix|Dim}} Excel {{APIPrefix|As New}} Application
| |
| {{APIPrefix|Dim}} Workbook {{APIPrefix|As}} Workbook
| |
| {{APIPrefix|Dim}} Sheet {{APIPrefix|As}} Worksheet
| |
|
| |
| Workbook = Excel.Workbooks.Open({{APIString|"C:\SampleData.xlsx"}})
| |
| Sheet = Workbook.Sheets(1)
| |
|
| |
| {{APIComment|'Read each row of data in the Excel file.}}
| |
| {{APIPrefix|Dim}} i {{APIPrefix|As}} Integer, MaxRow {{APIPrefix|As}} Integer
| |
| MaxRow = 20
| |
|
| |
| {{APIPrefix|For}} i = 2 {{APIPrefix|To}} MaxRow
| |
| Row = {{APIPrefix|New}} RawData
| |
|
| |
| {{APIComment|'Set the properties for the current row of data.}}
| |
| Row.StateFS = sheet.Cells(i, 1).text
| |
| Row.StateTime = sheet.Cells(i, 2).value
| |
| Row.FailureMode = sheet.Cells(i, 3).text
| |
|
| |
| {{APIComment|'Add the current row to the data collection.}}
| |
| {{APIPrefix|Call}} DataCollection.AddDataRow(Row)
| |
| {{APIPrefix|Next}} i
| |
|
| |
| {{APIComment|'Close the Excel file.}}
| |
| Workbook.Close()
| |
| Excel.Quit()
| |
| | |
| 3. Now that the data from the Excel file have been copied to the data collection, the next step is to save the data collection to the Synthesis repository.
| |
| | |
| First, connect to the Synthesis repository and then use the <code>SaveRawDataSet</code> method to save the data collection to the repository.
| |
| | |
| '''VB.NET'''
| |
|
| |
| {{APIComment|'Connect to the Synthesis repository. The following code assumes that}}
| |
| {{APIComment|'a standard repository called "RSRepository1.rsr10" exists in the C: drive.}}
| |
| {{APIPrefix|Dim}} MyRepository {{APIPrefix|As New}} Repository
| |
| MyRepository.ConnectToRepository ({{APIString|"C:\RSRepository1.rsr10"}})
| |
|
| |
| {{APIComment|'Save the extracted data collection to the repository.}}
| |
| MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)
| |
| | |
| === Test the Code ===
| |
| Let's verify whether the code can import the data successfully into the SDW.
| |
| | |
| Below is the complete code list for this example. On the Debug menu, click '''Start''' to run the application. Then verify that the data were imported by launching Weibull++, opening the SDW ('''Home > Synthesis > Synthesis Data Warehouse''') and then selecting the new data collection. (You may need to display the StateTime, StateFS and FailureMode columns by right-clicking a column header and choosing '''Select Columns'''.)
| |
| | |
| '''VB.NET'''
| |
|
| |
| {{APIPrefix|Imports}} SynthesisAPI
| |
| {{APIPrefix|Imports}} Microsoft.Office.Interop.Excel
| |
|
| |
| {{APIPrefix|Public Class}} Form1
| |
| {{APIPrefix|Private Sub}} Button1_Click(sender {{APIPrefix|As}} System.Object, e {{APIPrefix|As}} System.EventArgs) {{APIPrefix|Handles}} Button1.Click
| |
|
| |
| {{APIComment|'Declare a new instance of the RawDataSet class.}}
| |
| {{APIPrefix|Dim}} DataCollection {{APIPrefix|As New}} RawDataSet
| |
|
| |
| {{APIComment|'Name the data collection "New Data Collection," and then specify that}}
| |
| {{APIComment|'it is for use with Weibull++.}}
| |
| DataCollection.ExtractedName = {{APIString|"New Data Collection"}}
| |
| DataCollection.ExtractedType = RawDataSetType.Weibull
| |
|
| |
| {{APIComment|'Declare a new instance of the RawData class.}}
| |
| {{APIPrefix|Dim}} Row {{APIPrefix|As New}} RawData
| |
|
| |
| {{APIComment|'Open the Excel file. The following code assumes that your}}
| |
| {{APIComment|'sample data is saved in the C drive.}}
| |
| {{APIPrefix|Dim}} Excel {{APIPrefix|As New}} Application
| |
| {{APIPrefix|Dim}} Workbook {{APIPrefix|As}} Workbook
| |
| {{APIPrefix|Dim}} Sheet {{APIPrefix|As}} Worksheet
| |
|
| |
| Workbook = Excel.Workbooks.Open({{APIString|"C:\SampleData.xlsx"}})
| |
| Sheet = Workbook.Sheets(1)
| |
|
| |
| {{APIComment|'Read each row of data in the Excel file.}}
| |
| {{APIPrefix|Dim}} i {{APIPrefix|As}} Integer, MaxRow {{APIPrefix|As}} Integer
| |
| MaxRow = 20
| |
|
| |
| {{APIPrefix|For}} i = 2 {{APIPrefix|To}} MaxRow
| |
| Row = {{APIPrefix|New}} RawData
| |
|
| |
| {{APIComment|'Set the properties for the current row of data.}}
| |
| Row.StateFS = sheet.Cells(i, 1).text
| |
| Row.StateTime = sheet.Cells(i, 2).value
| |
| Row.FailureMode = sheet.Cells(i, 3).text
| |
|
| |
| {{APIComment|'Add the current row to the data collection.}}
| |
| {{APIPrefix|Call}} DataCollection.AddDataRow(Row)
| |
| {{APIPrefix|Next}} i
| |
|
| |
| {{APIComment|'Close the Excel file.}}
| |
| Workbook.Close()
| |
| Excel.Quit()
| |
|
| |
| {{APIComment|'Connect to the Synthesis repository. The following code assumes that}}
| |
| {{APIComment|'a standard repository called "RSRepository1.rsr10" exists in the C: drive.}}
| |
| {{APIPrefix|Dim}} MyRepository {{APIPrefix|As New}} Repository
| |
| MyRepository.ConnectToRepository ({{APIString|"C:\RSRepository1.rsr10"}})
| |
|
| |
| {{APIComment|'Save the extracted data collection to the repository.}}
| |
| MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)
| |
|
| |
| {{APIPrefix|End Sub}}
| |
| {{APIPrefix|End Class}}
| |
| | |
| ===References===
| |
| To learn more, see the reference documentation for the classes and methods discussed in this section:
| |
| *[[RawDataSet Class]]
| |
| *[[RawData Class]]
| |
| *[[AddDataRow Method|RawDataSet.AddDataRow Method]]
| |
| *[[Repository.DataWarehouse.SaveRawDataSet|Repository.DataWarehouse.SaveRawDataSet Method]]
| |