Connect to SQL Server Export Table to CSV

Hiya,

I don't really have time this minute to write a script but why don't you grab the recordset as you would normally and insert it into an Excel file and tell Excel to save it as a csv file. This assumes that you have Office/Excel on the system.

Regards,

Lee

use ADO recordset and getstring

http://www.thescripts.com/forum/thread530870.html

Code to export access table

http://www.freevbcode.com/ShowCode.asp?ID=5037

here's the full code
just change your sql and connectionstring to the database



on error resume next
dim db, rs
set db= createobject("adodb.connection")
db.open "your connection string here"       '<<<change connection string here
if err.number<> 0 then
      msgbox "Cannot connect to db"
else
      set rs=createobject("adodb.recordset")
      set rs=db.execute ("select prodnum,descript,price from products where isactive=1") ''<<<change SQL here
      if err.number<>0 then msgbox "Error"
      rs.movefirst
      Set fs= CreateObject("Scripting.fileSystemObject")
      Set file = fs.CreateTextFile("c:\exportt.csv",TRUE)     ''<<<change file name here
      while not rs.eof
            'create csv line here
            file.writeline(rs.GetString (,,",",vbcrlf ,""))
            rs.movenext
      wend
file.close
set fs=nothing
end if
rs.close
set rs=nothing
db.close
set db=nothing


hope this helps...

Cheers,

Dan

Can you give me an example connection string please, with password option to get access?

method 1: DSN-less connection
---------------
db.open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=mypassword;"
'if your access database doesnt have a pwd then
db.open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"


Method2: DSN connection
------------
You can also... create a system dsn which is what i recommend...

here's how to:

1. go to control panel > administrative tools > Data sources (ODBC)
2. then click on the System DSN TAB
3. click on ADD
4. Select Microsoft Access Driver (*.MDB)
5. click Finish
6. enter a name for your dsn ex: myodbcname (this will be a sort of shortcut to ur db)
7. enter a description for this dsn (this step is optional)
8. click on the button "SELECT" under database
9. select your database file (it is preferred that the db is on a short path)
10. click on OK

now your connection string would be: "DSN=myodbcname;uid=admin;pwd=mypass;"    'if you have a password
now your connection string would be: "DSN=myodbcname;uid=admin;pwd=;"               'if you dont have a password


for more information on connection strings goto: www.connectionstrings.com

hope i was helpful..

cheers,

dan


http://www.connectionstrings.com/

sorry i missunderstood ur question... i thought u wanted ms access connection string

here's the connection string in case you have an SQL SERVER:

Method 1: DSN-Less connection
--------------

db.open "Driver={SQL Server};Server=MyserverName;Database=databasename;Uid=dbusername;Pwd=dbpass;"

Method 2: DSN Connection
-----------------

create the dsn like above... this time select sql server as driver.
follow the wizard till the end...
u will have to know if the authentication method for sql server is windows authentication or sql server authentication.

connection string is the same: "DSN=myodbcname;uid=admin;pwd=mypass;"

cheers,

dan

Thanks...

I tried the above and found that when i try to export table it Give a MSG Box saying "Error"

I have done some investigating and found that i actually want to export a view thats inside the database called V_ST.

I went to design view of the database and have copied the select script from it.....

Hopefully this helps as to what i need to do...

SELECT     ST.STKEY, ST.SURNAME + ' ' + ST.PREF_NAME + ' ' + ST.SCHOOL_YEAR + ' ' + ST.HOME_GROUP AS STUDENT,
                      CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))
                      + ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))
                      + ' ' + DF.SURNAME_B WHEN ST.CONTACT_A = 'N' THEN NULL
                      ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))
                      + ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))
                      + ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A <> DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))
                      + ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND
                      DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND
                      DF.SURNAME_A IS NOT NULL THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1)) + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND
                      DF.SURNAME_B IS NULL THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT1,
                      CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN
                       ST.CONTACT_A = 'N' THEN NULL
                      ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A
                       <> DF.SURNAME_B THEN DF.TITLE_A + ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND
                      DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND DF.SURNAME_A IS NOT NULL
                      THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND DF.SURNAME_B IS NULL
                      THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT2, ST.SURNAME, ST.FIRST_NAME, ST.SECOND_NAME, ST.PREF_NAME,
                      ST.TITLE, ST.AB_STUDY, ST.ED_ALLOW AS EMA, ST.BIRTHDATE, ST.CAMPUS, ST.ENTRY, ST.GENDER, ST.HOME_GROUP, ST.HOME_LANG,
                      ST.HOUSE, ST.KOORIE, ST.NOTES, ST.RELATION_A01, ST.SCHOOL_YEAR, ST.FAM_ORDER, KCY.DESCRIPTION, ST.STATUS, ST.STUDENT_PIC,
                      ST.TAG, ST.YOUTH_ALLOW, ST.FAMILY, DF.DFKEY, DF.SURNAME_A, DF.NAME_A, DF.TITLE_A, DF.E_MAIL_A, DF.SURNAME_B, DF.NAME_B,
                      DF.E_MAIL_B, DF.TITLE_B, DF.MAILKEY, DF.NO_STUDENTS, UM.UMKEY, UM.ADDRESS01, UM.ADDRESS02, UM.ADDRESS03, UM.POSTCODE,
                      UM.STATE, UM.TELEPHONE, UM.FAX
FROM         (C21015374..ST AS ST INNER JOIN
                      C21015374..KCY AS KCY ON ST.SCHOOL_YEAR = KCY.KCYKEY) INNER JOIN
                      C21015374..DF AS DF ON DF.DFKEY = ST.FAMILY INNER JOIN
                      C21015374..UM AS UM ON DF.MAILKEY = UM.UMKEY
WHERE     ST.STATUS <> 'LEFT'

what's the error code u're getting? is it a runtime error? or a msgbox that u set!!
please give us some code...
the sql of the view is not needed for now...

cheers,

dan

MSG BOX I SET IT RETURNS "ERROR"

Thanks for your Help

can you try:
if you'r catching the error using if err.number <>0 then please use instead:
msgbox "Error: " & err.number & vbcrlf & err.description

tell me what u get!


and try this sql:

Dim sql As String
sql = "SELECT ST.STKEY, ST.SURNAME + ' ' + ST.PREF_NAME + ' ' + ST.SCHOOL_YEAR + ' ' + ST.HOME_GROUP AS STUDENT,"
sql = sql & " CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_B WHEN ST.CONTACT_A = 'N' THEN NULL"
sql = sql & " ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))"
sql = sql & " + ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A <> DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND"
sql = sql & " DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND"
sql = sql & " DF.SURNAME_A IS NOT NULL THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1)) + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND"
sql = sql & " DF.SURNAME_B IS NULL THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT1,"
sql = sql & " CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN"
sql = sql & " ST.CONTACT_A = 'N' THEN NULL"
sql = sql & " ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A"
sql = sql & " <> DF.SURNAME_B THEN DF.TITLE_A + ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND"
sql = sql & " DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND DF.SURNAME_A IS NOT NULL"
sql = sql & " THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND DF.SURNAME_B IS NULL"
sql = sql & " THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT2, ST.SURNAME, ST.FIRST_NAME, ST.SECOND_NAME, ST.PREF_NAME,"
sql = sql & " ST.TITLE, ST.AB_STUDY, ST.ED_ALLOW AS EMA, ST.BIRTHDATE, ST.CAMPUS, ST.ENTRY, ST.GENDER, ST.HOME_GROUP, ST.HOME_LANG,"
sql = sql & " ST.HOUSE, ST.KOORIE, ST.NOTES, ST.RELATION_A01, ST.SCHOOL_YEAR, ST.FAM_ORDER, KCY.DESCRIPTION, ST.STATUS, ST.STUDENT_PIC,"
sql = sql & " ST.TAG, ST.YOUTH_ALLOW, ST.FAMILY, DF.DFKEY, DF.SURNAME_A, DF.NAME_A, DF.TITLE_A, DF.E_MAIL_A, DF.SURNAME_B, DF.NAME_B,"
sql = sql & " DF.E_MAIL_B, DF.TITLE_B, DF.MAILKEY, DF.NO_STUDENTS, UM.UMKEY, UM.ADDRESS01, UM.ADDRESS02, UM.ADDRESS03, UM.POSTCODE,"
sql = sql & " UM.STATE , UM.TELEPHONE, UM.FAX"
sql = sql & " FROM (C21015374..ST AS ST INNER JOIN"
sql = sql & " C21015374..KCY AS KCY ON ST.SCHOOL_YEAR = KCY.KCYKEY) INNER JOIN"
sql = sql & " C21015374..DF AS DF ON DF.DFKEY = ST.FAMILY INNER JOIN"
sql = sql & " C21015374..UM AS UM ON DF.MAILKEY = UM.UMKEY"
sql = sql & " WHERE     ST.STATUS <> 'LEFT'"



cheers,
dan

ok i have returned back to trying the original v_st exporting the view as though it were a table.
the error i get is saying that the user i am using is not a database owner.

I checked the user and can see it's got public access

How can i export as the public user without actually changing any settings in my SQL Server.

I checked this by briefly adding the db_owner to the username properties and it worked.