G
Guest
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
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, '' )
CONVERT(varchar(10),@rownum)+replace(@xstring,char(39),'')+char(44)
END
ELSE
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