SteveS
Posts: 41
|
| Posted: 05/09/2004, 9:10 AM |
|
Hi all,
I've been searching for a simple solution to export an Access 2000 query to a CSV file. There are a few references on Google etc, however, I'm having problems converting them to work with the CC code.
The query is called "IT_List" and needs to be exported to the root of the C: drive to a file called "IT_List_Exported.CSV".
Any code would be most welcome. If anyone has done this with their own app then I would be grateful for your assistance.
I am always willing to share my code snippets with anyone if you would like to swap ideas etc (just done a great import from csv file routine).
Thanks as always.
Steve.
|
 |
 |
peterr
Posts: 5971
|
| Posted: 05/09/2004, 4:57 PM |
|
Hi,
I think that you could put the code that you found on Google in the "After Initialize" event of your page. That way when the page is opened the export will happen.
I'm not sure though if you want the file to be exported to the C: drive on the server?
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
SteveS
Posts: 41
|
| Posted: 05/10/2004, 6:03 AM |
|
Thanks for your comment Peter
The code I'm working on is as follows:
It works by asking you to select a table (then columns, then records). Once selected it saves the csv file. My problem is that I want a particular table to be exported so I don't need the script to ask me what I want. I have tried changing the code, however, I have been unsuccessful and just get lots of errors.
Has anyone got any ideas on how I can modify this code to simply export all of Table "A" to a csv?
Thanks all!
<% Option Explicit
Dim DSNtemp,Conn,RS,action,arrTables,intTable,i,j,x,y,strFields,objFSO,objFile,strLine
'Insert your own DSN info here
DSNtemp="Provider=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("./db_dsn.mdb") & ";"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open DSNtemp,"admin",""
action = Request("action")
If action = "" Then
Set RS = Conn.OpenSchema(20) '--> adSchemaTables = 20
RS.Filter = "TABLE_TYPE = 'TABLE'"
Response.Write "<form action=""" & Request.ServerVariables("SCRIPT_NAME") & "?action=getfields"" method=""POST"">" & VbCrLf
Response.Write "Select table(s):<BR>" & VbCrLf
Do While Not RS.EOF
Response.Write "<input type=""checkbox"" name=""tables"" value=""" & RS(2) & """>" & RS(2) & "<BR>" & VbCrLf
RS.MoveNext
Loop
Response.Write "<BR><input type=""submit"" value=""Next >>"">"
RS.Close
Set RS = Nothing
End If
If action = "getfields" Then
arrTables = Split(Replace(Request("tables")," ",""), ",")
Response.Write "<form action=""" & Request.ServerVariables("SCRIPT_NAME") & "?action=getrecords"" method=""POST"">" & VbCrLf
Response.Write "<input type=""hidden"" name=""tables"" value=""" & Join(arrTables, ",") & """>" & VbCrLf
Response.Write "<input type=""hidden"" name=""next"" value=""0"">" & VbCrLf
Response.Write "Select field(s):<BR>" & VbCrLf
For i = LBound(arrTables) to UBound(arrTables)
Response.Write "Table: " & arrTables(i) & "<BR>" & VbCrLf
Set RS = Conn.Execute("SELECT * FROM " & arrTables(i))
For j = 0 to RS.Fields.Count-1
Response.Write "<input type=""checkbox"" name=""" & arrTables(i) & """ value=""" & RS.Fields(j).Name & """>" & RS.Fields(j).Name & "<BR>" & VbCrLf
Next
RS.Close
Response.Write "<input type=""checkbox"" name=""" & arrTables(i) & """ value=""*"">All Fields<BR>" & VbCrLf
Response.Write "<BR>"
Next
Response.Write "<input type=""submit"" value=""Next >>"">"
Set RS = Nothing
End If
If action = "getrecords" And Not Request("next") = "end" Then
arrTables = Split(Request("tables"), ",")
intTable = Request("next")
If Instr(Request(arrTables(intTable)),"*") = 0 Then strFields = Request(arrTables(intTable)) Else strFields = "*"
Response.Write "<form action=""" & Request.ServerVariables("SCRIPT_NAME") & "?action=getrecords"" method=""POST"">" & VbCrLf
Response.Write "<input type=""hidden"" name=""tables"" value=""" & Request("tables") & """>" & VbCrLf
Response.Write "<input type=""hidden"" name=""table"" value=""" & arrTables(intTable) & """>" & VbCrLf
For i = LBound(arrTables) to UBound(arrTables)
Response.Write "<input type=""hidden"" name=""" & arrTables(i) & """ value=""" & Request(arrTables(i)) & """>" & VbCrLf
Next
If intTable >= 1 Then
For i = 0 to intTable-1
Response.Write "<input type=""hidden"" name=""" & arrTables(i) & "_rec"" value=""" & Replace(Request(arrTables(i) & "_rec")," ", "") & """>" & VbCrLf
Next
End If
If intTable+1 <= UBound(arrTables) Then
Response.Write "<input type=""hidden"" name=""next"" value=""" & intTable+1 & """>" & VbCrLf
Else
Response.Write "<input type=""hidden"" name=""next"" value=""end"">" & VbCrLf
End If
Response.Write "Table: " & arrTables(intTable) & "<BR>" & VbCrLf
Response.Write "Fields: " & strFields & "<BR><BR>" & VbCrLf
Response.Write "Select record(s):<BR>" & VbCrLf
j = 0
Set RS = Conn.Execute("SELECT " & Request(arrTables(intTable)) & " FROM " & arrTables(intTable))
Do While Not RS.EOF
If Instr(Request(arrTables(intTable)), ",") > 0 or Request(arrTables(intTable)) = "*" Then
Response.Write "<input type=""checkbox"" name=""" & arrTables(intTable) & "_rec"" value=""" & j & """>" & Left(RS(0),10) & "," & Left(RS(1),10) & "<BR>" & VbCrLf
Else
Response.Write "<input type=""checkbox"" name=""" & arrTables(intTable) & "_rec"" value=""" & j & """>" & Left(RS(0),10) & "<BR>" & VbCrLf
End If
RS.MoveNext
j = j + 1
Loop
Response.Write "<input type=""checkbox"" name=""" & arrTables(intTable) & "_rec"" value=""ALL"">All records<BR>" & VbCrLf
Response.Write "<BR><input type=""submit"" value=""Next >>"">"
RS.Close
Set RS = Nothing
End If
If action = "getrecords" and Request("next") = "end" Then
Dim arrRecs,strOutput
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
arrTables = Split(Request("tables"), ",")
strOutput = Server.MapPath(".") & "\" '<-- Edit this to change your output directory
For i = LBound(arrTables) to UBound(arrTables)
Set objFile = objFSO.CreateTextFile(strOutput & Trim(arrTables(i)) & ".csv")
Set RS = Conn.Execute("SELECT " & Request(arrTables(i)) & " FROM " & arrTables(i))
strLine = ""
If Instr(Request(arrTables(i)),"*") = 0 Then
objFile.WriteLine Replace(Request(arrTables(i)), " ", "")
Else
For j = 0 to RS.Fields.Count-1
strLine = strLine & RS.Fields(j).Name
If j < RS.Fields.Count-1 Then strLine = strLine & ","
Next
objFile.WriteLine strLine
End If
If Instr(Request(arrTables(i) & "_rec"), "ALL") <> 0 Then
Do While Not RS.EOF
strLine = ""
For j = 0 to RS.Fields.Count-1
If Not IsNull(RS(j)) Then strLine = strLine & Chr(34) & Replace(RS(j), Chr(34), Chr(34) & Chr(34)) & Chr(34)
If j < RS.Fields.Count-1 Then strLine = strLine & ","
Next
objFile.WriteLine strLine
RS.MoveNext
Loop
Else
arrRecs = Split(Replace(Request(arrTables(i) & "_rec")," ",""),",")
x = 0
y = 0
Do While Not RS.EOF
strLine = ""
If Not x > UBound(arrRecs) Then
If y = Int(arrRecs(x)) Then
For j = 0 to RS.Fields.Count-1
If Not IsNull(RS(j)) Then strLine = strLine & Chr(34) & Replace(RS(j), Chr(34), Chr(34) & Chr(34)) & Chr(34)
If j < RS.Fields.Count-1 Then strLine = strLine & ","
Next
objFile.WriteLine strLine
x = x + 1
End If
End If
y = y + 1
RS.MoveNext
Loop
End If
objFile.Close
Set objFile = Nothing
Next
Response.Write "Done.<BR>" & VbCrLf
For i = LBound(arrTables) to UBound(arrTables)
Response.Write "Created: " & Server.MapPath(".") & "\" & Trim(arrTables(i)) & ".csv" & "<BR>" & VbCrLf
Next
End If
%>
</body>
|
|
|
|
pcfountain
Posts: 35
|
| Posted: 05/10/2004, 9:33 AM |
|
Hey Steve,
In Access, you can create a Macro which exports a table to whatever format you want, using the "TrasferText" macro command. Basically you export the table once by hand and create a "specification" for exporting. Then you can do it in a Macro. Once the Access Macro is created, I believe there is a way to fire it straight from a SQL statement you send over the ODBC connection. (I would have to read up on my Access help to see exactly how.) This would reduce your hundreds of lines of code to about 3. Hopefully I am right about that. It's worth a shot.
_________________
Paul Fountain
http://www.pcfountain.com |
|
|
|
SteveS
Posts: 41
|
| Posted: 05/10/2004, 11:20 AM |
|
Hi Paul,
Thanks for your reply.
Not that I am an expert, however, having spent many hours messing arround with these routines and posting questions on forums I have found that Access macros cannot be accessed from outside of Access itself, therefore, you have to write SQL code using ASP! I wish it were easier! (you can use VB, however, I'n no VB expert either!)
Regards, Steve.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/10/2004, 12:17 PM |
|
The code you have is about 8-10 times bigger than it should be, probably because it displays some HTML and asks users to select something.
I'm not sure how familiar you are with ASP or CCS but the following two examples contain 75% of what you need: http://docs.codecharge.com/studio/html/ProgrammingTechn...eCustomSQL.html
http://docs.codecharge.com/studio/html/ProgrammingTechn...leDBValues.html
(just being able to connect to the database within the program and read database records).
If you can understand/follow the above examples then I can provide additional information on using them to create CSV. Otherwise someone would probably need to write the full code for you.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
