Microsoft Excel is a very popular and most used program used in building spreadsheets and commonly used in storing data. You can not use Excel as a database but can easily export or import data from and to databases using MS SQL. So you can create a backup of your data base in Excel format and also you can use it as a readable format for the distribution of information. This is a very useful ability and using this ability, a database architect can merge data from multiple sources into one central location to conduct analysis.
To import from Excel to MS SQL, just follow the steps given below.
First step is to upload the Excel file to your server by using the file chooser on your web server control panel. And then select the Excel file from your computer’s local drive to upload.
Now you will have to create a SQL connection to the Excel file using the following code described below:
Set fso = Server.CreateObject(“Scripting.FileSystemObject”)
psFilePath = “file.xls”
sPath = server.MapPath(“\”)
psFilePath = fso.BuildPath(sPath,psFilePath)
Now the next step is to add Excel information to the SQL database using the code given below:
if update = “true” Then
set g_conn = open_db()
set objRegExp = New RegExp
objRegExp.Pattern = “,”
objRegExp.Global = tru
After importing the data from Excel to MS SQl, use the following SQL code as given below:
Set objRS = Nothing
Set objConn = Nothing
There are also some following methods described on Microsoft site:
Import vs. Append
To import Excel data into a new SQL server table by using the SELECT…INTO…FROM syntax. Now you can easily convert these statements to append queries by using the INSERT INTO…SELECT…FROM syntax. You can assign source and destination objects as shown in codes above.
Use DTS or SSIS
To import information from Excel to MS SQL, you can use the SQL Server Data Transformation Services (DTS). Open the wizard and select the Excel source tables but remember one thing that Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$), and that plain object names without the dollar sign represent Excel named ranges.
Use a Linked Server
You can also simplify queries; you just have to configure an Excel workbook as a linked server in SQL server.
Here when you are importing the data from Excel to SQL, try to concentrate on following points: some times, especially when you assign the Excel source data by using the table name instead of a SELECT query. Then the columns in the destination SQL Server table will be rearranged in alphabetical order.
When importing the data, if the Jet Provider determines that an Excel column contains mixed text and numeric data. Then the Jet provider will select the majority.
So from the above method you can import information from Excel to MS SQL. But you need to have knowledge of SQL queries and command to do it.