SQL Server ->> 自动创建表并从文件加载数据

这个存储过程自动创建表并从文件加载数据。

有一点需要说明的是Excel 12.0驱动是兼容了Excel 97-2003和Excel 2007两者格式的Excel文件。

 

CREATE PROCEDURE [dbo].[LoadDataFromFileIntoXXXXDB]
    @FilePath NVARCHAR(4000), --Ex. D:\test.xls
    @SheetName NVARCHAR(4000), --Ex. Sheet1
    @FileType  NVARCHAR(50), --Ex. Excel
    @TargetTableSchema SYSNAME, --Ex. dbo
    @TargetTableName SYSNAME --Ex. MyTable
AS
SET NOCOUNT ON

DECLARE @msg NVARCHAR(4000)

IF ISNULL(@FilePath,'') = ''
    RAISERROR('@FilePath can not be empty',16,1)

IF ISNULL(@SheetName,'') = ''
    RAISERROR('@SheetName can not be empty',16,1)

IF NOT EXISTS(SELECT * FROM [XXXX].sys.schemas WHERE name = @TargetTableSchema)
BEGIN
    SET @msg = REPLACE('Schema @TargetTableSchema can not be found','@TargetTableSchema',@TargetTableSchema)
    RAISERROR(@msg,16,1)
    RETURN
END

DECLARE @cmd NVARCHAR(MAX)
SET @cmd = '
IF OBJECT_ID(''[XXXX].['+@TargetTableSchema+'].['+@TargetTableName+']'') IS NOT NULL
DROP TABLE [XXXX].['+@TargetTableSchema+'].['+@TargetTableName+']'

EXEC(@cmd)

IF @FileType = 'Excel'
BEGIN
    SET @cmd = '
    SELECT * INTO [XXXX].['+@TargetTableSchema+'].[' + @TargetTableName + ']
    FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0''　
                    ,''Excel 12.0 Xml;HDR=YES;IMEX=1;Database='+@FilePath+'''
                    ,''SELECT * FROM ['+@SheetName+'$]'');
    '
END

--PRINT(@cmd)
EXEC (@cmd)

SET NOCOUNT OFF

 

 

------------------------------------- updated 2015/12/09 ------------------------------------

上方代码的OPENROWSET加入了“IMEX=1”。IMEX选项的意思是import mode。0代表Export Mode，1代表import mode，2代表linked mode。默认是0。这种模式下OLEDB根据文件字段靠前的行的类型确认数据类型，这样就会出现数据丢失的情况。假设说前10行中Field1都是123456，第11行是123，456，可是第12行出现了“   123,456 ”。（注意下第12行的这个值其实前后不是空格，如果尝试在SQL Server里面用T-SQL函数ASCII转成ASCII码，你会发现其实不是空格，但是这种字符容易在文件内容为用户手工编辑，尤其是用户电脑是中文版的情况下，因为用户很可能使用中文输入法。）。

如果默认IMEX=0，则上面的结果是第11行成功被隐式转换成了123456（整型），第12行变为NULL。为了避免这种风险，把IMEX设置为1，这样每次数据加载就可以避免数据丢失的情况，因为所有的数据都被当成字符串对待。至于IMEX=2是什么？没去了解。

 

参考文献：

Mixed data types in Excel column