SQL Server and Excel data cross-guide

Source: Internet
Author: User
Tags datetime goto table name

Basic ways to import/export Excel from SQL Server

/*=================== Import/Export Excel basic methods ===================*/

From the Excel file, import the data into the SQL database, very simply, using the following statement directly:

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

--If the table that accepts the data import 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 to Excel from an SQL database, if the Excel file already exists and you have created a header for the data you want to receive, you can simply use:

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 also use BCP to guide the files of class Excel, and note the case:

--Case of export table

EXEC Master.. xp_cmdshell ' bcp database name. dbo. Table name out "C:test.xls"/C-/s "server name"/u "username"-P ""

--Case of export query

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 name of the worksheet for the Excel file, which will normally be added with $ to work properly.

--*/

As mentioned above, the class Excel file is exported in bcp, which is essentially a text file,

--To export a real Excel file. Use the following method

/*--Data Export Excel

Export data from tables to Excel, including field names, files as real Excel files

, if the file does not exist, the file will be created automatically

, the table is created automatically if the table does not exist

Only export standard data types are supported based on commonality considerations

--Jiangjian 2003.10--*/

/*--Call Example

P_EXPORTTB @tbname = ' Area 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) = '--filename, default to 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 if 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)};D sn= '; Readonly=false '

+'; create_db= "+ ';D atabase= ' + @sql + '" '

--Connecting 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 you overwrite a table that already exists, add the following statement

--Delete table before creation/if present

Select @sql = ' drop table [' + @tbname + '] '

EXEC @err =sp_oamethod @obj, ' execute ', @out out, @sql

--*/

--Create a table 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 if 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 systypes B on A.xtype=b.xusertype

where B.name not in (' image ', ' text ', ' uniqueidentifier ', ' sql_variant ', ' ntext ', ' varbinary ', ' binary ', ' timestamp ')

and object_id (@tbname) =id

Select @sql = ' CREATE TABLE [' + @tbname

+ '] (' +substring (@sql, 2,8000) + ') '

, @fdlist =substring (@fdlist, 2,8000)

EXEC @err =sp_oamethod @obj, ' execute ', @out 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

;D atabase= ' + @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 number

, @src as error source, @desc as error description

Select @sql, @constr, @fdlist

Go

--The above is the Guide table, the following is the Guide query statement, then everybody learned?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.