在做数据仓库时,最重要的就是ETL的开发,而在ETL开发中的第一步,就是要从原OLTP系统中抽取数据到过渡区中,再对这个过渡区中的数据进行转换,最后把经过处理的干净的数据加载到数据仓库中。
目标数据库是sql server,现在要从txt、csv文件中抽取数据。
1、开启即席查询
--修改高级参数
sp_configure 'show advanced options',1
go
--允许即席分布式查询
sp_configure 'Ad Hoc Distributed Queries',1
go
--如果配置的值不在合理范围(在最小值最大值范围内),那么可以强制覆盖
reconfigure with override
go
2、建立txt的连接服务器
EXEC sp_addlinkedserver
@server = 'txtsrv',
@srvproduct = 'Jet 4.0',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'c:\', --文件所在的目录
--NULL,
@provstr = 'Text'
--设置登录名
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
GO
--列出在链接服务器上的表,会列出目录中所有的文件:t#txt , list#csv
EXEC sp_tables_ex txtsrv
GO
3、查询
SELECT *
FROM txtsrv...[t#txt]
4、通过bulk insert的方式导入数据
先在C盘下创建 t.txt文件,然后输入几条数据。
另外,必须先建立一个表,批量插入时 表的字段必须与要导入的文件中的字段数量一致。
create table tt(a1 varchar(10),
a2 varchar(10),
a3 varchar(10))
BULK INSERT tt
FROM 'c:\t.txt' --扩展名为.csv的文件也可以
WITH(
FIELDTERMINATOR = ',', --字段分隔符
ROWTERMINATOR = '\n') --行终止符
SELECT * FROM tt
5、openrowset函数,通过Microsoft Text Driver驱动查询数据
在32位系统上通过openrowset函数,用Microsoft Text Driver或OLE DB Provider for Jet库将txt和CSV数据导入SQL Server表,
但是没有大容量导入优化。
--csv格式,通过Microsoft Text Driver驱动
select *
from
openrowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DBQ=C:\;', --驱动程序,存放txt或cvs的文件路径
'select * from list.csv') --用文件名称做为表名
--txt格式,通过Microsoft Text Driver驱动
select *
from
openrowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DBQ=C:\;', --驱动程序,存放txt或cvs的文件路径
'select * from list.txt')
6、openrowset函数,通过OLE DB Provider for Jet查询数据
--txt格式,通过OLE DB Provider for Jet
select *
from
openrowset('microsoft.jet.oledb.4.0',
'text;
hdr=yes;
database=c:\', --文件的路径
t#txt) --格式为:文件名称#扩展名
--csv格式,通过OLE DB Provider for Jet
select *
from
openrowset('microsoft.jet.oledb.4.0',
'text;
hdr=yes;
database=c:\', --文件的路径
list#csv) --格式为:文件名称#扩展名
需要注意的是:
如果分隔符不是逗号那么需要定义schema.ini文件。
schema.ini要和数据文件放在同一个目录下面.
schema.ini的定义方法:
[t.txt]
COLNAMEHEADER=TRUE
FORMAT=csvdelimited
col1=name1 short
col2=name2 short
col3=name3 short
col4=name4 short
第一行:如果文本文件的数据有列名,并且作为查询结果的列名的话,
则ColNameHeader应该为true,否则定义为false,
但是如果后面定义了列名的话,则不使用文本文件中数据的列名,而使用后面定义的列名。
但是如果数据有列名的,最好定义为true,否则的话,数据的列名将作为表中的数据。
第二行:format定义数据的分隔符。
一般的分隔符:空格,制表符,逗号。
分别对应:delimited( )(注意括号里应该有一个空格),tabdelimited,csvdelimited。
在选择分隔符时,建议使用tab,因为数据看起来比较整齐,但不能为保证数据的严格整齐,
而在数据当中使用多个tab,这样会导致读取数据时出现错误。其他的逗号和空格同样。
第三行到最后:指定每一列的列名、数据类型、字符集(使用ansi或者oem)、数据类型转换。
jet数据类型:bit,byte,short,long,currency,
single,double,datetime,text,momo
ODBC数据类型:char,float,int,longchar,date.
后面两个如果没有特殊情况的话可以不写,默认即可。