Insert using multiple field terminators

Hi all,
In Oracle, SQL Loader allows you to use the statement below:
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '|'
Is there a way to do this with BULK INSERT or some other import function?
The data files that I am attempting to import has fields terminated by commas
but when some of those text fields may contain a comma, they are also
enclosed by pipes. The above statement in Oracle deals with this issue
effectively... hoping I can find something for MS SQL Server. Thanks!
PatrickPatrick
CREATE TABLE TmpStList
(
stFName varchar (10) NOT NULL,
stLName varchar (10) NOT NULL,
stEmail varchar (30) NOT NULL
)
go
The data file (hawk.dat):
"Kelly","Reynold","kelly@.reynold.com"
"John","Smith","bill@.smith.com"
"Sara","Parker","sara@.parker.com"
The format file (hawk.bcp):
8.0
4
1 SQLCHAR 0 1 "\"" 0 first_quote ""
2 SQLCHAR 0 10 "\",\"" 1 stFName ""
3 SQLCHAR 0 10 "\",\"" 2 stLName ""
4 SQLCHAR 0 30 "\"\r\n" 3 stEmail ""
bulk insert TmpStList from 'C:\Staging\hawk.dat'
with (formatfile = 'C:\Staging\hawk.bcp')
select * from TmpStList
stFName stLName stEmail
-- -- --
Kelly Reynold kelly@.reynold.com
John Smith bill@.smith.com
Sara Parker sara@.parker.com
drop table TmpStList
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:CA167741-DE9E-4752-AEE1-97D3A67C3837@.microsoft.com...
> Hi all,
> In Oracle, SQL Loader allows you to use the statement below:
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '|'
> Is there a way to do this with BULK INSERT or some other import function?
> The data files that I am attempting to import has fields terminated by
> commas
> but when some of those text fields may contain a comma, they are also
> enclosed by pipes. The above statement in Oracle deals with this issue
> effectively... hoping I can find something for MS SQL Server. Thanks!
> Patrick
>