Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Suggest forum.snitz.com Content
 Request: Report Generator MOD		  New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

padawan
Junior Member

200 Posts
Posted - 14 October 2002 :  09:03:14  Show Profile

To MOD authors:

Hey there fellas! Are any of you working on a report generator/exporter MOD? It will be really nice to see an ADMIN OPTIONS component that enables a forum administrator to extract/export database information (from all fields created by the installs) to an Excel spreadsheet or a CSV file.

Any input/suggestion/answer to this request is equally appreciated...

regards,

- padawan
"...be mindful of the SnitzForce..."

dayve
Forum Moderator

USA
5820 Posts
Posted - 14 October 2002 :  12:08:08  Show Profile  Visit dayve's Homepage
actually XML output would be more desirable IMHO.
Go to Top of Page

Doug G
Support Moderator

USA
6493 Posts
Posted - 14 October 2002 :  12:46:18  Show Profile
quote:
Originally posted by dayve

actually XML output would be more desirable IMHO.

I'll second that :)
======
Doug G
======
Computer history and help at www.dougscode.com
Go to Top of Page

davemaxwell
Access 2000 Support Moderator

USA
3020 Posts
Posted - 14 October 2002 :  22:28:49  Show Profile  Visit davemaxwell's Homepage  Send davemaxwell an AOL message  Send davemaxwell an ICQ Message  Send davemaxwell a Yahoo! Message
quote:
Originally posted by Doug G

quote:
Originally posted by dayve

actually XML output would be more desirable IMHO.

I'll second that :)



Depends on the situation. If moving from one database platform to another, CSV can be much more useful than XML. Perhaps the ability to do both (I love suggesting more work for someone else )
Dave Maxwell
Barbershop Harmony Freak
Go to Top of Page

Reinsnitz
Snitz Forums Admin

USA
3545 Posts
Posted - 14 October 2002 :  22:44:26  Show Profile  Visit Reinsnitz's Homepage  Send Reinsnitz an AOL message  Send Reinsnitz an ICQ Message  Send Reinsnitz a Yahoo! Message
ah... but inherent prolems fall in CVS when dealing with the type of data we have here :) hehe

XML can be pulled into any modern application and output in almost any format you would like... :)

XML baby! :P
Reinsnitz (Mike)
Go to Top of Page

work mule
Senior Member

USA
1358 Posts
Posted - 14 October 2002 :  22:53:58  Show Profile
LA-LA-LA....

Is this perfect? NO, but it's the best I could do in converting one of my scripts to support both xml and csv in an hours time in order to provide a proof of concept.

I can't remember off the top of my head what characters I need to parse or change for a valid xml, but for a first go 'round here it is.

With a bit more tweaking, I could make this recursive to dump the entire database, but not tonight.

Now of course, SQL Server 2000 can do XML for you...



<!--#INCLUDE FILE="config.asp"-->
<!--#INCLUDE FILE="inc_func_common.asp" -->
<%

Response.Buffer = True

Dim FileType, strFileName, intUseDate

intUseDate = 1 'Include Date in Filename [1:yes;0:no]
strFileName = "test"

'FileType = "csv"
FileType = "xml"

set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open strConnString

Make_Export_File

my_Conn.Close
Set my_Conn = Nothing

'------------------------------------------------------------------------------
'EXPORT FUNCTIONS
'------------------------------------------------------------------------------

Sub Make_Export_File
   Dim strSQL 
   strSQL = "SELECT * FROM " & strTablePrefix & "CATEGORY"

   Dim objRS : Set objRS = Server.CreateObject("adodb.recordset")
   objRS.Open strSQL,my_Conn,3,1
   If objRS.State Then
      Select Case FileType
         Case "csv"
            Build_CSV_File objRS,strFileName
         Case "xml"
            Build_XML_File objRS,strFileName,"CATEGORY",0
      End Select
      objRS.Close
   Else
      Build_CSV_File_Blank
   End If
   Set objRS = Nothing
End Sub

Function ExportDate()
   If intUseDate = 1 Then
      Dim thisdate
      thisdate = Now()
      ExportDate = "-" & Year(thisdate) & doublenum(Month(thisdate)) & doublenum(Day(thisdate))
   Else
      ExportDate = ""
   End If
End Function

'------------------------------------------------------------------------------
'CSV FUNCTIONS
'------------------------------------------------------------------------------

Sub Build_CSV_File(rs,filename)
   Response.AddHeader "Content-Type","application/csv"
   Response.AddHeader "Content-Disposition","inline; filename=" & filename & ExportDate & ".csv"
   Dim c, f, strColFieldList, intFieldCount
   intFieldCount = 0
   For Each c In rs.Fields
     strColFieldList = strColFieldList & "," & c.Name
     intFieldCount = intFieldCount + 1
   Next
   strColFieldList = Mid(strColFieldList,2) & vbCrLf 'remove the first "," from the Header section
   Response.Write strColFieldList
   Do Until rs.EOF
      For f = 0 To intFieldCount-1
         If f > 0 Then response.write ","
         response.write CSVEncode(Trim(rs.Fields(f).Value))
      Next
      response.write vbCrLf
   rs.MoveNext
   Loop
End Sub

Sub Build_CSV_File_Blank
   Response.AddHeader "Content-Type","application/csv"
   Response.AddHeader "Content-Disposition","inline; filename=empty-" & ExportDate & ".csv"
   'Response.Write "no records matched your query"
End Sub

Function CSVEncode(string)
   If Len(string) > 0 Then
      string = Replace(string,"""","""""")
   End If
   CSVEncode = """" & string & """"
End Function

'------------------------------------------------------------------------------
'XML FUNCTIONS
'------------------------------------------------------------------------------

Sub Build_XML_File(rs,filename,parentelementname,depth)
   Response.AddHeader "Content-Type","application/xml"
   Response.AddHeader "Content-Disposition","inline; filename=" & filename & ExportDate & ".xml"
   Response.Write "<SNITZ>" & vbCrLf
   Dim c, f, strColFieldList, intFieldCount
   intFieldCount = 0
   For Each c In rs.Fields
     strColFieldList = strColFieldList & "," & c.Name
     intFieldCount = intFieldCount + 1
   Next
   strColFieldList = Mid(strColFieldList,2) & vbCrLf 'remove the first "," from the Header section
   Dim arrFields : arrFields = Split(strColFieldList,",")
   Do Until rs.EOF
	   Response.Write "<" & parentelementname & ">" & vbCrLf
      response.write String(depth+1,vbTab)
      For f = LBound(arrFields) To UBound(arrFields)
         response.write XMLEncode(Trim(rs.Fields(f).Value),arrFields(f))
      Next
      response.write vbCrLf
		Response.Write "</" & parentelementname & ">" & vbCrLf
	   rs.MoveNext
   Loop
	Response.Write "</SNITZ>" & vbCrLf
End Sub

Sub Build_XML_File_Blank
   Response.AddHeader "Content-Type","application/xml"
   Response.AddHeader "Content-Disposition","inline; filename=empty-" & ExportDate & ".xml"
   'Response.Write "no records matched your query"
End Sub

Function XMLEncode(data,elementname)
   If Len(data) > 0 Then
      data = "<" & elementname & ">" & data & "</" & elementname & ">" & vbCrLf
   End If
   XMLEncode = data
End Function

%>


EDIT: Minor Tweak
Edited by - work mule on 15 October 2002 00:01:33
Go to Top of Page

work mule
Senior Member

USA
1358 Posts
Posted - 14 October 2002 :  22:58:49  Show Profile
oh, yeah...

With the above, it's sorta dynamic in the fact that it will use the Field Names provided to create either the header in the CSV file or the container tags in XML.

I've used the csv version and aliased the fields to be whatever the target database/application needed the names to be.

Sort of a handy set of functions to have in the toolbox.
Go to Top of Page

work mule
Senior Member

USA
1358 Posts
Posted - 15 October 2002 :  01:43:33  Show Profile
Well so much for working on other things tonight...
Whoever suggested XML output, I hate you.

This is a bit better than the above.

This is a bit basic. It should be pretty easy to take this and adjust it to whatever you need. The SQL statement is really the key. Use that to define what fields to include, how they're named, the order and the criteria for the export.

This example outputs as:

<SNITZ_FORUMS_2000>
   <FORUM_CATEGORY></FORUM_CATEGORY>
   <FORUM_FORUM></FORUM_FORUM>
</SNITZ_FORUMS_2000>

I haven't attempted nesting these like this yet:


CATEGORY
   FORUM
      TOPICS
         REPLY
      A_TOPICS
         A_REPLY


Let me know if anyone tries this script out. 


<%
Response.Buffer = True
Response.Expires = -1
%>
<!--#INCLUDE FILE="config.asp"-->
<!--#INCLUDE FILE="inc_func_common.asp" -->
<%
Dim FileType, strFileName, intUseDate

'Choose whether or not to include date in the filename
intUseDate = 0 'Include Date in Filename [1:yes;0:no]

'Specify the name
strFileName = "snitz-xml"

'File Type - like 'duh!!
FileType = "xml"

set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open strConnString

Make_XML_File

my_Conn.Close
Set my_Conn = Nothing

'------------------------------------------------------------------------------
'XML EXPORT FUNCTIONS
'------------------------------------------------------------------------------

Sub Make_XML_File()
   Response.AddHeader "Content-Type","application/xml"
   Response.AddHeader "Content-Disposition","inline; filename=" & strFileName & ExportDate & ".xml"
   Response.Write "<?xml version=""1.0""?>" & vbCrLf
   Response.Write "<SNITZ_FORUMS_2000>" & vbCrLf
      GetTable "category",0
      GetTable "forum",0
      GetTable "topics",0
   Response.Write "</SNITZ_FORUMS_2000>" & vbCrLf
End Sub

Sub GetTable(table,depth)
   Dim objRS : Set objRS = Server.CreateObject("adodb.recordset")
   Dim strSQL
   Select Case table
      Case "category"
         Response.Write "<" & strTablePrefix & "CATEGORY>" & vbCrLf
         strSQL = "SELECT * FROM " & strTablePrefix & "CATEGORY ORDER BY CAT_ID"
         objRS.Open strSQL,my_Conn,3,1
         If objRS.State Then
            Build_XML_File objRS,"CATEGORY",0
            objRS.Close
         End If
         Response.Write "</" & strTablePrefix & "CATEGORY>" & vbCrLf
      Case "forum"
         Response.Write "<" & strTablePrefix & "FORUM>" & vbCrLf
         strSQL = "SELECT * FROM " & strTablePrefix & "FORUM ORDER BY FORUM_ID"
         objRS.Open strSQL,my_Conn,3,1
         If objRS.State Then
            Build_XML_File objRS,"FORUM",0
            objRS.Close
         End If
         Response.Write "</" & strTablePrefix & "FORUM>" & vbCrLf
      Case "topics"
         Response.Write "<" & strTablePrefix & "TOPICS>" & vbCrLf
         strSQL = "SELECT * FROM " & strTablePrefix & "TOPICS ORDER BY TOPIC_ID"
         objRS.Open strSQL,my_Conn,3,1
         If objRS.State Then
            Build_XML_File objRS,"TOPICS",0
            objRS.Close
         End If
         Response.Write "</" & strTablePrefix & "TOPICS>" & vbCrLf
      Case "archivetopics"
         Response.Write "<" & strTablePrefix & "A_TOPICS>" & vbCrLf
         strSQL = "SELECT * FROM " & strTablePrefix & "A_TOPICS ORDER BY TOPIC_ID"
         objRS.Open strSQL,my_Conn,3,1
         If objRS.State Then
            Build_XML_File objRS,"TOPICS",0
            objRS.Close
         End If
         Response.Write "</" & strTablePrefix & "A_TOPICS>" & vbCrLf
      Case "archivereply"
         Response.Write "<" & strTablePrefix & "A_REPLY>" & vbCrLf
         strSQL = "SELECT * FROM " & strTablePrefix & "A_REPLY ORDER BY REPLY_ID"
         objRS.Open strSQL,my_Conn,3,1
         If objRS.State Then
            Build_XML_File objRS,"REPLY",0
            objRS.Close
         End If
         Response.Write "</" & strTablePrefix & "A_REPLY>" & vbCrLf
   End Select
   Set objRS = Nothing
End Sub

Sub Build_XML_File(rs,parentelementname,depth)
   Dim c, f, strColFieldList, intFieldCount
   intFieldCount = 0
   For Each c In rs.Fields
     strColFieldList = strColFieldList & "," & c.Name
     intFieldCount = intFieldCount + 1
   Next
   strColFieldList = Mid(strColFieldList,2) & vbCrLf 'remove the first "," from the Header section
   Dim arrFields : arrFields = Split(strColFieldList,",")
   Do Until rs.EOF
      Response.Write "<" & parentelementname & ">" & vbCrLf
      response.write String(depth+1,vbTab)
      For f = LBound(arrFields) To UBound(arrFields)
         response.write XMLEncode(Trim(rs.Fields(f).Value),arrFields(f))
      Next
      response.write vbCrLf
      Response.Write "</" & parentelementname & ">" & vbCrLf
      rs.MoveNext
   Loop
End Sub

Function XMLEncode(data,elementname)
   If Len(data) > 0 Then
      data = Replace(data,"&","&") '& - &
      data = Replace(data,"<","<") '< - <
      data = Replace(data,">",">") '> - >
   
      Dim i
      For i = 128 to 255
         data = Replace(data,chr(i),Escape(chr(i)))
      Next
      data = "<" & elementname & ">" & data & "</" & elementname & ">" & vbCrLf
   End If
   XMLEncode = data
End Function

Function ExportDate()
   If intUseDate = 1 Then
      Dim thisdate
      thisdate = Now()
      ExportDate = "-" & Year(thisdate) & doublenum(Month(thisdate)) & doublenum(Day(thisdate))
   Else
      ExportDate = ""
   End If
End Function

%>
Go to Top of Page

work mule
Senior Member

USA
1358 Posts
Posted - 15 October 2002 :  01:56:07  Show Profile
If you need something exported as an Excel Spreadsheet, then try the following. Modify your SQL statement, etc to whatever you need.




<%
Response.Buffer = True
Response.Expires = -1
%>
<!--#INCLUDE FILE="config.asp"-->
<!--#INCLUDE FILE="inc_func_common.asp" -->
<%
Dim FileType, strFileName, intUseDate, strReportHeader

'Choose whether or not to include date in the filename
intUseDate = 0 'Include Date in Filename [1:yes;0:no]

'Specify the name
strFileName = "snitz-spreadsheet"

strReportHeader = "SNITZ REPORT"

'File Type
FileType = "xls"

set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open strConnString

Make_XLS_File

my_Conn.Close
Set my_Conn = Nothing

'------------------------------------------------------------------------------
'XLS EXPORT FUNCTIONS
'------------------------------------------------------------------------------

Sub Make_XLS_File
   'Response.AddHeader "Content-Type","application/xls"
   Response.AddHeader "Content-Type","application/vnd.ms-excel"
   Response.AddHeader "Content-Disposition","inline; filename=" & strFileName & ExportDate & ".xls"

   Response.Write "<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">"
   Response.Write "<head>"
   Response.Write "<!--[if gte mso 9]><xml>"
   Response.Write "<x:ExcelWorkbook>"
   Response.Write "<x:ExcelWorksheets>"
   Response.Write "<x:ExcelWorksheet>"
   Response.Write "<x:Name>" & strReportHeader & "</x:Name>"
   Response.Write "<x:WorksheetOptions>"
   Response.Write "<x:Print>"
   Response.Write "<x:ValidPrinterInfo/>"
   Response.Write "</x:Print>"
   Response.Write "</x:WorksheetOptions>"
   Response.Write "</x:ExcelWorksheet>"
   Response.Write "</x:ExcelWorksheets>"
   Response.Write "</x:ExcelWorkbook>"
   Response.Write "</xml>"
   Response.Write "<![endif]--> "
   Response.Write "</head>"
   Response.Write "<body>"
   Get_XLS_Data
   Response.Write "</body>"
   Response.Write "</html>"
End Sub

Sub Get_XLS_Data
   Dim strSQL 
   strSQL = "SELECT * FROM " & strTablePrefix & "CATEGORY"

   Dim objRS : Set objRS = Server.CreateObject("adodb.recordset")
   objRS.Open strSQL,my_Conn,3,1
   If objRS.State Then
      Build_XLS_Table objRS
      objRS.Close
   End If
   Set objRS = Nothing
End Sub

Sub Build_XLS_Table(rs)

   Dim c, f, strColFieldList, intFieldCount
   intFieldCount = 0

   Response.Write "<table>"
   Response.Write "<tr>"

   For Each c In rs.Fields
      Response.Write "<th style='background-color:#cccccc;' FILTER=ALL>  "
      Response.Write c.Name
      Response.Write "   </th>"
      intFieldCount = intFieldCount + 1
   Next

   Response.Write "</tr>"

   strColFieldList = Mid(strColFieldList,2) & vbCrLf 'remove the first "," from the Header section
   Do Until rs.EOF
      Response.Write "<tr>"
      For f = 0 To intFieldCount-1
         Response.Write "<td>"  ' style=""vnd.ms-excel.format.text""
         Response.Write Trim(rs.Fields(f).Value)
         Response.Write "</td>"
      Next
      response.write vbCrLf
      Response.Write "</tr>"

      rs.MoveNext
   Loop

   Response.Write "</table>"

End Sub

Function ExportDate()
   If intUseDate = 1 Then
      Dim thisdate
      thisdate = Now()
      ExportDate = "-" & Year(thisdate) & doublenum(Month(thisdate)) & doublenum(Day(thisdate))
   Else
      ExportDate = ""
   End If
End Function

%>
Go to Top of Page

work mule
Senior Member

USA
1358 Posts
Posted - 15 October 2002 :  03:06:53  Show Profile
Sample scripts can be found here:
http://www.snitzbitz.com/mods/details.asp?Version=All&mid=83
Go to Top of Page

ajhvdb
Junior Member

Netherlands
392 Posts
Posted - 15 October 2002 :  03:50:56  Show Profile
Learning more and more on this forum by reading. Thank you.
Go to Top of Page

Gremlin
General Help Moderator

New Zealand
7528 Posts
Posted - 15 October 2002 :  04:09:35  Show Profile  Visit Gremlin's Homepage
If your using MS SQL don't forget you can also actually get it to generate XML for you by using the FOR XML clause at the end of your select ie

SELECT M_NAME FROM FORUM_MEMBERS FOR XML AUTO
Kiwihosting.Net - The Forum Hosting Specialists
Go to Top of Page

padawan
Junior Member

200 Posts
Posted - 15 October 2002 :  23:57:58  Show Profile

wow! i've never seen so many talented brains converge on one key component at one time! ...so much brain waves across that i can almost fry an egg.

i hope this request becomes a beta MOD... then, a final release sometime in the future... goodluck, all!



- padawan
"...be mindful of the SnitzForce..."
Go to Top of Page

padawan
Junior Member

200 Posts
Posted - 22 October 2002 :  05:33:03  Show Profile
sir WORK MULE:

Thanks for creating the MOD for this. I'll have it a try when time permits.

On the reverse... could your MOD be revised/upgraded for "IMPORT" capabilities, as well? The reason for this is that if an admin has a list of external user data that s/he would like to add to a certain field (say States from an Excel/CSV list), it'll be much easier if the admin doesn't have to go through each user profile and edit them. Rather a simple 'match last name,' then upload this data onto this field' will certainly make the task much easier and faster to complete.

...just a thought...

- padawan
"...be mindful of the SnitzForce..."
Go to Top of Page

work mule
Senior Member

USA
1358 Posts
Posted - 25 October 2002 :  22:14:44  Show Profile
padawan,

Thanks for the comments, they're are appreciated.

I'm sure with additional work and testing this could be made into a mod which worked both ways. I honestly don't see myself working on this anytime in the near future, so please don't expect a beta mod to come from me. Anyone who wants to work on one using the scripts above as a starting point are more than welcome to. I have a couple of other pieces of code I promised here which I need to work out and get posted.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000 Version 3.4.07