Using Microsoft Excel to Create an External Table
In some cases, it may not be convenient to create your external table directly in your SQL database. The user who has the information that needs to be input into the external table may not have access to your SQL database back-end, or you may simply not be comfortable working directly with SQL management tools. Using the SQL Server Import and Export Wizard, you can create your dynamic field table data in Microsoft Excel and then import it directly into the database as a new table.
There are two steps to this process. First, you will need to create a spreadsheet and populate it with the information that will be used by your dynamic fields. The defines the hierarchy of values that will then be used by the dynamic field. Second, you will need to import that spreadsheet into your SQL database as a new table. If you already have a spreadsheet containing the information you want to use in your dynamic fields, you can skip directly to step two.
Before you begin creating your spreadsheet, you should plan what information it will contain. See Planning Dynamic Fields for more information.
The following set of instructions explains how to create a spreadsheet in Microsoft Excel 2010, and then import it using Microsoft SQL Server Management Studio. It is intended as a model set of instructions only. If you are using another version of Excel or another database management system or database management tool, the instructions may differ.
Note: If you do not want to create your spreadsheet in Microsoft Excel, you can create it in another spreadsheet program and save it as a comma-separated list (,csv) file. You can then import the spreadsheet using the process below, but using the Flat File Source data source.
To create a Microsoft Excel spreadsheet to use with your dynamic fields
- Open Microsoft Excel and create a new workbook.
- Optional: Double-click the name of the sheet at the bottom of the screen and type a name for the sheet. This name can be automatically used to name the table.
- For each field you want to handle dynamically, type a column name representing that data that will be in the column.
- Fill in the values you want to make available in your dynamic fields.
Example: Once the user in the above example has created the columns, she can begin filling in the columns. The spreadsheet would look like the following example.
Click to expand. - Optional: If you want to create additional information that will be imported into in other tables, you can do so by creating additional sheets in the workbook.
- Open the File menu and select Save. Provide a save name and location for the spreadsheet.
- Close Microsoft Excel.
Example: A user who works at a pet store wants to create dynamic fields corresponding to types of animals, so that users can select first a category of animal (such as mammals, birds, reptiles or fish), then a type of animal (such as cats or dogs), and then a breed (such as German Shepherd or Labrador Retriever). She creates three columns: Category, Type and Breed
To import your Microsoft Excel spreadsheet into Microsoft SQL Server Management Studio 2008
- Launch the SQL Server Import and Export Wizard in one of the following ways:
- In the Start menu, expand the Microsoft SQL Server 2008 folder and select Import and Export Data (32-bit).
- In SQL Server Management Studio, expand the server tree and select your database, then right-click and point to Tasks. Select Import Data....
- In the Welcome step, click Next.
- In the Choose a Data Source step, open the Data source option and select Microsoft Excel.
- Click the Browse... button and browse to the Excel spreadsheet file you want to import.
- The wizard will attempt to automatically detect the version of your Excel spreadsheet in the Excel version option. If it is unable to detect the version correctly, you can select another version from the list.
- If your spreadsheet was created with the column names in the first row, select First row has column names. If the first row contains data instead of column names, clear this option. Click Next to continue.
- In the Choose a Destination step, open the Data source option and select SQL Server Native Client 10.0.
- In the Server name option, select the SQL Server that hosts your Laserfiche database. (Data for dynamic fields must be stored on the same server as your Laserfiche database.)
- In the Authentication option, select one of the following:
- Use Windows Authentication: Select this option to authenticate to the SQL Server using your current Windows credentials.
- Use SQL Server Authentication: Select this option to authenticate to the SQL Server using a username and password, and then provide your username and password.
- In the Database option, select a database. You can use the same database as is used with your Laserfiche repository, but you do not have to. Click Next to continue.
- In the Specify Table Copy or Query step, select one of the following:
- Copy data from one or more tables or views: This option will copy all the data from one or more of the sheets in your Excel spreadsheet workbook. You can select which sheet or sheets to import, but all the data in the specified sheets will be included. In most cases, you will want to select this option.
- Write a query to specify the data to transfer: This option allows you to write a SQL query to import only selected data from your spreadsheet. You should select this option if your spreadsheet contains more information than you want to import. If you select this option, you will need to be able to write a SQL query. See SQL Query Language on MSDN for more information.
- Click Next to continue.
- Make sure that Run immediately is selected, and click Finish to complete the import.
Note: If you want to create a new database to host your table, you can do so by clicking New...