Перейти к содержимому

exel

MSsql чтение excel и txt

чтение excel

SELECT * INTO XLImportBetaX FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT jobid,userid,start,stop,date FROM [14.09.2012$]')

insert INTO XLImport512 select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [13.09.2012$]')

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT jobid,userid,start,stop,date FROM [14.09.2012$]')

select * from XLImportBeta2 exec sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE

чтение txt

 SELECT * FROM OPENROWSET(BULK N'C:\aos1.txt',SINGLE_CLOB ) AS Document

SELECT a.* FROM OPENROWSET( BULK 'c:\aos1.txt',
 FORMATFILE = 'c:\values.fmt') AS a;
 
   file format
 
 9.0
2
1  SQLCHAR  0  20 ":" 1 ID ""
2  SQLCHAR  0  20 "*" 2 IwD ""