Steps For Importing Excel Data into MBARI

Importing spreadsheet data into MBARI_Samples database

This process is for importing data from spreadsheets into the MBARI Samples Database. It is used when there are a large number of specimens to be entered and the record-by-record forms in MS Access would be cumbersome. The first step (step I, below) is to create the spreadsheet in MS Excel, the second step is to import the data to the Access table template (table MSSQL_IMPORT), and the third step is to run the stored procedure called "migrate_ import" to distribute the data to the appropriate tables in the MS SQL Server database (text of stored procedure). Steps II and III must be performed on Windows 95 or NT.

Create a MS Excel spreadsheet with column names that are the same as the field names in the MSSQL_IMPORT table.

The MSSQL_IMPORT table is found in the MS Access satellite database "sampleForms.mdb" under the Table tab. It is a template, a staging area for the data. Field names and formats of the template table must be carefully matched (spelling, data type, size) in the spreadsheet. Not all columns must appear in the spreadsheet. Those that do, however, need to be fully populated because the database defaults will not be applied. Note that the SeedConcept column names are not the same as the MS SQL Server database field names because they must be distributed to their own table. For each StartDTG, there must be a unique CollectionRefName and SampleRefName (all three are primary keys for the table).

Import Excel data into the MSSQL_IMPORT table in the MS SQL MBARI_Samples database using MS Access.

Open //cyclone.mbari.org/samplesDB/DataBase/sampleForms.mdb from within FrontPage.
Choose [File] -> [Get External Data] -> [Import] options
Set [File of types] to "Microsoft Excel (*.xls)"
Select the Excel datafile you wish to import and press [Import]
On first screen check "First row contains column headings" and press [Next>]
On second screen check "In an existing table", select "MSSQL_IMPORT" and press [Next>]
On third screen press [Finish]
Enter the UserName "samdba" and password to load table
Investigate all errors in error table. (An error table will be created in Access any time an error occurs, but table name may not show until table view is refreshed.) Delete all rows from MSSQL_IMPORT and delete error tables. Repeat from step 1 until all rows load successfully.

Move the data from the MSSQL_IMPORT table into the standard MBARI_Samples tables by executing the migrate_import stored procedure from inside ISQL_W (text of stored procedure)

Start ISQL_W [Start] -> [Programs] -> [Microsoft SQL Server 6.5] -> [ISQL_W]
Login in on server: godzilla, Login id: samdba, Password: *******, and press [Connect]
Type in "exec migrate_import" and press the green arrow (Execute Query) in upper right. The migrate_import does the following:

	Validate FacilityName
	Validate MaterialName
	Validate Equipment
	Validate PreservativeMethod
	Validate ContainerType
	Validate SubSampleMethod
	Validate Citation
	Get the ChiefScientist ID From Person
	Get the Collector ID From Person
	Get the Contact ID From Person
	Create the CollectionGroup Records (Retain CollectionGroupID) based on unique StartDTG, ChiefScientistID, and ShipName
	Create the CollectionEvent Records (Retain CollectionEventID) based on unique CollectionEventDTG and CollectionRefName
	Create the Sample Records (Retain SampleID) based on unique SampleRefName
	Create the SeedVIMSConcepts Records
	Create the Storage Location Records
	Create the HazMat Records
	Delete Records from MSSQL_IMPORT which loaded successfully

All rows that did not load correctly will be displayed and will remain in the MSSQL_IMPORT table. Use sampleForms.mdb (MS Access satellite database) to fix any rows in MSSQL_IMPORT.
Repeat steps 1 through 4 until all data loads correctly.

»Next: Temporary table design