1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Export data from MsSQL to a CSV file

Discussion in 'C#' started by ludwig, Aug 16, 2006.

  1. #1
    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
     
    ludwig, Aug 16, 2006 IP
  2. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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.
     
    vectorgraphx, Aug 16, 2006 IP
  3. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #3
    what you think of constructing the tables in HTML and then just copy and paste, I think it'll also work
     
    ludwig, Aug 16, 2006 IP
  4. Carlito

    Carlito Peon

    Messages:
    679
    Likes Received:
    25
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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.
     
    Carlito, Aug 16, 2006 IP
  5. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #5
    I think you are repeating what I wrote above.
    Don't you think so?
     
    ludwig, Aug 16, 2006 IP
  6. ccoonen

    ccoonen Well-Known Member

    Messages:
    1,606
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    160
    #6
    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.
     
    ccoonen, Aug 17, 2006 IP
  7. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #7
    Yes I think it does, but I don't use any third party software.....
     
    ludwig, Aug 17, 2006 IP
  8. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #8
    I finally could figure it out, I created an HTML table :) then just copied and pasted it into an Excel sheet :)

    thank you guys
     
    ludwig, Aug 18, 2006 IP
  9. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #9
    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):
     
    ludwig, Aug 18, 2006 IP
  10. rahulm

    rahulm Peon

    Messages:
    178
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #10
    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.
     
    rahulm, Aug 19, 2006 IP
  11. tommyonline

    tommyonline Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #11
    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
    :rolleyes:
     
    tommyonline, Nov 8, 2008 IP
  12. yugolancer

    yugolancer Well-Known Member

    Messages:
    320
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    110
    #12
    Is this problem resolved already?
     
    yugolancer, Dec 23, 2008 IP
  13. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #13
    it is after 2 years passed from the original post :)
     
    ludwig, Dec 24, 2008 IP
  14. yugolancer

    yugolancer Well-Known Member

    Messages:
    320
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    110
    #14
    Omg i just noticed the date lol
    But why this guy "tommyonline" posted reply on Nov 8th 2008?
    Adverts (sqlscripter) or what? Weird! :)
     
    yugolancer, Dec 25, 2008 IP
  15. dinomflorist

    dinomflorist Peon

    Messages:
    16
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #15
    use dtswizard mssql
     
    dinomflorist, Dec 29, 2008 IP