Solved

Connect to SQL Server Export Table to CSV

Posted on 2006-11-07
14
351 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
Comment Utility
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
Comment Utility
use ADO recordset and getstring
0
 
LVL 14

Expert Comment

by:Shiju Sasidharan
Comment Utility
0
 
LVL 14

Expert Comment

by:Shiju Sasidharan
Comment Utility
0
 
LVL 11

Expert Comment

by:Dany Balian
Comment Utility
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
Comment Utility
Can you give me an example connection string please, with password option to get access?
0
 
LVL 11

Expert Comment

by:Dany Balian
Comment Utility

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 11

Expert Comment

by:Dany Balian
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
MSG BOX I SET IT RETURNS "ERROR"

Thanks for your Help
0
 
LVL 11

Expert Comment

by:Dany Balian
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now