Export SQL result as CSV

Other SQL Server Topics
1

Hi All,

Can somebody please help?..

here is the problem:-

if OBJECT_ID('tempdb..#test') is not null drop table #test
if OBJECT_ID('tempdb..#test1') is not null drop table #test1
if OBJECT_ID('tempdb..#test2') is not null drop table #test2

create table #test (
						Typex varchar(255),
						Column2 varchar(255),
						Column3 varchar(255),
						Column4 varchar(255),
						Column5 varchar(255),
						Column6 varchar(255),
						Column7 varchar(255),
						Column8 varchar(255),
						Column9 varchar(255),
						Column10 varchar(255)
)

create table #test1 (
						Typex varchar(255),
						Column2 varchar(255),
						Column3 varchar(255),
						Column4 varchar(255),
						Column5 varchar(255),
						Column6 varchar(255),
						Column7 varchar(255),
						Column8 varchar(255),
						Column9 varchar(255),
						Column10 varchar(255)
)

create table #test2 (
						Typex varchar(255),
						Column2 varchar(255),
						Column3 varchar(255),
						Column4 varchar(255),
						Column5 varchar(255),
						Column6 varchar(255),
						Column7 varchar(255),
						Column8 varchar(255),
						Column9 varchar(255),
						Column10 varchar(255)
)

insert into #test values('Type1','C1','C2','C3','C4','C5','C6','C7','C8','C9')
insert into #test1 values('Type2','C1','C2','C3','C4','C5',null,null,null,null)
insert into #test2 values('Type3','C1','C2','C3',Null,Null,NULL,NULL,NULL,NULL)
insert into #test values('Type1','C1',NULL,'C3',NULL,'C5','C6','C7','C8','C9')
insert into #test1 values('Type2','C1',NULL,'C3','C4','C5',null,null,null,null)
insert into #test2 values('Type3',NULL,'C2',NULL,Null,Null,NULL,NULL,NULL,NULL)

			--10 columns
			select * from #test
	union all 
			--6 Columns
			select * from #test1
	union all 
			--4 Columns
			select * from #test2

Result:-

SQL result

When the above is saved as CSV, below is the result

CSV problem

when nulls have been removed it leaves commas for type 2 and type 3 beyond the expected columns
so for Type 1 should have 10 columns, Type 2 should have 6 columns and type 3 should have 4 columns
Nulls removed

this is the Expected result:-
Expected result

Is there a way to achieve the above?
Your help will be greatly apricated.
Many thanks

2

First - your expected results are not consistent with standard CSV formatted files. If someone were to try and parse your expected file it would fail because they would expect 10 fields for every row in the file.

Second - having the word 'NULL' in the results is going to depend on how that data is saved and the tool being used to create the file. Copy/Paste into Excel copies null values as the word NULL - for example.

One way to get your desired results would be to concatenate the columns yourself.

Select concat_ws(',', 'C1', 'C2', 'C3', 'C4', 'C5')
     , concat_ws(',', 'C1', Null, 'C3', Null)
3

here is a csv standard doc. Why would you break that and still want your file to be csv, then it will no longer be csv

4

Thanks all for your help, I have found a solution:

if OBJECT_ID('tempdb..#test3') is not null drop table #test3

select * 
into  #test3
from (
				--10 columns - 9 commas
				select * from #test
		union all 
				--6 Columns - 5 commas
				select * from #test1
		union all 
				--4 Columns - 3 commas
				select * from #test2
)main 


update x
set 
 Column7 = '<<Dummy>>'
,Column8 = '<<Dummy>>'
,Column9 = '<<Dummy>>'
,Column10 = '<<Dummy>>'
from #test3 x
where Typex = 'Type2'

update x
set 
 Column5 = '<<Dummy>>'
,Column6 = '<<Dummy>>'
,Column7 = '<<Dummy>>'
,Column8 = '<<Dummy>>'
,Column9 = '<<Dummy>>'
,Column10 = '<<Dummy>>'
from #test3 x
where Typex = 'Type3'

result
result708×160 4.56 KB

by simply marking the additional columns which are not required with a flag in this case <<Dummy>>, once saved a CSV you open in notepad and get rid of all commas which are followed by <<Dummy>> and replace null with blank.

5

This is a lot more work than building the output yourself using CONCAT_WS. If you are not on a version that supports CONCAT_WS then another way is to use CONCAT(col1, ',' + col2, ',' + col3, ',' + col4, ...). The end result is a single column that is delimited without the extra commas at the end.

6

what consumes this data at the end? this is no longer csv file. it is some mutant :slight_smile:

7

I have been asking myself the same thing but this was the requirement so be it, its sorted.