Importing a tab delimited file into SQL Server

I am trying to impport a tab demlied txt file, into SQL SERVER, using OPENROWSET. Howver, the file gets imports, into SQL, BUT ONLY in 1 column... The source file really has 3 columns, but when it gets isnerted into SQL, it only oputs it all in 1 column.

select * into #tmp from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=\\c:\SQLRSReports;','select * from vwConstrLendingmapLoanDefiCdDesc.txt')

How do I insert the data into 3 seperate colms, instead of 1?

Try

BULK INSERT
http://msdn2.microsoft.com/en-us/library/ms188365.aspx

or
BCP
http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx|||I would like to use the OPENROWSET Only. Is that possible with Openrowset?|||

Code Snippet

CREATE TABLE #newIn
(
Ident varchar(max),
subIdent int identity(1,1)
subItem varchar(max)
)

DECLARE @.posA int,
@.posB int,
@.srch char(1),
@.in varchar(max)

SET @.srch = ',' --or char(9) for tab

DECLARE csr CURSOR FOR
SELECT item from #tmp

OPEN csr

FETCH NEXT FROM csr
INTO @.in

WHILE @.@.FETCH_STATUS = 0
BEGIN

SET @.in = LTRIM(RTRIM(@.in))+@.srch
IF len(@.in)>1
BEGIN
SET @.posB = CHARINDEX(@.srch, @.in)
SET @.posA = 0
WHILE @.posB > 0
BEGIN
INSERT INTO #newIn (Ident, subItem) VALUES (@.in, substring(@.in, @.posA, @.posB-@.posA))
SET @.posA = @.posB + 1
SET @.posB = CHARINDEX(@.srch, @.in, @.posA)
END
END
END

CLOSE csr
DEALLOCATE csr

select *
from #newIn

DROP TABLE #newIn

This converts the single column into multiple rows that you can then extract back as you want.

If not exactly what you need, then it's a starting point for other variations.|||

I created a schema.ini file, with all the format specifiactions for my source file. Here is the code for that. However, when I import, it most of the data is imported fine, except for when the value has "" in the data. eg if the data has value such as 'pending "store" ', it imports the data as only "pending", and omits the store..

Here is my specificaiton of the schema file

[vwConstrLendingmapLoanDefiCdDesc.txt]
ColNameHeader=False
Format=TabDelimited
MaxScanRows=0
CharacterSet=ANSI
Col1=Doccd Char Width 50
Col2=Docdesc LongChar
Col3=Docshcd Char Width 100

- And this is the openrowset:

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=\\c:\SQLRSReports;','select * from vwConstrLendingmapLoanDefiCdDesc.txt')

Pl advise