Import Excel Data by Integration
Posted by SunnyWhite on Friday, December 10, 2010
Data is very important when we create a useful Excel workbook. How do the data come from? Usually, every company has a system database which is used to store a great deal of data, such as finance, staff information and so on. Developers research so much on importing Excel data to make everything become easier. One of the ways to import Excel data is to use Integration service.
There is a good tool to import Excel data which is called SQL Server Integration Service, known as SSIS. Through Excel integration, data can be easy to be imported. If we want to import Excel Data, firstly, we can create a new database called Excel/Import by using the Management Studio. Then right-click on it and choose Tasks>Import Data on the moment the database is created. Then, the SQL Server Import and Export Data Wizard will be opened. A Welcome screen will be shown to you, which are helpful introductions about Wizard. Let’s come back to the opened screen. Choose a Data Source is presented. We select Microsoft Excel as Data Source and then select your file and the right version. If your sheet contains a header row, activate the First row has column names checkbox.
Next step, Choose a Destination, Microsoft OLE DB Provider for SQL Server. The correct database should be selected by default because we did a right-click on it to start the wizard. Select the first one of Select Source Tables and Views. Go for the ProductList$ Source and then we can preview the Data. Actually, destination can be changed by Edit Mapping. You can change the destination table’s schema from default setting. Then click OK to Review Data Type Mapping. Next, enter the Save and Run Package window and activate the Save SSIS Package. Choose Do not save sensitive data option. Save SSIS Package and then Complete the Wizard. Now, the new package can be executed.
Except SSIS, .Net Excel integration also can complete to import Excel data automatically. Also, there are some software which include this function. Developers may use the software to make their work to be finished efficiently.
There is a good tool to import Excel data which is called SQL Server Integration Service, known as SSIS. Through Excel integration, data can be easy to be imported. If we want to import Excel Data, firstly, we can create a new database called Excel/Import by using the Management Studio. Then right-click on it and choose Tasks>Import Data on the moment the database is created. Then, the SQL Server Import and Export Data Wizard will be opened. A Welcome screen will be shown to you, which are helpful introductions about Wizard. Let’s come back to the opened screen. Choose a Data Source is presented. We select Microsoft Excel as Data Source and then select your file and the right version. If your sheet contains a header row, activate the First row has column names checkbox.
Next step, Choose a Destination, Microsoft OLE DB Provider for SQL Server. The correct database should be selected by default because we did a right-click on it to start the wizard. Select the first one of Select Source Tables and Views. Go for the ProductList$ Source and then we can preview the Data. Actually, destination can be changed by Edit Mapping. You can change the destination table’s schema from default setting. Then click OK to Review Data Type Mapping. Next, enter the Save and Run Package window and activate the Save SSIS Package. Choose Do not save sensitive data option. Save SSIS Package and then Complete the Wizard. Now, the new package can be executed.
Except SSIS, .Net Excel integration also can complete to import Excel data automatically. Also, there are some software which include this function. Developers may use the software to make their work to be finished efficiently.