It shouldn't be all that difficult to write code to read it.
Use the Line Input statement to read the text one line at a time.
Throw away blank lines.
If a line starts with [ and has ] somewhere later in it, determine what's in
between the brackets, and that's your current country. If you're interested
in holidays for that country, use the Split function on each of the
following rows to divide into the holiday name and date. You now know the
locale, holiday and date: you can insert into your table.
Continue untill the next country, or the end of the file.
Something like the following untested air-code:
Dim intFile As Integer
Dim intPos As Integer
Dim strCurrCountry As String
Dim strCurrLine As String
Dim strSQL As String
Dim varHolidayInformation As Variant
intFile = FreeFile()
Open "full path to file" For Input As #intFile
Do While Not Eof(intFile)
Line Input #intFile, strCurrLine
If Len(strCurrLine) > 0 Then
If Left$(strCurrLine, 1) = "["
intPos = InStr(2, strCurrLine, "]")
If intPos > 0 Then
strCurrCountry = Mid$(strCurrLine, 2, intPos - 2)
End If
Else
varHolidayInformation = Split(strCurrLine, ",")
If IsNull(varHolidayInformation) = False Then
strSQL = "INSERT INTO tblHoliday (Country, Holiday,
HolidayDt) " & _
"VALUES (" & Chr$(34) & strCurrCountry & Chr$(34) & ",
" & _
Chr$(34) & varHolidayInformation(0) & Chr$(34) & ", " &
_
CDate(varHolidayInformation(1) & ")"
CurrentDb.Execute strSQL, dbFailOnError
End If
End If
End If
Loop
Close #intFile
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
hughess7 said:
Hi Dan,
Thanks for your reply, I know it is just a text file and what the
structure
looks like - I just don't know how to read it into Access and was asking
for
some assistance with that. I know the code for importing a text file but
not
sure how to get all the right text into the relevant fields. I would like
to
store the fields Country, HolidayTxt, Date based on the following
structure.
How do I tell Access the Country is the bit in the brackers etc etc... ? I
don't want all the countries (eg US not required) but I can strip these
out
manually first if necessary. I also wandered if anyone else had already
utilised this file in a similar manner and had some code for it?
[United Kingdom] 55
Battle of the Boyne (N. Ireland),2003/7/14
Boxing Day,2003/12/26
Christmas Day,2003/12/25
Thanks
Sue
:
Hi,
It's just a text file so you can open it with Notepad and have a look at
the
structure.
You would have to then decide how you want to store it in Access. I mean,
you probably wouldn't
want to just store the info all in one table as it would not be
normalized.
--
HTH
Dan Artuso, Access MVP
Hi, I need a holiday table in my database so I can use it to exclude
any
public holidays when planning itineries. Due to the fact that we
operate
throughout Europe the outlook.hol file which comes with Outlook would
be
ideal as a basis. Has anyone used this as an import to Access? If so, I
would
appreciate any help!
I did use it to add them all to an Exchange Public Folder and tried
importing this (File, Import from Access) but the table it produced
excluded
the actual date of the holiday (appointment) which I found odd? It only
had
dates for creation, last modified etc.
My coding skills are very basic so if anyone offer some advice it would
be
most appreciated.
Thanks in advance for any help.
Sue
