数据仓库之抽取数据：通过链接服务器获取txt、csv中的数据

 

在做数据仓库时，最重要的就是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.
              
       后面两个如果没有特殊情况的话可以不写，默认即可。