SQL Server 从Excel导入到数据库操作遇到的科学计数法问题

问题描述

   今天在做从Excel导入数据到SQL Server 中将数据更新到表中，可惜就这一个简单的操作中出现了一点小插曲，就在我根据Excel中的编号关联表编号以此更新姓名字段时出现转换错误问题。如下图1所示:

      

       图：1

    在SQL Server中，数据类型隐式转换为从低级别到高级别转换，FLOAT类型级别高于NVARCHAR，以至于出现图1错误，关于SQL Serevr 详细数据类型优先级别如下：

    从高到底

2.sql_variant
3.xml
4.datetimeoffset
5.datetime2
6.datetime
7.smalldatetime
8.date
9.time
10.float
11.real
12.decimal
13.money
14.smallmoney
15.bigint
16.int
17.smallint
18.tinyint
19.bit
20.ntext
21.text
22.image
23.timestamp
24.uniqueidentifier
25.nvarchar (including nvarchar(max) )
26.nchar
27.varchar (including varchar(max) )
28.char
29.varbinary (including varbinary(max) )
30.binary (lowest)

 

    看到这个问题相信大家都觉得很简单，直接将字段转换成字符串类型不就可以了吗？ 好吧，直接CONVERT转成NVARCHAR类型随后执行，可以执行成功但是关联出来没有数据，很奇怪！由于编号在Excel中的类型为FLOAT，经过转换后数据变成科学计数法数据，模拟一下数据，如下图2所示：

    

 

    图：2

 

    知道问题缘由后便可以明准的解决问题，可以有一下几种方法：

    1. 在Excel中直接将编号列设置成文本格式，随后到入到SQL Server做操作。

    2.将FLOAT转换成字符串类型，可先将FLOAT转成整形或是DECIMAL类型，然后再转成字符串即可。

    3.将关联另一个表字段转为FLOAT类型。

   

    关于解决问题第三点，我将另一个表中的字段转为FLOAT类型出现的如图1错误（从数据类型 nvarchar 转换为 float 时出错。）  ，经过查看字段的数据全是由数字组成，为何还报转换错误？ 由于空格并不会引起该错误，然后我将目标转到换行，回车符上面，果然有的数据最后存在换行符。随后将换行符（char(10)）替换成空即可。

 

    顺便附带一下从Excel导入数据到SQL Server的脚本：

    

--OPENROWSET

SELECT  *
FROM    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                   'Excel 12.0;HDR=Yes;IMEX=1;Database=C:/Users/Administrator/Desktop/20191008.xlsx',
                   'select * from [Sheet3$]');  

--OpenDataSource
SELECT  *
FROM    OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
                       'Data Source=C:/Users/Administrator/Desktop/20191008.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet3$];

 

    以上为在工作中遇到的一点小问题，记录下来以便后续用，也可以为其他遇到这个小坑的朋友提供一点解决的办法。