hi folks

I went thru some major pains with SQL Srvr trying to import a CSV greater
than 255 columns (limitation with MS TXT/CSV  driver) and had to write my
own sp_OA file reader, parsing routines (views), etc..

I'm down to one last issue. I need to get:

 '(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)'

to replace the first character on the split, IF IT's a COMMA with a '|'
(adding to the expression above).

Expresso testing seems to indicate everything is a go as far as the
expression goes, but I need this enhancement (to take care of embedded
commas in cells).

Expresso input:

37,"'AB","'CDE","'FG","'( (4.0L 4X2,4X4 AT L/OIL
COOLER)",NE01,"'030002","'001",,,,,W,XAGP5695,*,0,0,1,1,1,1,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,X,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,
Here's the TSQL code:
====================
DECLARE @rownum int
 DECLARE @rowtext varchar(8000)
 DECLARE @xstring varchar(8000)
 DECLARE @xpos SMALLINT
 DECLARE @xcount SMALLINT
 DECLARE @xlenString SMALLINT
 DECLARE @sDelim varchar(5)
 DECLARE @eDelim varchar(5)
 DECLARE @regex integer

 set @sDelim = char(44)+char(39)+char(34) --[,'"]
 --print @sDelim
 --set @eDelim = char(34)+char(39)+char(44) --["',]
 --print @eDelim
 SET @regex = dbo.regexObj( '(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)', 0, 1 );

 --DECLARE a1159_Cursor CURSOR FAST_FORWARD FOR SELECT rownum,rowtext from
VIEW_1159Step2 where rownum<85085 ORDER BY rownum
 DECLARE a1159_Cursor CURSOR FAST_FORWARD FOR SELECT rownum,rowtext from
VIEW_1159Step2 ORDER BY rownum
 OPEN a1159_cursor FETCH NEXT FROM a1159_cursor INTO @rownum,@rowtext

 WHILE @@FETCH_STATUS = 0
 BEGIN
-- double quote checker
        If dbo.cnt_strings(@sDelim,@rowtext)>0
BEGIN
            SET @xstring= dbo.regexObjReplace( @regex, @rowtext, '' )
    PRINT
CONVERT(varchar(10),@rownum)+replace(@xstring,char(39),'')+char(44)
END
ELSE
    PRINT
CONVERT(varchar(10),@rownum)+char(124)+replace(@rowtext,char(44),char(124))+
char(124)
    FETCH NEXT FROM a1159_cursor INTO @rownum,@rowtext
 END
--delete from tmp1159
 CLOSE a1159_cursor
 DEALLOCATE a1159_cursor
==============
thanks
Rob