We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Creating a file - 500pts

Liam2k3
Liam2k3 asked
on
Medium Priority
353 Views
Last Modified: 2008-02-01
Hey All,
I want to create a file based on information held in a table of my database.  The file itself is an ICS file (calendar file), however this is not important.  The data held in the file is plain text, and follows the structure below:

BEGIN:VCALENDAR
VERSION
 :2.0
PRODID
 :-//Mozilla.org/NONSGML Mozilla Calendar V1.0//EN
METHOD
 :PUBLISH
BEGIN:VEVENT
SUMMARY
 :The Title of the Event
DESCRIPTION
 :Description of the Event
CLASS
 :PUBLIC
X-MOZILLA-ALARM-DEFAULT-LENGTH
 :30
X-MOZILLA-RECUR-DEFAULT-INTERVAL
 :0
DTSTART
 ;VALUE=DATE
 :yyyymmdd
DTEND
 ;VALUE=DATE
 :yyyymmdd
END:VEVENT
END:VCALENDAR

For Each Event
BEGIN:VEVENT
SUMMARY
 :The Title of the Event (Name of customer)
DESCRIPTION
 :Description of the Event (Extra details from the customer database, previous tunings and address)
CLASS
 :PUBLIC
X-MOZILLA-ALARM-DEFAULT-LENGTH
 :Standard numeric time – fixed for all events ( :30)
X-MOZILLA-RECUR-DEFAULT-INTERVAL
 :Standard numeric time – always 0 ( :0)
DTSTART
 ;VALUE=DATE
 :yyyymmddThhmmss
DTEND
 ;VALUE=DATE
 :yyyymmddThhmmss
END:VEVENT

The data I would like to retrieve from my database would come from a query holding the fields Summary, Description, Date Start and Date End.  For each record on the query I would like a new event created in the file.  After running for each record of the query I would like the file to be saved as Calendar.ics.  Every time the script is run I would like it to recreate the entire file and overwrite the previous existing one.

Please could you provide me with a script which will do this.

Liam
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2006

Commented:
This sample code shows you how to write to a file


    Dim iHandle As Integer
   
    iHandle = FreeFile
    Open "c:\test.txt" For Output As #iHandle
    Print #iHandle, "hello"
    Close #iHandle


this code opens the DB and reads records

    Dim rs As DAO.Recordset

sSql = "SELECT * FROM myTable"
set rs = CurrentDB.OpenRecordSet(sSql)
do while rs.EOF = FALSE
    'access fields
    debug.print rs!field1
    'next
    rs.movenext
loop
rs.close
set rs =nothing


Now merge the two,

    Dim iHandle As Integer
    Dim rs As DAO.Recordset
   
    iHandle = FreeFile
    Open "c:\dipak\xxx_ttt.txt" For Output As #iHandle
   
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")
    Do While rs.EOF = False
        Print #iHandle, "hello"
        rs.MoveNext
    Loop
    rs.close
    set rs = nothing
    close #iHandle

But what you do is write the lines you want and the fields you want within the loop
if you want individual files, then open and close the file within the loop

does this make sense?
CERTIFIED EXPERT
Top Expert 2006
Commented:
put together equals something like this

    Dim iHandle As Integer
    Dim rs As DAO.Recordset
   
    iHandle = FreeFile
   
    Open "c:\calendars\Calendar.ics" For Output As #iHandle
    Set rs = CurrentDb.OpenRecordset("SELECT Summary, Description, DateStart, DateEnd FROM MyTable")
    Do While rs.EOF = False
       
        Print #iHandle, "BEGIN: VCALENDAR"
        Print #iHandle, "Version"
        Print #iHandle, ":2.0"
        Print #iHandle, "PRODID"
        Print #iHandle, ":-//Mozilla.org/NONSGML Mozilla Calendar V1.0//EN"
        Print #iHandle, "METHOD"
        Print #iHandle, ": PUBLISH"
        Print #iHandle, "BEGIN: VEVENT"
        Print #iHandle, "SUMMARY"
        Print #iHandle, ":The Title of the Event (" & rs!Summary & ")"
        Print #iHandle, "Description"
        Print #iHandle, ":" & rs!Description
        Print #iHandle, "Class"
        Print #iHandle, ":PUBLIC"
        Print #iHandle, "X -MOZILLA - ALARM - Default - LENGTH"
        Print #iHandle, ":30"
        Print #iHandle, "X -MOZILLA - RECUR - Default - INTERVAL"
        Print #iHandle, ":0"
        Print #iHandle, "DTSTART"
        Print #iHandle, ";VALUE=DATE"
        Print #iHandle, ": " & Format(rs!StartDate, "YYYYMMDDHHMMSS")
        Print #iHandle, "DTEND"
        Print #iHandle, ";VALUE=DATE"
        Print #iHandle, ": " & Format(rs!StartDate, "YYYYMMDDHHMMSS")
        Print #iHandle, "End: VEVENT"
        Print #iHandle, "End: VCALENDAR"

        rs.MoveNext
    Loop
   
    rs.Close
    Set rs = Nothing
    Close #iHandle

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2006

Commented:
spot the mistakes!!!

I put StartDate where there should be End Date

You also need to correct the fields from the table,

Hopefully u understand what Ive done so you can correct the code where necessary

Author

Commented:
Thanks for the help, the code worked a treat, heres what I ended up with:

    Dim iHandle As Integer
    Dim rs As DAO.Recordset
   
    iHandle = FreeFile
   
    Open "c:\Calendar.ics" For Output As #iHandle
    Set rs = CurrentDb.OpenRecordset("SELECT Summary, Description, StartTime, EndTime FROM Calendar")
   
        Print #iHandle, "BEGIN:VCALENDAR"
        Print #iHandle, "Version:2.0"
        Print #iHandle, "X-WR-CALNAME:Work"
        Print #iHandle, "PRODID:-//Mozilla.org/NONSGML Mozilla Calendar V1.0//EN"
        Print #iHandle, "X-WR-TIMEZONE:Europe/London"
        Print #iHandle, "CALSCALE:GREGORIAN"
        Print #iHandle, "METHOD:PUBLISH"
       
    Do While rs.EOF = False
        Print #iHandle, "BEGIN:VEVENT"
        Print #iHandle, "SUMMARY:" & rs!Summary
        Print #iHandle, "Description:" & rs!Description
        Print #iHandle, "Class:PUBLIC"
        Print #iHandle, "X-MOZILLA-ALARM-Default-LENGTH:30"
        Print #iHandle, "X-MOZILLA-RECUR-Default-INTERVAL:0"
        Print #iHandle, "DTSTART:" & Format(rs!StartTime, "YYYYMMDDHHMMSS")
        Print #iHandle, "DTEND:" & Format(rs!EndTime, "YYYYMMDDHHMMSS")
        Print #iHandle, "End:VEVENT"
       
        rs.MoveNext
    Loop
        Print #iHandle, "End:VCALENDAR"
    rs.Close
    Set rs = Nothing
    Close #iHandle

Out of interest, could you possibly explain to me why we set iHandle as an Integer yet we add to it like a string?

Liam
CERTIFIED EXPERT
Top Expert 2006

Commented:
thats how VB works

FreeFile is used to obtain the next file handle. File handles in VB are integers
lots of people just used a number like 1, 2, but I prefer to make use of that

Everytime u use the file handle, u need to specify in whatever call u do

Print is one command to write to a file, but first u must specify what file u want to write to

It is possible to have multiple files open

Therefore we use Print <file handle>, <text to write>


does that explain what you meant by why we set iHandle as an Integer yet we add to it like a string?

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.