我本意是想把他导入sql server中作为三个字段(x y z)的数据，没想到它们全跑到x字段下了，怎么办？
因为列分隔符中没有“空格”这个选项，所以我就把空格用“替换”功能替换成英文半角逗号，如图所示：
导入后确实变成了3个字段，我以为大功告成，没想到数据库里面的数值和源文件中的数值不一样。这是什么原因？？？？？？？
而且我假设将1.txt导入数据库命名为2.txt,再又导出命名为3.txt,最后发现1、2、3中有很多数据都不一样啊，整了几个小时，都没搞明白什么原因，求解！
6 个解决方案
#1
用分号 ; 分割,然后将文件后缀改为xls，倒入的时候选择excel文件类型。
#2
你把你的数据，贴成文本，不要贴成图，还有你的表结构也贴出来，然后帮你导入试试
#4
-13.311157,1.891571,1.66568
-13.311218,1.894928,1.666016
-13.307159,1.886597,1.66214
-13.319,1.905518,1.662598
-13.320862,1.906586,1.662079
-13.32016,1.909058,1.663513
-13.314331,1.896576,1.664673
-13.310791,1.891571,1.640472
-13.307312,1.888641,1.639923
-13.310486,1.891571,1.645538
-13.307465,1.886597,1.641968
-13.307281,1.886597,1.636963
-13.320679,1.906586,1.641907
-13.317413,1.901581,1.637848
-13.310699,1.895721,1.636414
-13.320984,1.906586,1.636841
-13.313965,1.896576,1.639618
-13.314148,1.896576,1.644531
-13.317596,1.901581,1.642883
-13.318787,1.905548,1.644043
-13.310822,1.891571,1.655609
-13.307159,1.886597,1.647034
-13.306854,1.886597,1.652069
-13.310669,1.891571,1.650543
-13.311096,1.895081,1.651642
-13.307007,1.886597,1.657135
-13.311005,1.891571,1.660645
-13.307495,1.888611,1.658539
-13.320709,1.906586,1.657043
-13.313843,1.896576,1.649689
-13.317291,1.901581,1.647949
-13.317444,1.901581,1.652985
-13.320862,1.906586,1.646942
-13.320068,1.90921,1.648376
-13.320526,1.906586,1.652008
-13.313995,1.896576,1.654602
-13.314148,1.896576,1.659637
-13.317627,1.901581,1.65802
-13.344604,1.941895,1.643311
-13.341125,1.936829,1.639404
-13.337341,1.931793,1.640656
-13.337494,1.931793,1.645752
-13.344421,1.941895,1.638245
-13.34082,1.936829,1.64447
-13.338287,1.936554,1.642975
-13.344635,1.941895,1.658569
-13.337677,1.931763,1.650818
-13.340973,1.936829,1.649536
-13.344299,1.941895,1.648407
-13.341431,1.939301,1.652161
-13.344452,1.941895,1.653473
-13.33786,1.931763,1.655762
-13.338013,1.931763,1.660828
-13.341156,1.936829,1.654602
-13.34082,1.936829,1.659576
-13.338409,1.936401,1.657806
-13.330536,1.921692,1.638275
-13.33429,1.926727,1.641815
-13.334106,1.926727,1.636841
-13.324097,1.911621,1.640625
-13.327087,1.916626,1.639374
-13.323792,1.911621,1.645691
-13.327271,1.916626,1.644562
-13.329254,1.922668,1.645447
-13.330719,1.921692,1.643311
-13.33432,1.926727,1.656982
-13.327454,1.916626,1.649628
-13.323944,1.911621,1.650757
-13.330353,1.922394,1.648529
-13.330872,1.921692,1.648376
-13.333954,1.926727,1.646881
-13.331055,1.921692,1.653442
-13.334137,1.926727,1.651947
-13.324127,1.911591,1.65567
-13.327606,1.916626,1.654694
-13.32428,1.911591,1.660706
-13.327301,1.916626,1.65976
-13.331207,1.921692,1.658478
-13.329071,1.923004,1.659882
-13.317291,1.901581,1.663116
-13.330902,1.921692,1.663544
-13.344788,1.941895,1.663666
-13.340973,1.936829,1.664642
-13.338165,1.931763,1.665894
-13.334473,1.926727,1.662048
-13.327454,1.916626,1.664795
-13.323944,1.911591,1.665802
-12.243042,0.591248,1.664368
-11.432556,-0.153229,1.664246
-11.432678,-0.151917,1.663544
-11.444122,-0.143524,1.664642
-11.441284,-0.145996,1.665314
-11.313354,-0.250732,1.639099
-11.319305,-0.246124,1.641144
-11.316284,-0.248413,1.641907
-11.313477,-0.250885,1.642822
-11.310486,-0.253143,1.636108
-11.307373,-0.255249,1.636963
-11.319489,-0.246368,1.63736
-11.316467,-0.248627,1.638123
-11.31543,-0.246338,1.637756
-11.310272,-0.25293,1.639862
-11.307159,-0.255035,1.640747
-11.310394,-0.253052,1.643585
-11.307281,-0.255157,1.64447
-11.319092,-0.245911,1.644989
-11.316406,-0.248535,1.64563
-11.313477,-0.250916,1.654083
-11.310181,-0.252838,1.647339
-11.307404,-0.25528,1.648193
-11.310303,-0.25296,1.651123
-11.307526,-0.255432,1.651947
-11.313568,-0.251007,1.646545
-11.319214,-0.246033,1.648621
-11.316193,-0.248322,1.649384
-11.31369,-0.251129,1.65033
-11.319,-0.245819,1.652466
-11.316284,-0.248444,1.653137
-11.310791,-0.251495,1.651855
-11.310425,-0.253082,1.654846
-11.307617,-0.255554,1.655701
-11.310211,-0.252838,1.6586
-11.307404,-0.25531,1.659454
-11.319092,-0.245941,1.656219
-11.316406,-0.248566,1.65686
-11.315338,-0.245972,1.654022
-11.313385,-0.250793,1.66156
-11.313599,-0.251038,1.657806
-11.319214,-0.246063,1.659973
-11.316528,-0.248688,1.660583
-11.377228,-0.198547,1.639191
-11.37735,-0.198669,1.642914
-11.368652,-0.205811,1.641663
-11.368866,-0.206024,1.637878
-11.371552,-0.203461,1.636963
-11.374908,-0.201508,1.63623
-11.368774,-0.205933,1.645386
-11.371338,-0.203247,1.640717
-11.369415,-0.202271,1.639252
-11.374695,-0.201294,1.640015
-11.37146,-0.203369,1.64447
-11.374817,-0.201447,1.643768
-11.392059,-0.18692,1.638428
-11.386017,-0.191498,1.636505
-11.394775,-0.184326,1.637848
-11.386169,-0.19162,1.640259
-11.386292,-0.191742,1.643982
-11.394897,-0.184448,1.641602
-11.391968,-0.186829,1.645935
-11.391846,-0.186707,1.642212
-11.39502,-0.18457,1.645264
以上只是我数据的一部分，我需要导入的数据一般都是几万行~几百万行(空间三维点坐标)，反正就是这种格式的txt文件，本来数据之间是一个空格，逗号是用替换功能加上去的。我是新手，所以文件我用“导入导出向导”进行导入和导出的，问题来了：我导入整个txt文件后，在表中发现数据无法和源文件中的数据对应，例如源文件中1~50行的数据到表中却成了12532~12581行，120~200行的数据到表中成了800~880行，好像就是把源文件的数据分成了很多块，再乱序拼接起来；我把数据库中的表删了重新导入，又发现这下表中的数据又能和txt源文件中的数据完全对应起来，我删了重新导入，反反复复搞了几十次，发现表中的数据有时候能和源文件中对应起来，有时候又无法对应，但出错的次数更多。还有就是，我在源txt文件中复制一部分数据出来放在另一个txt文件中，导入后发现表中的数据能和txt中的数据能完全对应，不论复制多少，都能完全对应，但如果用CTRL+A全选，复制后再导入，又不对。各位大神，何解？？？就这个导数据我都弄好几个小时，
#5
这是我需要导入的txt文件的连接地址：http://pan.baidu.com/s/1dDikvKl
恳请牛人帮帮忙哈！
恳请牛人帮帮忙哈！
#6
我试了一下，能查询到数据的：
use pubs
go
create table xxdd
(
aa nvarchar(1000),
bb nvarchar(1000),
cc nvarchar(1000)
)
go
exec xp_cmdshell 'bcp pubs.dbo.xxdd format nul -t " " -f c:\wc.fmt -c -Usa -Pyupeigu -S 192.168.1.100,1433'
go
select *
from
openrowset(bulk 'c:\基圆.txt', --要读取的文件路径和名称
formatfile='c:\wc.fmt', --格式化文件的路径和名称
firstrow = 1, --要载入的第一行,由于第一行是标题,所以从2开始
--lastrow = 1000, --要载入的最后一行,此值必须大于firstrow
maxerrors = 10, --在加载失败之前加载操作中最大的错误数
--errorfile ='c:\wc_error1.txt', --存放错误的文件
rows_per_batch = 10000 --每个批处理导入的行数
) as t
这个是查询到的记录：
#1
用分号 ; 分割,然后将文件后缀改为xls，倒入的时候选择excel文件类型。
#2
你把你的数据，贴成文本，不要贴成图，还有你的表结构也贴出来，然后帮你导入试试
#3
#4
-13.311157,1.891571,1.66568
-13.311218,1.894928,1.666016
-13.307159,1.886597,1.66214
-13.319,1.905518,1.662598
-13.320862,1.906586,1.662079
-13.32016,1.909058,1.663513
-13.314331,1.896576,1.664673
-13.310791,1.891571,1.640472
-13.307312,1.888641,1.639923
-13.310486,1.891571,1.645538
-13.307465,1.886597,1.641968
-13.307281,1.886597,1.636963
-13.320679,1.906586,1.641907
-13.317413,1.901581,1.637848
-13.310699,1.895721,1.636414
-13.320984,1.906586,1.636841
-13.313965,1.896576,1.639618
-13.314148,1.896576,1.644531
-13.317596,1.901581,1.642883
-13.318787,1.905548,1.644043
-13.310822,1.891571,1.655609
-13.307159,1.886597,1.647034
-13.306854,1.886597,1.652069
-13.310669,1.891571,1.650543
-13.311096,1.895081,1.651642
-13.307007,1.886597,1.657135
-13.311005,1.891571,1.660645
-13.307495,1.888611,1.658539
-13.320709,1.906586,1.657043
-13.313843,1.896576,1.649689
-13.317291,1.901581,1.647949
-13.317444,1.901581,1.652985
-13.320862,1.906586,1.646942
-13.320068,1.90921,1.648376
-13.320526,1.906586,1.652008
-13.313995,1.896576,1.654602
-13.314148,1.896576,1.659637
-13.317627,1.901581,1.65802
-13.344604,1.941895,1.643311
-13.341125,1.936829,1.639404
-13.337341,1.931793,1.640656
-13.337494,1.931793,1.645752
-13.344421,1.941895,1.638245
-13.34082,1.936829,1.64447
-13.338287,1.936554,1.642975
-13.344635,1.941895,1.658569
-13.337677,1.931763,1.650818
-13.340973,1.936829,1.649536
-13.344299,1.941895,1.648407
-13.341431,1.939301,1.652161
-13.344452,1.941895,1.653473
-13.33786,1.931763,1.655762
-13.338013,1.931763,1.660828
-13.341156,1.936829,1.654602
-13.34082,1.936829,1.659576
-13.338409,1.936401,1.657806
-13.330536,1.921692,1.638275
-13.33429,1.926727,1.641815
-13.334106,1.926727,1.636841
-13.324097,1.911621,1.640625
-13.327087,1.916626,1.639374
-13.323792,1.911621,1.645691
-13.327271,1.916626,1.644562
-13.329254,1.922668,1.645447
-13.330719,1.921692,1.643311
-13.33432,1.926727,1.656982
-13.327454,1.916626,1.649628
-13.323944,1.911621,1.650757
-13.330353,1.922394,1.648529
-13.330872,1.921692,1.648376
-13.333954,1.926727,1.646881
-13.331055,1.921692,1.653442
-13.334137,1.926727,1.651947
-13.324127,1.911591,1.65567
-13.327606,1.916626,1.654694
-13.32428,1.911591,1.660706
-13.327301,1.916626,1.65976
-13.331207,1.921692,1.658478
-13.329071,1.923004,1.659882
-13.317291,1.901581,1.663116
-13.330902,1.921692,1.663544
-13.344788,1.941895,1.663666
-13.340973,1.936829,1.664642
-13.338165,1.931763,1.665894
-13.334473,1.926727,1.662048
-13.327454,1.916626,1.664795
-13.323944,1.911591,1.665802
-12.243042,0.591248,1.664368
-11.432556,-0.153229,1.664246
-11.432678,-0.151917,1.663544
-11.444122,-0.143524,1.664642
-11.441284,-0.145996,1.665314
-11.313354,-0.250732,1.639099
-11.319305,-0.246124,1.641144
-11.316284,-0.248413,1.641907
-11.313477,-0.250885,1.642822
-11.310486,-0.253143,1.636108
-11.307373,-0.255249,1.636963
-11.319489,-0.246368,1.63736
-11.316467,-0.248627,1.638123
-11.31543,-0.246338,1.637756
-11.310272,-0.25293,1.639862
-11.307159,-0.255035,1.640747
-11.310394,-0.253052,1.643585
-11.307281,-0.255157,1.64447
-11.319092,-0.245911,1.644989
-11.316406,-0.248535,1.64563
-11.313477,-0.250916,1.654083
-11.310181,-0.252838,1.647339
-11.307404,-0.25528,1.648193
-11.310303,-0.25296,1.651123
-11.307526,-0.255432,1.651947
-11.313568,-0.251007,1.646545
-11.319214,-0.246033,1.648621
-11.316193,-0.248322,1.649384
-11.31369,-0.251129,1.65033
-11.319,-0.245819,1.652466
-11.316284,-0.248444,1.653137
-11.310791,-0.251495,1.651855
-11.310425,-0.253082,1.654846
-11.307617,-0.255554,1.655701
-11.310211,-0.252838,1.6586
-11.307404,-0.25531,1.659454
-11.319092,-0.245941,1.656219
-11.316406,-0.248566,1.65686
-11.315338,-0.245972,1.654022
-11.313385,-0.250793,1.66156
-11.313599,-0.251038,1.657806
-11.319214,-0.246063,1.659973
-11.316528,-0.248688,1.660583
-11.377228,-0.198547,1.639191
-11.37735,-0.198669,1.642914
-11.368652,-0.205811,1.641663
-11.368866,-0.206024,1.637878
-11.371552,-0.203461,1.636963
-11.374908,-0.201508,1.63623
-11.368774,-0.205933,1.645386
-11.371338,-0.203247,1.640717
-11.369415,-0.202271,1.639252
-11.374695,-0.201294,1.640015
-11.37146,-0.203369,1.64447
-11.374817,-0.201447,1.643768
-11.392059,-0.18692,1.638428
-11.386017,-0.191498,1.636505
-11.394775,-0.184326,1.637848
-11.386169,-0.19162,1.640259
-11.386292,-0.191742,1.643982
-11.394897,-0.184448,1.641602
-11.391968,-0.186829,1.645935
-11.391846,-0.186707,1.642212
-11.39502,-0.18457,1.645264
以上只是我数据的一部分，我需要导入的数据一般都是几万行~几百万行(空间三维点坐标)，反正就是这种格式的txt文件，本来数据之间是一个空格，逗号是用替换功能加上去的。我是新手，所以文件我用“导入导出向导”进行导入和导出的，问题来了：我导入整个txt文件后，在表中发现数据无法和源文件中的数据对应，例如源文件中1~50行的数据到表中却成了12532~12581行，120~200行的数据到表中成了800~880行，好像就是把源文件的数据分成了很多块，再乱序拼接起来；我把数据库中的表删了重新导入，又发现这下表中的数据又能和txt源文件中的数据完全对应起来，我删了重新导入，反反复复搞了几十次，发现表中的数据有时候能和源文件中对应起来，有时候又无法对应，但出错的次数更多。还有就是，我在源txt文件中复制一部分数据出来放在另一个txt文件中，导入后发现表中的数据能和txt中的数据能完全对应，不论复制多少，都能完全对应，但如果用CTRL+A全选，复制后再导入，又不对。各位大神，何解？？？就这个导数据我都弄好几个小时，
#5
这是我需要导入的txt文件的连接地址：http://pan.baidu.com/s/1dDikvKl
恳请牛人帮帮忙哈！
恳请牛人帮帮忙哈！
#6
我试了一下，能查询到数据的：
use pubs
go
create table xxdd
(
aa nvarchar(1000),
bb nvarchar(1000),
cc nvarchar(1000)
)
go
exec xp_cmdshell 'bcp pubs.dbo.xxdd format nul -t " " -f c:\wc.fmt -c -Usa -Pyupeigu -S 192.168.1.100,1433'
go
select *
from
openrowset(bulk 'c:\基圆.txt', --要读取的文件路径和名称
formatfile='c:\wc.fmt', --格式化文件的路径和名称
firstrow = 1, --要载入的第一行,由于第一行是标题,所以从2开始
--lastrow = 1000, --要载入的最后一行,此值必须大于firstrow
maxerrors = 10, --在加载失败之前加载操作中最大的错误数
--errorfile ='c:\wc_error1.txt', --存放错误的文件
rows_per_batch = 10000 --每个批处理导入的行数
) as t
这个是查询到的记录：