March 10, 2017 at 6:16 am
DECLARE @table_name varchar(300)= 'CSV_TABLE'
DECLARE @TextQualifier varchar(30) = '"'
DECLARE @Delimiter varchar(30) = ';'
DECLARE @EOLDelimiter varchar(30) = char(13)+char(10)
DECLARE @To_Quote varchar(30) = '%['+@Delimiter+@textQualifier+@EOLDelimiter+']%'
DECLARE @work VARCHAR(8000)
DECLARE @Lijstje VARCHAR(max) = ''
DECLARE @qs varchar(8000) = ''''+@DELIMITER+@TextQualifier+'''+REPLACE( CONVERT(VARCHAR(MAX),[<COLUMN_NAME>]),'''+@TextQualifier+''','''+@TextQualifier+@TextQualifier+''')+'''+@TextQualifier+''''
DECLARE @Ns varchar(8000) = ''''+@DELIMITER+'''+COALESCE(CONVERT(VARCHAR(MAX),[<COLUMN_NAME>]),'''')'
DECLARE @SS varchar(4000) = '+CASE WHEN [<COLUMN_NAME>] LIKE '''+@To_Quote+''' THEN ' +@QS+' ELSE ' +@ns+ ' END'
select @Lijstje = @Lijstje + REPLACE(@SS, '<Column_name>', Column_name) FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table_name
set @work = 'SELECT RIGHT(''0000000''+convert(varchar(6), ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY (SELECT NULL))),8) '+@Lijstje+' FROM '+@table_name
EXEC (@work) -- Print @work -- use print to inspect the generated script.
Dear Reader,
With the above code I a have tried to make a fairly minimalistic CSV generator. When run in SSMS you get a CSV tekst.
Ben
March 10, 2017 at 6:23 am
Dear Reader,
With the above code I a have tried to make a fairly minimalistic CSV generator. When run in SSMS you get a CSV tekst.
It is not perfect, but the features are :
Fields with special characters get qouted with the TextQualifier.
TextQualifiers get escaped in the field. (Two TextQualifiers).
Only 'special' fields are qouted.
EOL characters can appear in fields.
Textqualifier, delimitor, EOLdelimitor and other special characters can be choosen.
It is simple to add extra characters as 'special' characters so that these field get quoted as wel.
Output can be done on screen and used, or directly to a file.
This works on tables and views, with a view any adaptation can easely be made.
Output of the file is in UTF-8 format.
Compact, (UTF-8, no unneccesary qouting'). (In general about the size of the table, when zipped, a lot smaller).
The script can be run directly or can be printed for further modification.
Easely extendable. (By using a view on the source for alterations adding an extra field or removing a field or changes to a field)
First field is a linenumber. (On a target system this can be ignored or deleted. Alteration of the script or the generated script can remove this linenumber).
Not perfect :
The file starts of with an empty line.
The second line is a repeated number of hashes.
The file ends with 2 empty lines. (There should be only one).
From within a stored procedure the result can not easely be transferred to a file.
I do not know how different collations are handled, or how extremely long fields are handled.
So I am publishing this, because it might be usefull.
Is this something to work further on ?
Any solutions to the non perfect parts ?
Suggestions are extremely welcome ?
Any collaboration on this ?
Below the signature a table which can serve for testing.
Reason for working on the minimalistic version is that I am also working on a more extensive version. But that is work in progress.
The extensive version can be used from within a stored procedure and write to a file without the two first lines.
Alterations for testing:
DECLARE @SS varchar(4000) = '+CASE WHEN <COLUMN_NAME> LIKE '''+@To_Quote+''' THEN ' +@QS+' ELSE ' +@ns+ ' END'
-- Can be changed to
DECLARE @SS varchar(4000) = ',CASE WHEN <COLUMN_NAME> LIKE '''+@To_Quote+''' THEN ' +@QS+' ELSE ' +@ns+ ' END'
-- This wil give a result were each field is handled on it's own. For example in table format to study certain effects.
EXEC (@work) -- Print @work -- use print to inspect the generated script.
-- Can be changed to
Print @work -- use print to inspect the generated script.
-- To see the generation script.
DECLARE @EOLDelimiter varchar(30) = char(13)+char(10)
-- Can be changed to
DECLARE @EOLDelimiter varchar(30) = '<EOL>'
-- To get a better understanding of the End Of Line characters or markers.
Ben
An example table containing different testing situations for the CSV script:
---------------------------------------------------------------------------------
-- Minimalistic CSV_Table --
---------------------------------------------------------------------------------
EXEC sp_drop CSV_TABLE
CREATE TABLE CSV_TABLE(
comment varchar(300),
longtxt varchar(max),
fl float,
lint bigint,
dt datetime,
dt2 datetime2,
d date,
t time
)
-- SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))XXX(N)
insert into CSV_TABLE SELECT * FROM (VALUES
('A ''first'' line.','xyz0',1.123,12345678,getdate(),getdate(),getdate(),getdate())
, ('Single qoute' ,'This is a tekst with a single quote before '' this word.',1.123,12345678,getdate(),getdate(),getdate(),getdate())
, ('Single double qoute' ,'This is a tekst with a single double quote before " this word.',1.123,12345678,getdate(),getdate(),getdate(),getdate())
, ('Single comma' ,'This is a tekst with a single comma before , this word.',1.123,12345678,getdate(),getdate(),getdate(),getdate())
, ('Single semicolon' ,'This is a tekst with a semicolon before ; this word.',1.123,12345678,getdate(),getdate(),getdate(),getdate())
, ('Comma semicolon' ,'This is a tekst, with a semicolon before ; this word.',1.123,12345678,getdate(),getdate(),getdate(),getdate())
, ('time','xyz1',1.123,12345678,'1999-09-09 19:19:19.789','1999-09-09 19:19:19.789','1999-09-09 19:19:19.789','1999-09-09 19:19:19.789')
, ('Long text with everything','xyz2',1.123,12345678,getdate(),getdate(),getdate(),getdate())
, ('Long text with everything','xyz3',1.123,12345678,getdate(),getdate(),getdate(),getdate())
, ('Long text with everything','xyz4',1.123,12345678,getdate(),getdate(),getdate(),getdate())
, ('Long text with everything','xyz5',1.123,12345678,getdate(),getdate(),getdate(),getdate())
) xxx (A,B,C,D,E,F,G,H)
UPDATE CSV_TABLE SET
longtxt =
'Hello dear Reader,
This is a "tex" containing an number of special
characters.
For example; a comma, a semicolon.
Also six qoutes in a row '''''''''''' here
Also six double qoutes in a row """""" here'
WHERE longtxt = 'xyz1'
UPDATE CSV_TABLE SET
longtxt =
'
Few empty lines
Hello dear Reader,
This is a "tex" containing an number of special
characters.
For example; a comma, a semicolon.
Also five qoutes in a row '''''''''' here
Also five double qoutes in a row """"" here'
WHERE longtxt = 'xyz2'
UPDATE CSV_TABLE SET
longtxt =
'
Few empty lines
Hello dear Reader,
This is a "tex" containing an number of special
characters.
For example; a comma, a semicolon.
Also five qoutes in a row '''''''''' here
Also five double qoutes in a row """"" here'
WHERE longtxt = 'xyz3'
;
WITH
L0 AS(SELECT 0 AS c UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), -- 4
L1 AS(select 0 as x from L0 A, L0 B, L0 C, L0 D), -- 4 ^4 = 256
L9 AS(Select *, row_number() OVER(PARTITION BY x order by x )-1 as nr from L1), -- voeg rijnummers toe
I AS(select 'Special Char' TEKST1,
'Dit is char nr :'+RIGHT('000'+convert(varchar(6),NR),3)+'>>>'+char(nr)+'<<< ' TEKST,1 x ,1y,GETDATE() a,GETDATE()b,GETDATE()c,GETDATE()d FROM L9)
insert into CSV_TABLE select * from I
SELECT * FROM CSV_TABLE
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply