Excel导入超难题200分求解

Question

是的 ,我也遇到这个问题,如果一个列数字居多,但又有文字的,会把文字变为NULL  ,没找到解决的方法,现在的办法是先把EXCEL导如ACCESS,然后再导到SQLSERVER

Answer 1

文件发到 [email protected] 供测试

Answer 2

--也发我一个,我给你测试 [email protected]

Answer 3

导入导出大全 
*******  导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'/***********  导入Excel
select * from openrowset('MicroSoft.Jet.OleDB.4.0',
                         'Excel 5.0;HDR=yes;Database=c:\test.xls',sheet1$)
--------------------------------------------------------
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions/** 导入文本文件
EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'/** 导出文本文件
EXEC master..xp_cmdshell 'bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'导出到TXT文本，用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
BULK INSERT 库名..表名
FROM 'c:\test.txt'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '\n'
)
--/* dBase IV文件
select * from 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]')
--*/--/* dBase III文件
select * from 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]')
--*/--/* FoxPro 数据库
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
--*//**************导入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
/***************** 导出到DBF ***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
select * from 表说明:
SourceDB=c:\  指定foxpro表所在的文件夹
aa.DBF        指定foxpro表的文件名. 
/*************导出到Access********************/
insert into openrowset('Microsoft.Jet.OLEDB.4.0', 
   'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表/*************导入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0', 
   'x:\A.mdb';'admin';'',A表) 
 
Top 
 
 回复人： txlicenhe(马可) ( ) 信誉：167  2003-10-10 18:52:32Z  得分:0  
 
 
  
*********************  导入 xml　文件DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
      </Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue 
             white red">
            <Urgency>Important</Urgency>
            Happy Customer.
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 1)
      WITH (oid     char(5), 
            amount  float, 
            comment ntext 'text()')
EXEC sp_xml_removedocument @idoc 
Top 
 
 回复人： txlicenhe(马可) ( ) 信誉：167  2003-10-10 18:54:30Z  得分:0  
 
 
  
邹健的
/********************导整个数据库*********************************************/用bcp实现的存储过程
/*
实现数据导入/导出的存储过程
         根据不同的参数,可以实现导入/导出整个数据库/单个表
调用示例:
--导出调用示例
----导出单个表
exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',1
----导出整个数据库
exec file2table 'zj','','','xzkh_sa','C:\docman',1--导入调用示例
----导入单个表
exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',0
----导入整个数据库
exec file2table 'zj','','','xzkh_sa','C:\docman',0*/
if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
drop procedure File2Table
go
create procedure File2Table
@servername varchar(200)--服务器名
,@username varchar(200)--用户名,如果用NT验证方式,则为空''
,@password varchar(200)--密码
,@tbname varchar(500)--数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filename varchar(1000)--导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit--1为导出,0为导入
as
declare @sql varchar(8000)if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
begin
set @sql='bcp '+@tbname
+case when @isout=1 then ' out ' else ' in ' end
+' "'+@filename+'" /w'
+' /S '+@servername
+case when isnull(@username,'')='' then '' else ' /U '+@username end
+' /P '+isnull(@password,'')
exec master..xp_cmdshell @sql
end
else
begin--导出整个数据库,定义游标,取出所有的用户表
declare @m_tbname varchar(250)
if right(@filename,1)<>'\' set @filename=@filename+'\'set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U'''
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
set @sql='bcp '+@tbname+'..'+@m_tbname
+case when @isout=1 then ' out ' else ' in ' end
+' "'+@filename+@m_tbname+'.txt " /w'
+' /S '+@servername
+case when isnull(@username,'')='' then '' else ' /U '+@username end
+' /P '+isnull(@password,'')
exec master..xp_cmdshell @sql
fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go

Answer 4

数据类型的问题在你的工作表中,显示为NULL值的行记录是"以文本形式显示数字", 只需要改成数字就可以出来了.excel中打开你的文件--单左上角有绿色小三角的单元格--单击出现的带!的按钮--选择"转换为数字",保存再查询就可以了.
如果确定某列全部应该为数字,可以用下面的方法处理所有的:
1. 在一个空白单元格中输入数字1
2. 复制该单元格
3. 选中需要处理的单元格,可以通过Ctrl多选.
4. 菜单中选择"编辑"--"选择性粘贴"
5. 在出现的对话框中,单"运算"项中的"乘"
6. 确定.
7. 保存处理结果,然后再查询就正常了.

Answer 5

另一种解决方法.打开excel文件,在第1条记录(字段名外的)的数字前输入1个',表示用文本形式来存储数字.
然后保存这样也能查询出来.

Answer 6

Excel的文件是客户通过应用程序生成Excel,再做过加工后进行导入,所以实际上他的整个具体操作流程格式转换我并不清楚,这应该是Excel自身不是数据库文件造成的,在导入时,无法辩识是文本或是数字.
在第一条记录前加' 号 ,并不足够条件导入,原则上Excel是搜寻前八行,如果文本多于数字,则是文本列,如是数字多于文字,则是数字列.但我开启了注册表的这个选项: TypeGuessRows=0,也就是全行检索,它会扫描整个行.
全加'的方式,我知道可行,但希望客户不需要动这个动作,直接通过应用程序就可导入.
而且数字和文本,有些编号是以0开头的,不能全以数字格式做到,必须以全文本格式,但Excel就算转为文本,也导不进去.

Answer 7

不要客户做也行,写个程序预处理一下excel文件嘛

Answer 8

这是excel导入数据库中经常会遇到的问题。只有在导入之前做预处理的方式最可行。我也是一直这么做的。
不知道是否有更好的解决方法。

Answer 9

1. 我使用的是2003, 只在第1条记录加'可以解决问题(针对楼主提供的文件)
2. 我的注册表只有
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\
3. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows
   决定数据类型抽样行数,默认为8, 你可以设置为1, 这样只根据第1条记录决定.
   如果为0,不知道是什么结果了
4. OPENROWSET中的MICROSOFT.JET.OLEDB.4.0应该对应注册表中的Jet\4.0

Answer 10

首先感谢各位朋友的帮忙.
虽然问题没有彻底解决,但后来想了一下,如果数据源正确的话,是可以导入,而事后处理的方式,估计微软的EXCEL和ADO是无法处理的.
Excel2000与2003的确差别挺大,如邹建的那个小三角与第一行加字符,都是2003才有的.最后,再次感谢网上的热心朋友.
200分只给一半可别说我小气哦:D :D :D