首页 > Sql-Mysql > Excel文件导入到SQL,文本、数字丢失,变成NULL的处理

Excel文件导入到SQL,文本、数字丢失,变成NULL的处理

导入一个Excel到Sql,好方便进行数据的筛选,但是却发现,在导入后,电话字段有很多数据丢失,变成了Null,经过仔细检查,发现丢失的都是包含有字符型的(如区号与号码之间的-),再查看一下导入后的sql表,电话所在字段类型为float类型~~~难怪会有很多数据丢失了。。
将Excel中的该列单元格格式改为 文本 格式,再导入,已经不行。

搜索一番,发现有一个方法,不过这个方法只针对于完全数字格式的字段,如若想导入的是数字格式,而非字符串格式,就需要这样的做法:

Excel导入数据到Sql Server

Excel导入数据到Sql Server

1、将导入的过程存为DTS包,将此DTS包找到

2、在刚才保存的DTS包上右键,编辑包

3、在编辑器里右键单击空白处,选择“脱接属性”命令。

4、依次展开“连接”——excel的那个连接——>“OLE DB属性“,找到“Extended Properties”属性。

5、将右边的“Value”属性的值改为“Excel 8.0;HDR=YES;IMEX=1”

6、保存此包、并运行即可。

关于第五步中的IMEX设置有如下解释:

IMEX是用来告诉驱动程序使用Excel文件的模式,其值有0、1、2三种,分别代表导出、导入、混合模式。当我们设置IMEX=1时将强制混合数据转换为文本,但仅仅这种设置并不可靠,IMEX=1只确保在某列前8行数据至少有一个是文本项的时候才起作用,它只是把查找前8行数据中数据类型占优选择的行为作了略微的改变。例如某列前8行数据全为纯数字,那么它仍然以数字类型作为该列的数据类型,随后行里的含有文本的数据仍然变空。
另一个改进的措施是IMEX=1与注册表值TypeGuessRows配合使用,TypeGuessRows 值决定了ISAM 驱动程序从前几条数据采样确定数据类型,默认为“8”。可以通过修改“HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel”下的该注册表值来更改采样行数。但是这种改进还是没有根本上解决问题,即使我们把IMEX设为“1”, TypeGuessRows设得再大,例如1000,假设数据表有1001行,某列前1000行全为纯数字,该列的第1001行又是一个文本,ISAM驱动的这种机制还是让这列的数据变成空。

但是这也仅仅是导入数字类型的字段,如果要导入为字符型的,比如说我们的电话号码形式的(010-21345678),这种做法是行不通的,但是还有另外一种很简单的方法,就是将Excel文件导出成Csv格式的文件,再将csv导入到Sql,就可以很方便的把电话号码导入进去了。这种做法也仅限于导入字符型的数据,因为CSV格式为文本格式,不包括数据类型,如果需要导入后数据为数字类型,就需要用上边的方法了。

  1. rolex watches 12月 5th, 2010 @ 01:53 | #1

    You are mistaken. Let’s discuss it. Write to me in PM, we will communicate.

  2. panerai uhren 12月 5th, 2010 @ 13:22 | #2

    In it something is also to me it seems it is excellent idea. I agree with you.

  3. blancpain 12月 5th, 2010 @ 20:30 | #3

    You have hit the mark. In it something is also to me it seems it is very good idea. Completely with you I will agree.

  4. Disposable Tip 12月 7th, 2010 @ 17:19 | #4

    I consider, that you are not right. I am assured. I can prove it. Write to me in PM, we will discuss.

  5. Accessories 12月 8th, 2010 @ 02:53 | #5

    The same…

  6. best registry cleaner 12月 8th, 2010 @ 05:54 | #6

    Really good sharing this.

  7. Cartier jewelry 12月 8th, 2010 @ 19:44 | #7

    I agree with told all above.

  8. tag heuer 12月 8th, 2010 @ 23:38 | #8

    What phrase… super, remarkable idea

  9. luxury watches 12月 9th, 2010 @ 15:10 | #9

    I apologise, but, in my opinion, you are not right. I am assured. I suggest it to discuss. Write to me in PM, we will communicate.

  10. shopping plan 12月 9th, 2010 @ 19:27 | #10

    I apologise, but I suggest to go another by.

  11. best replica watches review 12月 10th, 2010 @ 09:01 | #11

    Excuse, the phrase is removed

  12. Nothing Is Impossible 12月 10th, 2010 @ 23:42 | #12

    I suggest you to try to look in google.com, and you will find there all answers.

  13. watch cartier 12月 12th, 2010 @ 03:41 | #13

    Very valuable phrase

  14. china electronics 12月 13th, 2010 @ 00:28 | #14

    I consider, that you commit an error. I can prove it. Write to me in PM, we will discuss.

  15. DVD MAX Online 12月 15th, 2010 @ 16:27 | #15

    You commit an error. I can prove it. Write to me in PM, we will communicate.

  16. pandora charm 12月 19th, 2010 @ 10:30 | #16

    In my opinion you are not right. I can prove it. Write to me in PM, we will discuss.

  17. replica bell ross 12月 19th, 2010 @ 20:05 | #17

    Now all became clear, many thanks for the information. You have very much helped me.

  18. sex 12月 24th, 2010 @ 08:10 | #18

    hey, nice weblog and good publish

  19. anonse erotyczne 12月 28th, 2010 @ 12:17 | #19

    Very interesting topic and useful post. It really help me. Thanks.

  20. redtube 1月 5th, 2011 @ 19:27 | #20

    Thanks for such interesting and terrific post. Really useful and helpful information. I’ve twitted your blog.

评论提交中, 请稍候...

留言

可以使用的标签: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
Trackbacks & Pingbacks ( 1 )
  1. 12月 16th, 2010 @ 04:05 | #1
    Trackback: Casino 1292442207