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 Well-Known Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    175
    #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 Well-Known Member

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

    ccoonen Active Member

    Messages:
    1,594
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    90
    #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 Well-Known Member

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

    ludwig Well-Known Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    175
    #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 Well-Known Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    175
    #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

    Code (Text):
    1.  
    2. <%
    3. Response.ContentType = "application/vnd.ms-excel"
    4. set rsPhones=Server.CreateObject("ADODB.Recordset")
    5. strSQL="SELECT * FROM phones ORDER BY id ASC"
    6. rsPhones.Open strSQL, Global_DBConnection
    7. %>
    8. 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
    9. <%
    10. rsPhones.MoveFirst
    11. do while not rsPhones.EOF
    12.  
    13.     str1 = rsPhones("general1")
    14.     if str1<>"" then
    15.         str1=replace(str1, vbcrlf,"")
    16.         str1=replace(str1, ",",".")
    17.     end if
    18.  
    19.     str2 = rsPhones("general2")
    20.     if str2<>"" then
    21.         str2=replace(str2, vbcrlf,"")
    22.         str2=replace(str2, ",",".")
    23.     end if
    24.  
    25.     str3 = rsPhones("size1")
    26.     if str3<>"" then
    27.         str3=replace(str3, vbcrlf,"")
    28.         str3=replace(str3, ",",".")
    29.     end if
    30.  
    31.     str4 = rsPhones("size2")
    32.     if str4<>"" then
    33.         str4=replace(str4, vbcrlf,"")
    34.         str4=replace(str4, ",",".")
    35.     end if
    36.  
    37.     str5 = rsPhones("display1")
    38.     if str5<>"" then
    39.         str5=replace(str5, vbcrlf,"")
    40.         str5=replace(str5, ",",".")
    41.     end if
    42.  
    43.     str6 = rsPhones("display2")
    44.     if str6<>"" then
    45.         str6=replace(str6, vbcrlf,"")
    46.         str6=replace(str6, ",",".")
    47.     end if
    48.  
    49.     str7 = rsPhones("ringtones1")
    50.     if str7<>"" then
    51.         str7=replace(str7, vbcrlf,"")
    52.         str7=replace(str7, ",",".")
    53.     end if
    54.  
    55.     str8 = rsPhones("ringtones2")
    56.     if str8<>"" then
    57.         str8=replace(str8, vbcrlf,"")
    58.         str8=replace(str8, ",",".")
    59.     end if
    60.  
    61.     str9 = rsPhones("memory1")
    62.     if str9<>"" then
    63.         str9=replace(str9, vbcrlf,"")
    64.         str9=replace(str9, ",",".")
    65.     end if
    66.  
    67.     str10 = rsPhones("memory2")
    68.     if str10<>"" then
    69.         str10=replace(str10, vbcrlf,"")
    70.         str10=replace(str10, ",",".")
    71.     end if
    72.  
    73.     str11 = rsPhones("memory3")
    74.     if str11<>"" then
    75.         str11=replace(str11, vbcrlf,"")
    76.         str11=replace(str11, ",",".")
    77.     end if
    78.  
    79.     str12 = rsPhones("OS")
    80.     if str12<>"" then
    81.         str12=replace(str12, vbcrlf,"")
    82.         str12=replace(str12, ",",".")
    83.     end if
    84.  
    85.     str13 = rsPhones("features1")
    86.     if str13<>"" then
    87.         str13=replace(str13, vbcrlf,"")
    88.         str13=replace(str13, ",",".")
    89.     end if
    90.  
    91.     str14 = rsPhones("features2")
    92.     if str14<>"" then
    93.         str14=replace(str14, vbcrlf,"")
    94.         str14=replace(str14, ",",".")
    95.     end if
    96.  
    97.     str15 = rsPhones("features3")
    98.     if str15<>"" then
    99.         str15=replace(str15, vbcrlf,"")
    100.         str15=replace(str15, ",",".")
    101.     end if
    102.  
    103.     str16 = rsPhones("features4")
    104.     if str16<>"" then
    105.         str16=replace(str16, vbcrlf,"")
    106.         str16=replace(str16, ",",".")
    107.     end if
    108.  
    109.     str17 = rsPhones("features5")
    110.     if str17<>"" then
    111.         str17=replace(str17, vbcrlf,"")
    112.         str17=replace(str17, ",",".")
    113.     end if
    114.  
    115.     str18 = rsPhones("features6")
    116.     if str18<>"" then
    117.         str18=replace(str18, vbcrlf,"")
    118.         str18=replace(str18, ",",".")
    119.     end if
    120.  
    121.     str19 = rsPhones("features7")
    122.     if str19<>"" then
    123.         str19=replace(str19, vbcrlf,"")
    124.         str19=replace(str19, ",",".")
    125.     end if
    126.  
    127.     str20 = rsPhones("features8")
    128.     if str20<>"" then
    129.         str20=replace(str20, vbcrlf,"")
    130.         str20=replace(str20, ",",".")
    131.     end if
    132.  
    133.     str21 = rsPhones("features9")
    134.     if str21<>"" then
    135.         str21=replace(str21, vbcrlf,"")
    136.         str21=replace(str21, ",",".")
    137.     end if
    138.  
    139.     str22 = rsPhones("features10")
    140.     if str22<>"" then
    141.         str22=replace(str22, vbcrlf,"")
    142.         str22=replace(str22, ",",".")
    143.     end if
    144.  
    145.     str23 = rsPhones("battery")
    146.     if str23<>"" then
    147.         str23=replace(str23, vbcrlf,"")
    148.         str23=replace(str23, ",",".")
    149.     end if
    150.  
    151.     str24 = rsPhones("battery1")
    152.     if str24<>"" then
    153.         str24=replace(str24, vbcrlf,"")
    154.         str24=replace(str24, ",",".")
    155.     end if
    156.  
    157.     str25 = rsPhones("battery2")
    158.     if str25<>"" then
    159.         str25=replace(str25, vbcrlf,"")
    160.         str25=replace(str25, ",",".")
    161.     end if
    162.  
    163.     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
    164. rsPhones.MoveNext
    165. loop
    166. rsPhones.Close
    167. %>
    168.  
    169.  
     
    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 Peon

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

    ludwig Well-Known Member

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

    yugolancer Peon

    Messages:
    312
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #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