[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

Connect to SQL Server Export Table to CSV

Your help would be appreciated with this.

I need to create a VB Script that connects to an sql server and exports a table to csv. Examples or a basic script i can modify would be good.

Not sure where to start.
0
dion_p1
Asked:
dion_p1
  • 6
  • 4
  • 2
  • +2
1 Solution
 
Lee SavidgeCommented:
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
0
 
EDDYKTCommented:
use ADO recordset and getstring
0
 
Shiju SasidharanAssoc Project ManagerCommented:
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Shiju SasidharanAssoc Project ManagerCommented:
0
 
Dany BalianCTOCommented:
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
0
 
dion_p1Author Commented:
Can you give me an example connection string please, with password option to get access?
0
 
Dany BalianCTOCommented:

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/

0
 
Dany BalianCTOCommented:
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

0
 
dion_p1Author Commented:
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'
0
 
Dany BalianCTOCommented:
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
0
 
dion_p1Author Commented:
MSG BOX I SET IT RETURNS "ERROR"

Thanks for your Help
0
 
Dany BalianCTOCommented:
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
0
 
dion_p1Author Commented:
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.
0
 
Dany BalianCTOCommented:
The public role is a special database role to which every database user belongs. The public role contains default access permissions for any user who can access the database. This database role cannot be dropped.

you dont need to export as public, u just need to give access to the user to all tables in ur db
the easiest solution is to put the user in the db_datareader group
and if u want him to also write to the db then add him to the db_datawriter group

tell me if this helps,

cheers,

dan
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now