By configuring a linked server in SQL server, you can retrieve data from many external data sources such as MS Access, Excel, or other database types.
To populate Enterprise PDM lists with data contained in an Excel workbook, you would have to first configure an Excel data source as a linked server in SQL Server, then query the linked server for desired information.
For more information on linked servers and distributed queries, please consult the following Microsoft Knowledge Base article: http://support.microsoft.com/kb/306397
Excel must be installed on the server that has the SQL server installed.
To configure an Excel data source as a linked server in SQL Server 2005:
- In SQL Server Management Studio, in the Object Explorer pane, expand Server Objects.
- Right-click Linked Server and select New Linked Server.
-
To create a new linked server, in the left pane, in the General property page:
-
Type any name for the linked server. You should choose a name similar to the name of the Excel worksheet for easier reference. In this example the name "MY_EXCEL_WORKBOOK_LINKED_SERVER" is used.
- Select Other data source.
- In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.
- For Product Name, type Excel for the name of the OLE DB data source.
- For Data Source, type the full path and file name of the Excel document, for example: C:\EngineeringData\My_Excel_Workbook.xls.
- For Provider String, type Excel 8.0 for an Excel 97, 2000, or 2002 workbook.
- Click OK.
- Query the Excel data source on the linked server.
For example, to retrieve the fields in the 'PartNumber' column stored in Sheet1 of your Excel file you can use the following code: SELECT PartNumber FROM MY_EXCEL_WORKBOOK_LINKED_SERVER...Sheet1$
You can use the above SQL Query in an Enterprise PDM list with a data type "From SQL Database" and set it to refresh every X minutes to retrieve the PartNumbers from the Excel file into Enterprise PDM.
You must enter a database name (any existing database on the server will do, for example the Northwind sample database or the file vault database), and the server and login credentials of the SQL server with the linked server.