Solved

Connect to SQL Server Export Table to CSV

Posted on 2006-11-07
14
357 Views
Last Modified: 2010-04-30
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
Comment
Question by:dion_p1
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 17888468
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 17888492
use ADO recordset and getstring
0
 
LVL 14

Expert Comment

by:Shiju Sasidharan
ID: 17888518
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 14

Expert Comment

by:Shiju Sasidharan
ID: 17888528
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 17889051
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
 

Author Comment

by:dion_p1
ID: 17892856
Can you give me an example connection string please, with password option to get access?
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 17892997

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
 
LVL 11

Expert Comment

by:Dany Balian
ID: 17893069
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
 

Author Comment

by:dion_p1
ID: 17903091
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
 
LVL 11

Expert Comment

by:Dany Balian
ID: 17904393
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
 

Author Comment

by:dion_p1
ID: 17904582
MSG BOX I SET IT RETURNS "ERROR"

Thanks for your Help
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 17904685
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
 

Author Comment

by:dion_p1
ID: 17909757
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
 
LVL 11

Accepted Solution

by:
Dany Balian earned 500 total points
ID: 17912246
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

Gigs: Get Your Project Delivered by an Expert

Select from 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question