解决将Excel表导入到SQL Server数据库时出现Text was truncated or one or more characters had no match in the target code错误

编写python爬虫程序可以在电商、旅游等网站上爬取相关评论数据,这些数据可以用于词云制作、感情词分析、提取关键词等,也可以将爬取下来的数据以自己的方式进行展示。评论数据爬取下来后,就要考虑怎样入库,可以在爬虫程序中编写代码直接入库,也可以将爬取到的数据存到Excel表格中,再将Excel表格导入到数据库中。在将Excel表格导入到SQL Server数据库时可能会出现很多错误,这里要解决的错误是:Text was truncated or one or more characters had no match in the target code。这是由于字段大小(size)设置过小,而数据中有超过这个size的记录从而导致了截断(truncate)。

在SSMS中,可以编写SQL语句或使用菜单创建数据表,并为表中的字段设置大小(size)。在将Excel表格导入到数据库时,字段的大小又是怎么确定的呢?在Excel表格中,第一行的各列值作为数据表(指数据库中的table)中的各字段名,第二行以后的每一行的记录将作为数据表中的记录。下面两张图反映了Excel表中数据和数据表中数据的对应关系。

景点概览示例

景点概览示例2

我们在SSMS中打开“景点概览”数据表的设计视图,查看各字段的大小(size)。

数据表中各字段的大小

Excel表在导入到数据库后,字段的Data Type(数据类型)是根据Excel表中第二行(即第一条记录)的值推出的。如果第二行对应列的那个值是数字,数据库中相应字段的Data Type就是float型,如果值是字符,相应字段的Data Type就是nvarchar(255)。将数字都作为float型,将字符都作为nvarchar(255)型可以避免一些截断问题或高精度向低精度转换等问题的出现。但如果某一个字段的Data Type被设置成了nvarchar(255),而表中又有记录的值的大小超过了255,那么就会出现文章开头所说的truncate(截断)问题。所以,将Excel表格的各条记录中,最长的那条记录放到Excel表格的第二行(即第一条记录的位置),就可以解决上面所说的问题


一、准备Excel表格数据

  Excel表格是可以导入到SQL Server的,但是WPS表格是不行的。准备好Excel表格后,另存为成合适的格式(Excel 2003或当前的Excel版本号,后面会用到)。这里保存成Excel 2003(.xls)的格式。使用的数据是夫子庙景区的评论数据,首行只有一个字段“评论”。

  将Excel表格另存为合适的格式

  夫子庙景区评论

二、在SSMS中创建数据库并导入Excel表格

  打开SSMS,创建数据库,右键数据库-Tasks-Import Data,打开SQL Server Import and Export Wizard。

  右键数据库导入Excel

  SQL Server Import and Export Wizard

  选择Data source数据源为Microsoft Excel表格,选择准备好的Excel文件,在Excel version中选择相应的版本号。已自动勾选的First row has column names表示Excel表格中首行作为字段值。

  选择要导入的Excel表格

  选择导入Destination为:SQL Server Native Client,Server name是当前SQL Server连接的服务名,应该是自动出现的,Authentication选择登录方式(Windows身份验证或SQL Server身份验证),Database选择Excel表导入到的数据库。

  选择要导入到的数据库

  选择全部导入或者编写SQL语句部分导入(我也不是很明白..)。这里选择:Copy data from one or more tables or views,全部导入。

  全部导入或query选择导入

  Next之后,选择Excel表中的工作簿(这里只有一个夫子庙)。

  选择夫子庙表

  点击Edit Mappings,可以看到“评论”字段的Data Type被设置成了nvarchar(255)。这里要注意,虽然在这里Size这个值是可以更改的,但是即使改为max后依然导入失败(没有解决)。

  查看夫子庙评论字段的size

  继续后续步骤,执行导入。

  后续步骤1

  后续步骤2

  点击完成,导入失败,查看出错日志。

  报错

三、整理Excel表格数据重新导入

  上面的Excel表格导入失败了,在整理表格之前,要在SSMS中将失败的数据表删掉(虽然失败了,但是数据表的框架已经创建了)。如果对表格进行SELECT查询,结果是空的。

  查询到了空结果

  打开Excel表格,找到最长的那条记录,并将其移动到第二行。

  找到最长的那条记录

  将最长的那条记录移到第二行

  重新导入到数据库,这次导入成功了。

  查看夫子庙评论字段的size2

  导入成功

  在SSMS中,SELECT查询刚才导入进来的数据。Excel表格导入进来的数据表的名字是Excel表中数据簿的名字后+"$"符号。

  查询刚才导入进来的数据

四、总结

  在将Excel表格导入到SQL Server数据库时如果出现:Text was truncated or one or more characters had no match in the target code 这个错误,将Excel表中最长的那条记录移到第二行即可(即第一条记录所在行)解决导入失败的问题

  另外,SQL Server数据库的导入导出功能还可以实现数据库中数据导出到Excel表或其他一些数据源之间的相互导入导出,读者可以进行尝试。

posted @ 2018-10-07 21:30  wangmengdx  阅读(2070)  评论(0编辑  收藏