DATA: IMPORT FROM EXCEL FILE TO MICROSOFT SQL SERVER

 Audience: This Blog was intended for 6months of experience with Microsoft SQL Server.

Importing Data to Microsoft SQL Server from Excel File:

Prerequisites:
  1. Excel File with data to be imported.
  2. SQL Server Edition: Any Edition (Developer Edition, Evaluation, Enterprise, Standard, Express)
  3. Tools Used: 
  • SQL Server Import and Export Wizard.
SQL Server Import and Export Data Wizard
  • SQL Server Integration Tools.
Note: To run successfully this example, SSIS (SQL Server Integration Services) to be installed in local computer.

Procedure on importing data:

  • Open a blank excel file 
  • Enter below data into file (this data will be added to table)
  • Change the name of the sheet to WizardWalkthrough (highlighted in red color in below screenshot)

Note: This sheet name will be the name of the table in database.
  • Open SQL Server Import and Export Data Wizard.

  • Select Data Source (here Excel File - WizardWalkthrough.xlsx) and click next

  • Select Destination (Microsoft OLE DB Driver for SQL Server)
Note: This only will work if MSSQL Server installed in local computer.

  • Click on properties in "Choose a Destination" Window (above screenshot)
Note: Properties will only see your database based MSSQL server installation.
  • Select Copy data from one or more tables or views option
  • Make destination database is created before hand in testing MSSQL Server.
  • Run the Package Immediately
  • After Running the package, all the data from Excel will be imported into destination database (dbo.WizardWalkthrough)

Comments

Post a Comment

Popular posts from this blog

DATA: IMPORT & EXPORT IN MICROSOFT SQL SERVER

  Audience: This Blog was intended for 6months of experience with Microsoft SQL Server. Tools for Import & Export Data from Microsoft SQL Server: There are 3 ways of data importing & exporting tools available in MSSQL Server. Transact-SQL Command - Line Tools Wizards Short introduction of each method: Transact-SQL: Is a procedural programming language and extension of standard SQL data manipulation statements developed by Microsoft. Which can be used for data import and export mechanisms. Command-Line tools: There are serval MSSQL CLI tools available for data manipulation using command prompt. CLI tools examples as below: BCP Utility SQLCMD Utility SQL Server Application Utility SQL Shell Cmdlets for PowerShell  So on.... These tools needs to be explicitly installed on different Operating Systems like Windows, MacOS and Linux. Wizards: Wizards are like GUI developed to ease of running rich scripts to provide access to SQL for developers and database administrators of a...
Read more

Microsoft SQL Server Structure

Image
TLDR: This article provides information about Microsoft SQL Server structures which includes information about components which were unified to create a workable database. Components of Microsoft SQL Server: SQL Server Instance  Database  Schema - owner of database objects like tables, views and stored procedures Tables, Views and Stored Procedures.
Read more