Function DBTable2CSV(CSVFull, DSN, SQL)
' Database -- Convert to Comma-Delimited File. This page demonstrates the capabilities how to write an ASCII comma-delimited file from a SQL statement.
' Returns 0, 1 or -1
' 0 if table is empty
' 1 export success
' -1 error in exporting
whichname = CSVFull ' "/upload/tests/authors.txt"
myDSN = DSN ' "DSN=Student;uid=student;pwd=magic"
mySQL = SQL ' "select * from authors where au_id <100"
showblank = ""
shownull = " <null >"
linestart = chr(34)
lineend = chr(34)
delimiter = chr(34) & "," & chr(34)
delimitersub = ""
Rett = 0
whichFN = server.mappath(whichname)
Set fstemp = server.CreateObject("Scripting.FileSystemObject")
Set filetemp = fstemp.CreateTextFile(whichFN, true)
' true = file can be over-written if it exists
' false = file CANNOT be over-written if it exists
set conntemp = server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp = conntemp.execute(mySQL)
If rstemp.eof then ' this code detects if data is empty
'DBTable2CSV = Rett
'Exit Function
Else
DO UNTIL rstemp.eof
thisline = linestart
For each whatever in rstemp.fields
thisfield = whatever.value
if isnull(thisfield) then
thisfield= shownull
End if
If trim(thisfield) = "" then
thisfield= showblank
End if
thisfield = replace(thisfield, delimiter, delimitersub)
thisline = thisline & thisfield & delimiter
Next
tempLen = len(thisline)
tempLenDelim = len(delimiter)
thisline = mid(thisline, 1, tempLEN - tempLenDelim ) & lineend
filetemp.WriteLine thisline
rstemp.movenext
LOOP
Rett = -1
If err.number = 0 then Rett = 1
End if
DBTable2CSV = Rett
filetemp.Close
rstemp.close
conntemp.close
set filetemp = nothing
set fstemp = nothing
set rstemp = nothing
set conntemp = nothing
End Function
' Database -- Convert to Comma-Delimited File. This page demonstrates the capabilities how to write an ASCII comma-delimited file from a SQL statement.
' Returns 0, 1 or -1
' 0 if table is empty
' 1 export success
' -1 error in exporting
whichname = CSVFull ' "/upload/tests/authors.txt"
myDSN = DSN ' "DSN=Student;uid=student;pwd=magic"
mySQL = SQL ' "select * from authors where au_id <100"
showblank = ""
shownull = " <null >"
linestart = chr(34)
lineend = chr(34)
delimiter = chr(34) & "," & chr(34)
delimitersub = ""
Rett = 0
whichFN = server.mappath(whichname)
Set fstemp = server.CreateObject("Scripting.FileSystemObject")
Set filetemp = fstemp.CreateTextFile(whichFN, true)
' true = file can be over-written if it exists
' false = file CANNOT be over-written if it exists
set conntemp = server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp = conntemp.execute(mySQL)
If rstemp.eof then ' this code detects if data is empty
'DBTable2CSV = Rett
'Exit Function
Else
DO UNTIL rstemp.eof
thisline = linestart
For each whatever in rstemp.fields
thisfield = whatever.value
if isnull(thisfield) then
thisfield= shownull
End if
If trim(thisfield) = "" then
thisfield= showblank
End if
thisfield = replace(thisfield, delimiter, delimitersub)
thisline = thisline & thisfield & delimiter
Next
tempLen = len(thisline)
tempLenDelim = len(delimiter)
thisline = mid(thisline, 1, tempLEN - tempLenDelim ) & lineend
filetemp.WriteLine thisline
rstemp.movenext
LOOP
Rett = -1
If err.number = 0 then Rett = 1
End if
DBTable2CSV = Rett
filetemp.Close
rstemp.close
conntemp.close
set filetemp = nothing
set fstemp = nothing
set rstemp = nothing
set conntemp = nothing
End Function
CSVFull, DSN, SQL
Views 131
Downloads 69
CodeID
DB ID
ANmarAmdeen
627
Revisions
v1.0
Tuesday
May
31
2022