April 11, 2007 at 12:06 pm
I am trying to figure out how I can select from a flat file,
with openrowset.
I know how to do that in excel like below
How can I select from a tab delimited file
This is what I do with an excel file in SQL 2005
Select s1.[Name]FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\db\file.xls;HDR=YES',
'SELECT distinct [Name] FROM [Sheet1$] as s1 where [Name] is not null order by [Name] desc'
) as s1
I am not sure what to do to show what the delimiters are I know in bulk insert you can do something like
BULK INSERT DB FROM 'filename'
WITH (
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)
April 12, 2007 at 4:18 am
April 12, 2007 at 5:54 am
I tried this, but I keep getting invalid argument
Select s1.[Name]FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Datasource=d:\db\pcorfiles\stcvis.txt;HDR=YES,FMT=Delimited',
'SELECT distinct [Name] FROM [Sheet1$] as s1 where [Name] is not null order by [Name] desc'
) as s1
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply