How to import SQL databases using Excel in ASP

[To] ASP how to import SQL database Excel

Http://www.webuc.net/moody/articles/7430.aspx

The so-called data transmission actually refers to the access between SQL Server and excel.

Why should we take this issue into consideration?

Due to historical reasons, many of the customer's previous data is stored in a text database, such as acess, Excel, and FoxPro. Currently, after system upgrades and database servers such as sqlserver and Oracle, you often need to access data in the text Database, which leads to such a requirement. A project that has been on a business trip for some time ago is faced with the following problem: data exchange between sqlserver and VFP.

To complete the title, it is very simple in sqlserver.

Generally, there are three methods: 1. DTS tool 2, BCP 3, and distributed query.

DTS does not need to be mentioned, because it is a graphical operation interface and is easy to use.

Here we will mainly talk about the following two examples: Query, add, delete, and modify:

The following nonsense will not say, directly in the form of T-SQL.

I. sqlserver and access

1. How to query access data:

Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ','; database = c: \ db2.mdb ', 'select * From serv_user ')

Or

Select * From OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = "C: \ db2.mdb"; user id = admin; Password =')... serv_user

2. Write Data from sqlserver to access:

Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ','; database = c: \ db2.mdb ', 'select * From accee table ')

Select * From sqlserver table
Or use BCP

Master .. xp_mongoshell 'bcp "serv-htjs.dbo.serv_user" out "C: \ db3.mdb"-C-Q-s "."-U "sa"-P "sa "'

The main difference above is: OpenRowSet requires the existence of MDB and tables. BCP will generate the MDB when it does not exist.

3. Write Data from access to sqlserver: with the above foundation, this is simple.

Insert into sqlserver table select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0 ','; database = c: \ db2.mdb ', 'select * From accee table ')

Or use BCP

Master .. xp_mongoshell 'bcp "serv-htjs.dbo.serv_user" in "C: \ db3.mdb"-C-Q-s "."-U "sa"-P "sa "'

4. delete access data:

Delete from OpenRowSet ('Microsoft. Jet. oledb.4.0 ','; database = c: \ db2.mdb ', 'select * From serv_user ')

Where lock = 0

5. Modify access data:

Update OpenRowSet ('Microsoft. Jet. oledb.4.0 ','; database = c: \ db2.mdb ', 'select * From serv_user ')

Set lock = 1

SQL Server and access are roughly the same.

Ii. sqlserver and Excel

1. query from Excel

Select * From OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; HDR = yes; database = c: \ book1.xls;', 'select * from [sheet1 $] ') Where C like' % F %'

Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Excel 5.0; HDR = yes; IMEX = 2; database = c: \ book1.xls ', [sheet1 $])

1) When HDR = yes, you can view the 1st rows of XLS as fields. For example, if HDR = No is set to 1st, an error will be reported during where.
2) [] and Meiyuan $ are required; otherwise, M $ may not recognize this account.

2. Modify execl

Update OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; HDR = yes; database = c: \ book1.xls;', 'select * from [sheet1 $] ')

Set a = 'erqua' where C like '% F %'

3. Import and Export

Insert into OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; HDR = yes; database = c: \ book1.xls;', 'select * from [sheet2 $] ') (ID, name)

Select ID, name from serv_user

Or BCP

Master .. xp_mongoshell 'bcp "serv-htjs.dbo.serv_user" out "C: \ book2.xls"-C-Q-s "."-U "sa"-P "sa "'

Import from Excel to sqlserver:

Select * into serv_user_bak
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; HDR = yes; database = c: \ book1.xls;', 'select * from [sheet1 $] ')

If the table serv_user_bak does not exist, create

For more information about BCP and distributed queries, check the help provided by sqlserver.
Data exchange between sqlserver and TXT files, HTML files, and VFP files is very easy ....

/* = ======= */

To import data from an Excel file to a SQL database, you can simply use the following statement:

/* ===================================================== ===================================== */
-- If the table to be imported already exists
Insert into Table select * from
OpenRowSet (''microsoft. Jet. oledb.4.0''
, ''Excel 5.0; HDR = yes; database = C: test.xls '', sheet1 $)

-- If you import data and generate a table
Select * into table from
OpenRowSet (''microsoft. Jet. oledb.4.0''
, ''Excel 5.0; HDR = yes; database = C: test.xls '', sheet1 $)

/* ===================================================== ===================================== */
-- If you export data from the SQL database to excel, And the Excel file already exists, and you have created a header based on the data to be received, you can simply use it:
Insert into OpenRowSet (''microsoft. Jet. oledb.4.0''
, ''Excel 5.0; HDR = yes; database = C: test.xls '', sheet1 $)
Select * from table

-- If the Excel file does not exist, you can use bcp to import Excel-like files. Note the case sensitivity:
-- Export tables
Exec master .. xp_mongoshell ''BCP database name. DBO. table name out "C: test.xls"/C-/s "server name"/u "username"-P "password "''

-- Export Query Information
Exec master .. xp_cmdshell ''bcp "select au_fname, au_lname from pubs .. authors order by au_lname "queryout" C: test.xls "/C-/s" server name "/u" username "-P" password "''

/* -- Description:
C: test.xls is the Excel file name for import/export.
Sheet1 $ is the worksheet name of the Excel file. You must add $ to use it normally.
--*/
-- As mentioned above, BCP is used to export Excel-like files, whose essence is text files,

-- To export a real Excel file, use the following method:

/* -- Data export Excel

Export the table data to excel, including the field name. The file is a real Excel file.
If the file does not exist, the file is automatically created.
If the table does not exist, the table is automatically created.
Only standard data types can be exported for the sake of universality.
-- Producer build 2003.10 --*/

/* -- Call example

P_exporttb @ tbname = ''region information'', @ Path = ''c: '', @ fname+'aa.xls''
--*/
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_exporttb] '') and objectproperty (ID, n' isprocedure'') = 1)

Drop procedure [DBO]. [p_exporttb]
Go

Create proc p_exporttb
@ Tbname sysname, -- Name of the table to be exported
@ Path nvarchar (1000), -- file storage directory
@ Fname nvarchar (250) = ''' -- file name. The default value is table name.
As
Declare @ err int, @ SRC nvarchar (255), @ DESC nvarchar (255), @ out int
Declare @ OBJ int, @ constr nvarchar (1000), @ SQL varchar (8000), @ fdlist varchar (8000)

-- Parameter detection
If isnull (@ fname, ''') = ''' set @fname=@tbname=''.xls''

-- Check whether the file already exists
If right (@ path, 1) <> ''' set @ Path = @ path + ''''
Create Table # Tb (a bit, B bit, C bit)
Set @ SQL = @ path + @ fname
Insert into # TB exec master .. xp_fileexist @ SQL

-- Database creation statement
Set @ SQL = @ path + @ fname
If exists (select 1 from # TB where a = 1)
Set @ constr = ''driver = {Microsoft Excel Driver (*. xls)}; DSN = '''; readonly = false''

+ ''; Create_db =" + ''; database ='' + @ SQL + ''"''

-- Connect to the database
Exec @ err = sp_oacreate ''adodb. connection', @ OBJ out
If @ err <> 0 goto lberr

Exec @ err = sp_oamethod @ OBJ, ''open'', null, @ constr
If @ err <> 0 goto lberr

/* -- If the existing table is overwritten, add the following statement.
-- Delete the table before creating the table./If the table exists
Select @ SQL = ''drop table [''+ @ tbname +'']''
Exec @ err = sp_oamethod @ OBJ, ''execute '', @ out, @ SQL
--*/

-- SQL statement used to create a table
Select @ SQL = ''', @ fdlist = ''''
Select @ fdlist = @ fdlist + '', ['' + A. Name + '']''
, @ SQL = @ SQL + '', ['' + A. Name + '']''
+ Case when B. Name in (''char '', ''nchar'', ''varchar '', ''nvarchar'') then
''Text (''+ Cast (case when a. length> 255 then 255 else A. Length End as varchar) + '')''

When B. Name in (''tynyint'', ''int'', ''bigint'', ''tinyint'') Then ''int''
When B. Name in (''smalldatetime'', ''datetime'') Then ''datetime''
When B. Name in (''money'', ''smallmoney'') Then ''money''
Else B. Name end
From syscolumns a left join policypes B on A. xtype = B. xusertype
Where B. name not in (''imag'', ''text'', ''uniqueidentifier'', ''SQL _ variant'', ''ntext '', ''varbinary '', ''binary'', ''timestamp '')

And object_id (@ tbname) = ID
Select @ SQL = ''create table [''+ @ tbname
+ ''] ('' + Substring (@ SQL,) + '')''
, @ Fdlist = substring (@ fdlist, 2,8000)
Exec @ err = sp_oamethod @ OBJ, ''execute '', @ out, @ SQL
If @ err <> 0 goto lberr

Exec @ err = sp_oadestroy @ OBJ

-- Import data
Set @ SQL = ''OpenRowSet (''' Microsoft. Jet. oledb.4.0 ''', ''' Excel 5.0; HDR = Yes
; Database = ''+ @ path + @ fname + ''', ['' + @ tbname + ''' $]) '''

Exec (''insert into ''+ @ SQL +'' (''+ @ fdlist +'') Select ''+ @ fdlist + ''from'' + @ tbname)

Return

Lberr:
Exec sp_oageterrorinfo 0, @ SRC out, @ DESC out
Lbexit:
Select cast (@ err as varbinary (4) as error code
, @ SRC as error source, @ DESC as error description
Select @ SQL, @ constr, @ fdlist
Go
-- The above is the table for export, and the following is the query statement for export.

/* -- Data export Excel

Export the queried data to excel, including the field name. The file is a real Excel file.
If the file does not exist, the file is automatically created.
If the table does not exist, the table is automatically created.
Only standard data types can be exported for the sake of universality.
-- Producer build 2004.10 --*/

/* -- Call example

P_exporttb @ sqlstr = ''select * From region information''
, @ Path = ''c: '',@fname}''aa.xls'', @ sheetname = ''region information''
--*/
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_exporttb] '') and objectproperty (ID, n' isprocedure'') = 1)

Drop procedure [DBO]. [p_exporttb]
Go

Create proc p_exporttb
@ Sqlstr varchar (8000), -- query statement. If order by is used in the query statement, add Top 100 percent.
@ Path nvarchar (1000), -- file storage directory
@ Fname nvarchar (250), -- file name
@ Sheetname varchar (250) = ''' -- Name of the worksheet to be created. The default value is the file name.
As
Declare @ err int, @ SRC nvarchar (255), @ DESC nvarchar (255), @ out int
Declare @ OBJ int, @ constr nvarchar (1000), @ SQL varchar (8000), @ fdlist varchar (8000)

-- Parameter detection
If isnull (@ fname, ''') = ''' set @fname=''temp.xls''
If isnull (@ sheetname, ''') = ''' set @ sheetname = Replace (@ fname ,''.'',''#'')

-- Check whether the file already exists
If right (@ path, 1) <> ''' set @ Path = @ path + ''''
Create Table # Tb (a bit, B bit, C bit)
Set @ SQL = @ path + @ fname
Insert into # TB exec master .. xp_fileexist @ SQL

-- Database creation statement
Set @ SQL = @ path + @ fname
If exists (select 1 from # TB where a = 1)
Set @ constr = ''driver = {Microsoft Excel Driver (*. xls)}; DSN = '''; readonly = false''

+ ''; Create_db =" + ''; database ='' + @ SQL + ''"''

-- Connect to the database
Exec @ err = sp_oacreate ''adodb. connection', @ OBJ out
If @ err <> 0 goto lberr

Exec @ err = sp_oamethod @ OBJ, ''open'', null, @ constr
If @ err <> 0 goto lberr

-- SQL statement used to create a table
Declare @ tbname sysname
Set @ tbname = ''# TMP _'' + convert (varchar (38), newid ())
Set @ SQL = ''select * into [''+ @ tbname +''] From (''+ @ sqlstr +'')''
Exec (@ SQL)

Select @ SQL = ''', @ fdlist = ''''
Select @ fdlist = @ fdlist + '', ['' + A. Name + '']''
, @ SQL = @ SQL + '', ['' + A. Name + '']''
+ Case when B. Name in (''char '', ''nchar'', ''varchar '', ''nvarchar'') then
''Text (''+ Cast (case when a. length> 255 then 255 else A. Length End as varchar) + '')''

When B. Name in (''tynyint'', ''int'', ''bigint'', ''tinyint'') Then ''int''
When B. Name in (''smalldatetime'', ''datetime'') Then ''datetime''
When B. Name in (''money'', ''smallmoney'') Then ''money''
Else B. Name end
From tempdb .. syscolumns a left join tempdb .. policypes B on A. xtype = B. xusertype
Where B. name not in (''imag'', ''text'', ''uniqueidentifier'', ''SQL _ variant'', ''ntext '', ''varbinary '', ''binary'', ''timestamp '')

And a. ID = (select ID from tempdb .. sysobjects where name = @ tbname)
Select @ SQL = ''create table [''+ @ sheetname
+ ''] ('' + Substring (@ SQL,) + '')''
, @ Fdlist = substring (@ fdlist, 2,8000)

Exec @ err = sp_oamethod @ OBJ, ''execute '', @ out, @ SQL
If @ err <> 0 goto lberr

Exec @ err = sp_oadestroy @ OBJ

-- Import data
Set @ SQL = ''OpenRowSet (''' Microsoft. Jet. oledb.4.0 ''', ''' Excel 5.0; HDR = Yes
; Database = ''+ @ path + @ fname + ''', ['' + @ sheetname + ''' $]) '''

Exec (''insert into ''+ @ SQL +'' (''+ @ fdlist + '') select ''+ @ fdlist +'' from [''+ @ tbname +''] '')

Set @ SQL = ''drop table [''+ @ tbname +'']''
Exec (@ SQL)
Return

Lberr:
Exec sp_oageterrorinfo 0, @ SRC out, @ DESC out
Lbexit:
Select cast (@ err as varbinary (4) as error code
, @ SRC as error source, @ DESC as error description
Select @ SQL, @ constr, @ fdlist
Go