CODEWITHSUNDEEP
sqlvbscript
user3814827
user3814827

Reputation: 1

Export column from database table to text file

I have the code below, which copies the contents of a database table to a text file, but I want only one particular column of that table.

Sub OnClick(Byval Item)                 
  Dim rs,fieldVals,dbConnIn
  Dim connectString,shell,tmp,fso,ts,line

  Const adOpenDynamic=2
  Const adLockPessimistic=2
  Const adCmdTable=2
  Const adOpenForwardOnly=0
  Const adOpenStatic=3
  Const adLockReadOnly=1

  'connectString="Provider=SQLOLEDB;Server=192.168.168.4;Database=MYDB;Uid=sa;Pwd=myPassword;"
  connectString="Provider=MSDASQL.1;Persist Security Info=False;Data Source=LocalServer;Initial Catalog=FOX_REPORTS"
  Set dBConnIn = CreateObject("ADODB.Connection")
  dBConnIn.CommandTimeout = 300
  dBConnIn.Open connectString
  ' This is just a simple way of getting a record set from and SQL Query
  Set rs=CreateObject("ADODB.RecordSet")
  rs.Open _
    "DATALOG", _
    dbConnIn, _
    adOpenStatic, _
    adLockReadOnly, _
    adCmdTable

  Set shell=CreateObject("WScript.Shell")
  Set fso=CreateObject("Scripting.FileSystemObject")
  Set ts=fso.OpenTextFile("C:\DATALOG.TXT",2,True)
  line=""
  For Each tmp In rs.Fields
    line=line & tmp.Name & "    "
  Next
  ts.WriteLine Left(line,Len(line)-1)
  While Not rs.EOF
    line=""
    For Each tmp In rs.Fields
      line=line & """" & Replace(tmp.Value,"""","""""") & ""","
    Next
    ts.WriteLine Left(line,Len(line)-1)
    rs.MoveNext
  Wend

  rs.Close
  ts.close
End Sub

Upvotes: 0

Views: 151

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

Replace

rs.Open _
  "DATALOG", _
  dbConnIn, _
  adOpenStatic, _
  adLockReadOnly, _
  adCmdTable

with

rs.Open _
  "SELECT [colname] FROM DATALOG", _
  dbConnIn, _
  adOpenStatic, _
  adLockReadOnly

where colname is the name of the column you want to export.

Upvotes: 1

Related Questions