Export data from MsSQL to a CSV file

Hi guys,

Could you please give me a hint which is the best way to export data from an MsSQL table to a CSV file, or any other file

Thank you in advance

 

well for just a run-of-the-mill csv you could always just step through the recordsets and build your own, like this

while not recordset.eof
response.write field1 & ", " & field2 & ", " & field3 & ", " & field4 & ", " & field5 & "<br />" & vbcrlf
recordset.movenext
wend

and either display that to a browser and c/p it if it's a one-off deal or append it to a file if its a regular occurance via the filesystemobject method.

 

what you think of constructing the tables in HTML and then just copy and paste, I think it'll also work

 

Run the query (SELECT * FROM tablename) to get all the data in Query Analyzer, then copy and paste the results from there into Excel. Then save as csv file type.

 

I think you are repeating what I wrote above.
Don't you think so?

 

i think in EM you can right click on the table you want to export, All tasks > Export Data - and the DTS Manager pops up and allows you to export in many formats.

 

Yes I think it does, but I don't use any third party software.....

 

I finally could figure it out, I created an HTML table then just copied and pasted it into an Excel sheet

thank you guys

 

hi again,

actually I had problems with the content in my site when I had , or ENTERs

so I did the code as follows

<%
Response.ContentType = "application/vnd.ms-excel"
set rsPhones=Server.CreateObject("ADODB.Recordset")
strSQL="SELECT * FROM phones ORDER BY id ASC"
rsPhones.Open strSQL, Global_DBConnection
%>
id,Network,Announced,Dimensions,Weight,Type,Size,Type,Vibration,Phonebook,Call records,Card slot,OS,GPRS,Data speed,Messaging,Clock,Alarm,Infrared port,Games,Colors,Camera,Other features,Battery Type,Stand-by,Talk time
<%
rsPhones.MoveFirst
do while not rsPhones.EOF

	str1 = rsPhones("general1")
	if str1<>"" then
		str1=replace(str1, vbcrlf,"")
		str1=replace(str1, ",",".")
	end if

	str2 = rsPhones("general2")
	if str2<>"" then
		str2=replace(str2, vbcrlf,"")
		str2=replace(str2, ",",".")
	end if

	str3 = rsPhones("size1")
	if str3<>"" then
		str3=replace(str3, vbcrlf,"")
		str3=replace(str3, ",",".")
	end if

	str4 = rsPhones("size2")
	if str4<>"" then
		str4=replace(str4, vbcrlf,"")
		str4=replace(str4, ",",".")
	end if

	str5 = rsPhones("display1")
	if str5<>"" then
		str5=replace(str5, vbcrlf,"")
		str5=replace(str5, ",",".")
	end if

	str6 = rsPhones("display2")
	if str6<>"" then
		str6=replace(str6, vbcrlf,"")
		str6=replace(str6, ",",".")
	end if

	str7 = rsPhones("ringtones1")
	if str7<>"" then
		str7=replace(str7, vbcrlf,"")
		str7=replace(str7, ",",".")
	end if

	str8 = rsPhones("ringtones2")
	if str8<>"" then
		str8=replace(str8, vbcrlf,"")
		str8=replace(str8, ",",".")
	end if

	str9 = rsPhones("memory1")
	if str9<>"" then
		str9=replace(str9, vbcrlf,"")
		str9=replace(str9, ",",".")
	end if

	str10 = rsPhones("memory2")
	if str10<>"" then
		str10=replace(str10, vbcrlf,"")
		str10=replace(str10, ",",".")
	end if

	str11 = rsPhones("memory3")
	if str11<>"" then
		str11=replace(str11, vbcrlf,"")
		str11=replace(str11, ",",".")
	end if

	str12 = rsPhones("OS")
	if str12<>"" then
		str12=replace(str12, vbcrlf,"")
		str12=replace(str12, ",",".")
	end if

	str13 = rsPhones("features1")
	if str13<>"" then
		str13=replace(str13, vbcrlf,"")
		str13=replace(str13, ",",".")
	end if

	str14 = rsPhones("features2")
	if str14<>"" then
		str14=replace(str14, vbcrlf,"")
		str14=replace(str14, ",",".")
	end if

	str15 = rsPhones("features3")
	if str15<>"" then
		str15=replace(str15, vbcrlf,"")
		str15=replace(str15, ",",".")
	end if

	str16 = rsPhones("features4")
	if str16<>"" then
		str16=replace(str16, vbcrlf,"")
		str16=replace(str16, ",",".")
	end if

	str17 = rsPhones("features5")
	if str17<>"" then
		str17=replace(str17, vbcrlf,"")
		str17=replace(str17, ",",".")
	end if

	str18 = rsPhones("features6")
	if str18<>"" then
		str18=replace(str18, vbcrlf,"")
		str18=replace(str18, ",",".")
	end if

	str19 = rsPhones("features7")
	if str19<>"" then
		str19=replace(str19, vbcrlf,"")
		str19=replace(str19, ",",".")
	end if

	str20 = rsPhones("features8")
	if str20<>"" then
		str20=replace(str20, vbcrlf,"")
		str20=replace(str20, ",",".")
	end if

	str21 = rsPhones("features9")
	if str21<>"" then
		str21=replace(str21, vbcrlf,"")
		str21=replace(str21, ",",".")
	end if

	str22 = rsPhones("features10")
	if str22<>"" then
		str22=replace(str22, vbcrlf,"")
		str22=replace(str22, ",",".")
	end if

	str23 = rsPhones("battery")
	if str23<>"" then
		str23=replace(str23, vbcrlf,"")
		str23=replace(str23, ",",".")
	end if

	str24 = rsPhones("battery1")
	if str24<>"" then
		str24=replace(str24, vbcrlf,"")
		str24=replace(str24, ",",".")
	end if

	str25 = rsPhones("battery2")
	if str25<>"" then
		str25=replace(str25, vbcrlf,"")
		str25=replace(str25, ",",".")
	end if

	response.write rsPhones("id") & "," & str1 & "," & str2 & "," & str3 & "," & str4 & "," & str5 & "," & str6 & "," & str7 & "," & str8 & "," & str9 & "," & str10 & "," & str11 & "," & str12 & "," & str13 & "," & str14 & "," & str15 & "," & str16 & "," & str17 & "," & str18 & "," & str19 & "," & str20 & "," & str21 & "," & str22 & "," & str23 & "," & str24 & "," & str25 & vbcrlf
rsPhones.MoveNext
loop
rsPhones.Close
%>

Code (markup):

 

Hi
I think u should create a Excel object using this object u can access all the funcationlity of excel then by loop u can copy ur data from recordset and paste in the excel row , col.

 

Try SQL Scripter to export data to Csv/Text.
You can also export data to Excel or PDF as well as generating Insert/Update/Delete T-SQL data scripts.

http://www.sqlscripter.com

 

Is this problem resolved already?

 

it is after 2 years passed from the original post

 

Omg i just noticed the date lol
But why this guy "tommyonline" posted reply on Nov 8th 2008?
Adverts (sqlscripter) or what? Weird!

 

use dtswizard mssql