1 / 12

Importing Data from a Spreadsheet

The Sister Cities Database Development Project. Importing Data from a Spreadsheet. If you came to this presentation via a web browser, right-click and choose “F u ll Screen” before proceeding. Click mouse or press space bar to continue.

raquel
Télécharger la présentation

Importing Data from a Spreadsheet

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Sister Cities Database Development Project Importing Data from a Spreadsheet If you came to this presentation via a web browser, right-click and choose “Full Screen” before proceeding. Click mouse or press space bar to continue. • This presentation was prepared by Professor Steve Ross, with the advice of other MIS Faculty, for use in MIS Classes at Western Washington University. Please contact Dr. Ross for permission to use in other settings..

  2. Introductory Slides If you are reading this slide, by this time you should have successfully … • Analyzed the original spreadsheet data, • Modified the data, and • Imported the data into a temporary table in your database. Now you are ready for the final steps • Design the final, permanent table • Create that table • Transfer the data to the final table • Verify the results. Original Data  Modified Data  “Import” Table  “Final” Table

  3. Creating the perfect home for your data • These slides introduce a basic approach to copying the imported data into a new table that will be the permanent home of the data. The table that was created by the Import Wizard will be considered a temporary table that can be deleted when the process is complete. • Building the perfect home for your data usually involves: • Creating a new table that meets all design specs. • Copying the data into the new (permanent) table.

  4. Estimate the size of each Field (1) Estimate the size required for each field to comfortably hold all data without being so large that it is wasteful. • Right-click import table name, • Choose Select Top 1000 Rows

  5. Estimate the size of each Field (2) Modify the SELECT query … • Remove “TOP 1000” • Add functions to determine maximum length of strings and maximum value of numbers: SELECT MAX(LEN([LastName])) AS [LastName] ,MAX(LEN([FirstName])) AS [FirstName] ,MAX(LEN([MidName])) AS [MidName] ,MAX([AnnualPay1000]) AS [AnnualPay1000] ,MAX(LEN([DepartmentName])) AS [DepartmentName] ,MAX(LEN([Rank])) AS [Rank] ,MAX([BudgetAuthorityIn1000]) AS [BudgetAuthorityIn1000] ,MAX([PersonalStaff]) AS [PersonalStaff] ,MAX(LEN([MailCode])) AS [MailCode] FROM [createdbexample].[dbo].[WorkersTab]

  6. Estimate the size of each Field (3)

  7. Create Final Table • We start with a clone of the import table • Right-click the import table name, choose Script Table as ►CREATE To ► New Query Editor Window • Modify the CREATE command • Change data types and field sizes as necessary – using your best analytical judgment. • Add primary key field and constraint • Execute the queryexample on following slide

  8. Note change in data type from string to numeric USE [createdbexample] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblWorker]( [WorkerID] [int] IDENTITY(1,1) NOT NULL, [LastName] [varchar](25) NULL, [FirstName] [varchar](15) NULL, [MidName] [varchar](15) NULL, [AnnualPay1000] [money] NULL, [DepartmentName] [varchar](20) NULL, [Rank] [varchar](15) NULL, [BudgetAuthorityIn1000] [money] NULL, [PersonalStaff] [int] NULL, [MailCode] [varchar](4) NULL, CONSTRAINT [PK_tblWorker] PRIMARY KEY ([WorkerID]) ) ON [PRIMARY] GO

  9. The Finished Table • Once the table is created, right-click table name and choose Design • Table name • Primary key • ID in name • Identity • Field names • Field sizes

  10. Populate Final Table • To insert data from the import table … • Right-click the final table name, choose Script Table as ►INSERT To ► New Query Editor Window • Replace the VALUES keyword and list with a SELECT … FROM command • Executeexample on following slide

  11. Script for inserting rows of data: (2) This part puts the data in the final table. (1) This part retreavesall the data in the import table (3) Notice the 1:1 correspondence of field names: • ([LastName][LastName] • ([FirstName][FirstName] INSERT INTO [createdbexample].[dbo].[tblWorker] ([LastName],[FirstName],[MidName] ,[AnnualPay1000],[DepartmentName] ,[Rank],[BudgetAuthorityIn1000] ,[PersonalStaff],[MailCode]) SELECT [LastName],[FirstName],[MidName] ,[AnnualPay1000],[DepartmentName] ,[Rank],[BudgetAuthorityIn1000] ,[PersonalStaff],[MailCode] FROM [createdbexample].[dbo].[WorkersTab] GO

  12. Verify the Results • Inspect the table (right-click table name, choose Select Top 1000 Rows) • Data in expected columns • Numeric data format, ensure no loss • Character data, check for truncation

More Related