By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

constructing a database from downloaded newspaper articles

P: n/a
I am hoping for some assistance with formatting a large text file which
consists of a series of individual records. Each record includes
specific labels/field names (a sample of 1 record (one of the longest
ones) is below - at end of post. What I want to do is reformat the
data, so that each individual record becomes a row (some cells will
have a lot of text). For example, the column variables I want are (a)
HD in one column (b) BY in one column (c) WC data in one column,
(d) PD data in one column, (e) SC data in one column (f) PG data in one
column & g) LP and TD text in one column - this column can contain
quite a lot of text, e.g 1900 words. The other fields are unwanted

If there were 150 individual records, when formatted this would be a 7
column by 150 row dataset.

Can I save such data into Access so each of the above fields becomes a
field in Access, and then, if I wanted to select certain fields for
exporting as a text file to be analysed by another program?

If this is possible, any suggestions or assistance is much appreciated,
Bob

(A) sample data

HD Was Charles Manson temporarily insane when he led a wild
killing
rampage in the US in 1969?
BY By Deborah Cassrels.
WC 1834 words
PD 23 June 2001
SN Courier Mail
SC COUMAI
PG 30
LA English
CY (c) 2001 Queensland Newspapers Pty Ltd

LP Was Charles Manson temporarily insane when he led a wild
killing
rampage in the US in 1969? Clearly he was mad and bad. But would
Queensland have placed him before its Mental Health Tribunal,
found him of
unsound mind at the time of his crimes, institutionalised him and

"treated" his illness? WHY is Queensland the only jurisdiction in
the
Commonwealth with a Mental Health Tribunal which establishes if
an accused
is fit to face trial or of unsound mind at the time of an alleged
offence?
Why is mental incompetence not determined in an adversarial court
by a
jury? Under the Mental Health Act 1974, the tribunal, a statutory
body
operating since 1985, comprises three-yearly appointments of a
Supreme
Court judge and two assisting psychiatrists, whose advice does
not have to
be accepted. The judge alone constitutes the tribunal, an
inquisitorial
process conducted in the Supreme Court in Brisbane.

TD Victims or family are not notified of hearings or allowed to
submit
victim impact statements. They are prohibited from talking to the
media
until 28 days after the decision. And when patients return to the

community there is no requirement for neighbours or victims to be

notified. Is this legislation enlightened or are we just suckers,
falling
for time and money-saving strategies? The tribunal has earned a
reputation
as progressive, humane and economical among some judges who have
presided
over it. The inaugural chair, former Supreme Court judge Angelo
Vasta QC,
thinks the tribunal system is "enlightened" and "it saves an
enormous
amount of expenditure". He points to the humane side of treating
the ill
in a secure hospital rather than punishing them for offences but
is
uncomfortable with borderline cases. "Whether people are mad or
bad ought
to be established by a very thorough investigation.
The associated Patient Review Tribunals (of which there are five)
consist
of three to six members, including the chair who is a legal
officer, a
medical practitioner and a mental health professional. A
psychiatrist is
not required. The other three have no specific qualifications and
can
include former patients. The tribunals operate in closed hearings
and
patients of unsound mind or unfit for trial are reviewed every 12
months.
Leave is granted either by the Mental Health Tribunal or the
Patient
Review Tribunal, which determine when a restricted patient is
discharged
into the community. Says the Director of Mental Health, Dr Peggy
Brown:
"In the case of serious offences you can be assured the period of

monitoring is quite lengthy." Under the Mental Health Act 2000 to
be
implemented late this year, the tribunal will be replaced by a
Mental
Health Court and the Patient Review Tribunal by the Mental Health
Review
Tribunal. Queensland Health Minister Wendy Edmond says the name
change
reflects transparency, with proceedings under oath and
cross-examination
of witnesses. The legislation represents "real change to the
rights of
victims of crime". But there is still an embargo on publishing
decisions
in the media.
Dr Brown says when patients are granted leave, victims or
families can
apply to be notified but decisions will be made on individual
cases. "The
(new) tribunal has to establish that there are reasonable grounds
for the
notification order to be made ... and it's also an appealable
decision,"
returning to the Mental Health Court.
Brown says there are efficiencies in the new legislation but
"it's not
about saving money". The main advantages were that victims could
make
submissions to both bodies. Concerns still might not be addressed
but
reasons were expected to be provided. The court's composition and
sole
power of the judge will be retained. Victims or relatives can be
notified
of hearings and decisions about the patient. If not, reasons must
be
provided. The Patient Review Tribunals will be replaced by one
tribunal
with hearings still closed. It will comprise up to five members
including
a president (a lawyer of at least seven years' standing),
psychiatrist or
medical practitioner and community members and it will be chaired
by a
legal officer. Leave will be approved by the corresponding
previous
bodies. Chief Justice Paul de Jersey who presided over the 1995
case of
Ross Farrah, a paranoid schizophrenic, who after murdering his
girlfriend,
Christine Nash, was allowed out of the John Oxley Centre to play
sport and
see movies, says the proposed legislative changes to the Mental
Health Act
appear to be "refinements". Two weeks ago, Nash's teenage son
Wade
committed suicide after suffering years of torment following his
mother's
murder. In May 1996, a letter was sent to the tribunal by now
former
director of secure care services at John Oxley Dr Peter Fama. It
said:
"Should Ross be committed to the Tribunal for trial on a charge
of
manslaughter or murder, I have to report that he is now fit to be
placed
in corrective custody ... There is no clinical need for further
detention
of Ross in hospital." De Jersey has been involved in the process
of
amendments in the new Act and believes the "adjustments" are
satisfactory:
"It's probably a question of how they're implemented. I thought
the
changes were more concerned with image than effecting substantial
change
to the system, calling it a court rather than a tribunal. There
is some
attempt to enhance the openness of the procedures such as the
advice given
by the existing psychiatrists being revealed in open court to the
judge
but they're aspects of streamlining rather than substantive
change." He
says many people are irked by a perceived disproportion between
the
treatment of mentally ill offenders and their victims. "As a
community we
need much more positively to address the situation of victims."
De Jersey
points to the James Bulger murder in the UK eight years ago when
two
10-year-old boys abducted and battered James, two, to death. The
killers
are expected to be freed soon. Says de Jersey: "Whatever one
thinks of
future plans for the young offenders it is extraordinary, if
reportedly
correct, that so little help has been given to the bereft mother
of the
murdered toddler. "Similarly, here, it is generally indefensible
where
victims or the families of victims are not informed of details of
the
likely release of their offenders, and even before that where
they are not
given a proper explanation as to the process and counselling to
help them
comprehend that process and as well the consequences of the
crime. We are
as a community moving towards a greater focus on the position of
victims
but a lot more needs to be done. "The anguish of victims and the
families
of victims that insane offenders appear to escape punishment is
understandable. The issue is whether the community is prepared to
accept
that insane offenders primarily need treatment." The Mental
Health
Tribunal worked on two assumptions, that offenders of unsound
mind should,
in the interests of the community, be treated rather than
punished, and
that a determination whether an offender was of unsound mind
could
responsibly be made by a Supreme Court judge with expert
psychiatric
assistance. "I have wondered whether with the ultimately serious
crimes
such as murder the community may not reasonably demand that in
the
interests of reassurance that the determination be made by a
jury." He
believes the community's longer term interests would best be
served by
medically treating insane offenders in a hospital rather than a
prison,
where if rehabilitated, they could contribute to the community.
"I accept,
however, that in many cases there will be serious residual
concern, for
example, can the offender be trusted, if left unsupervised, to
continue to
take the relevant medication?"
De Jersey admits problems have arisen when offenders, granted
leave,
stopped taking medication but says if they can be relied upon to
maintain
stability through medication it would be inhumane to keep them
locked up.
Continued medical monitoring was necessary. If conditions were
breached
the person should be returned to restricted custody at the
psychiatric
hospital. While the most vulnerable in society deserve compassion
it does
not surprise there is public concern about lack of proper
scrutiny, the
capacity to re-offend and misuse of the legal process by using
insanity as
a defence. IN the general quest to improve treatment provisions
for
patients the 2000 Act says: "The new legislation provides for
involuntary
treatment in the community as an alternative to being an
in-patient in a
mental health service which reflects contemporary clinical
practice and
the principle of reform that involuntary treatment must be in the
least
restrictive form."
Perhaps the overwhelming feeling is patients' rights have
priority over
victims' rights. Ted Flack, spokesman for the Queensland Homicide
Victims
Support Group says the new Act provides a better environment for
victims'
participation, but there are serious flaws. The rights of
homicide victims
were not guaranteed and this caused an inordinate amount of
distress.
"There's still considerable discretion in the hands of the Mental
Health
Court and the Mental Health Review Tribunal as to whether they
would admit
any evidence from the victims. The new Act is framed in such a
way as to
provide guaranteed rights to the person who's suffering from a
mental
illness and those rights come appropriately from the
international
conventions, but there are similar international conventions for
victims
and they are being completely ignored in the Act." Flack says the
primary
purpose of the Mental Health Tribunal is to save money and to
safeguard
the rights of the mentally disabled person. He believes the
criminally
insane can be catered for properly in jail. "The imprecise
science of
psychiatry is not an appropriate set of guidelines for the
release into
the community of dangerous killers," he says.

NS
GCAT : Political/General News | GCRIM : Crime/Courts | GHEA :
Health |
GHOME : Law Enforcement

RE
AUSNZ : Australia and New Zealand | AUSTR : Australia

AN
Document coumai0020010710dx6n005vl

Jul 23 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
The first identifing fields can be stored as text, for the last field I
would recommend that you used a memo field, this can store up to 65,535
characters.

Good luck,

Nick

bgreen wrote:
I am hoping for some assistance with formatting a large text file which
consists of a series of individual records. Each record includes
specific labels/field names (a sample of 1 record (one of the longest
ones) is below - at end of post. What I want to do is reformat the
data, so that each individual record becomes a row (some cells will
have a lot of text). For example, the column variables I want are (a)
HD in one column (b) BY in one column (c) WC data in one column,
(d) PD data in one column, (e) SC data in one column (f) PG data in one
column & g) LP and TD text in one column - this column can contain
quite a lot of text, e.g 1900 words. The other fields are unwanted

If there were 150 individual records, when formatted this would be a 7
column by 150 row dataset.

Can I save such data into Access so each of the above fields becomes a
field in Access, and then, if I wanted to select certain fields for
exporting as a text file to be analysed by another program?

If this is possible, any suggestions or assistance is much appreciated,
Bob

(A) sample data

HD Was Charles Manson temporarily insane when he led a wild
killing
rampage in the US in 1969?
BY By Deborah Cassrels.
WC 1834 words
PD 23 June 2001
SN Courier Mail
SC COUMAI
PG 30
LA English
CY (c) 2001 Queensland Newspapers Pty Ltd

LP Was Charles Manson temporarily insane when he led a wild
killing
rampage in the US in 1969? Clearly he was mad and bad. But would
Queensland have placed him before its Mental Health Tribunal,
found him of
unsound mind at the time of his crimes, institutionalised him and

"treated" his illness? WHY is Queensland the only jurisdiction in
the
Commonwealth with a Mental Health Tribunal which establishes if
an accused
is fit to face trial or of unsound mind at the time of an alleged
offence?
Why is mental incompetence not determined in an adversarial court
by a
jury? Under the Mental Health Act 1974, the tribunal, a statutory
body
operating since 1985, comprises three-yearly appointments of a
Supreme
Court judge and two assisting psychiatrists, whose advice does
not have to
be accepted. The judge alone constitutes the tribunal, an
inquisitorial
process conducted in the Supreme Court in Brisbane.

TD Victims or family are not notified of hearings or allowed to
submit
victim impact statements. They are prohibited from talking to the
media
until 28 days after the decision. And when patients return to the

community there is no requirement for neighbours or victims to be

notified. Is this legislation enlightened or are we just suckers,
falling
for time and money-saving strategies? The tribunal has earned a
reputation
as progressive, humane and economical among some judges who have
presided
over it. The inaugural chair, former Supreme Court judge Angelo
Vasta QC,
thinks the tribunal system is "enlightened" and "it saves an
enormous
amount of expenditure". He points to the humane side of treating
the ill
in a secure hospital rather than punishing them for offences but
is
uncomfortable with borderline cases. "Whether people are mad or
bad ought
to be established by a very thorough investigation.
The associated Patient Review Tribunals (of which there are five)
consist
of three to six members, including the chair who is a legal
officer, a
medical practitioner and a mental health professional. A
psychiatrist is
not required. The other three have no specific qualifications and
can
include former patients. The tribunals operate in closed hearings
and
patients of unsound mind or unfit for trial are reviewed every 12
months.
Leave is granted either by the Mental Health Tribunal or the
Patient
Review Tribunal, which determine when a restricted patient is
discharged
into the community. Says the Director of Mental Health, Dr Peggy
Brown:
"In the case of serious offences you can be assured the period of

monitoring is quite lengthy." Under the Mental Health Act 2000 to
be
implemented late this year, the tribunal will be replaced by a
Mental
Health Court and the Patient Review Tribunal by the Mental Health
Review
Tribunal. Queensland Health Minister Wendy Edmond says the name
change
reflects transparency, with proceedings under oath and
cross-examination
of witnesses. The legislation represents "real change to the
rights of
victims of crime". But there is still an embargo on publishing
decisions
in the media.
Dr Brown says when patients are granted leave, victims or
families can
apply to be notified but decisions will be made on individual
cases. "The
(new) tribunal has to establish that there are reasonable grounds
for the
notification order to be made ... and it's also an appealable
decision,"
returning to the Mental Health Court.
Brown says there are efficiencies in the new legislation but
"it's not
about saving money". The main advantages were that victims could
make
submissions to both bodies. Concerns still might not be addressed
but
reasons were expected to be provided. The court's composition and
sole
power of the judge will be retained. Victims or relatives can be
notified
of hearings and decisions about the patient. If not, reasons must
be
provided. The Patient Review Tribunals will be replaced by one
tribunal
with hearings still closed. It will comprise up to five members
including
a president (a lawyer of at least seven years' standing),
psychiatrist or
medical practitioner and community members and it will be chaired
by a
legal officer. Leave will be approved by the corresponding
previous
bodies. Chief Justice Paul de Jersey who presided over the 1995
case of
Ross Farrah, a paranoid schizophrenic, who after murdering his
girlfriend,
Christine Nash, was allowed out of the John Oxley Centre to play
sport and
see movies, says the proposed legislative changes to the Mental
Health Act
appear to be "refinements". Two weeks ago, Nash's teenage son
Wade
committed suicide after suffering years of torment following his
mother's
murder. In May 1996, a letter was sent to the tribunal by now
former
director of secure care services at John Oxley Dr Peter Fama. It
said:
"Should Ross be committed to the Tribunal for trial on a charge
of
manslaughter or murder, I have to report that he is now fit to be
placed
in corrective custody ... There is no clinical need for further
detention
of Ross in hospital." De Jersey has been involved in the process
of
amendments in the new Act and believes the "adjustments" are
satisfactory:
"It's probably a question of how they're implemented. I thought
the
changes were more concerned with image than effecting substantial
change
to the system, calling it a court rather than a tribunal. There
is some
attempt to enhance the openness of the procedures such as the
advice given
by the existing psychiatrists being revealed in open court to the
judge
but they're aspects of streamlining rather than substantive
change." He
says many people are irked by a perceived disproportion between
the
treatment of mentally ill offenders and their victims. "As a
community we
need much more positively to address the situation of victims."
De Jersey
points to the James Bulger murder in the UK eight years ago when
two
10-year-old boys abducted and battered James, two, to death. The
killers
are expected to be freed soon. Says de Jersey: "Whatever one
thinks of
future plans for the young offenders it is extraordinary, if
reportedly
correct, that so little help has been given to the bereft mother
of the
murdered toddler. "Similarly, here, it is generally indefensible
where
victims or the families of victims are not informed of details of
the
likely release of their offenders, and even before that where
they are not
given a proper explanation as to the process and counselling to
help them
comprehend that process and as well the consequences of the
crime. We are
as a community moving towards a greater focus on the position of
victims
but a lot more needs to be done. "The anguish of victims and the
families
of victims that insane offenders appear to escape punishment is
understandable. The issue is whether the community is prepared to
accept
that insane offenders primarily need treatment." The Mental
Health
Tribunal worked on two assumptions, that offenders of unsound
mind should,
in the interests of the community, be treated rather than
punished, and
that a determination whether an offender was of unsound mind
could
responsibly be made by a Supreme Court judge with expert
psychiatric
assistance. "I have wondered whether with the ultimately serious
crimes
such as murder the community may not reasonably demand that in
the
interests of reassurance that the determination be made by a
jury." He
believes the community's longer term interests would best be
served by
medically treating insane offenders in a hospital rather than a
prison,
where if rehabilitated, they could contribute to the community.
"I accept,
however, that in many cases there will be serious residual
concern, for
example, can the offender be trusted, if left unsupervised, to
continue to
take the relevant medication?"
De Jersey admits problems have arisen when offenders, granted
leave,
stopped taking medication but says if they can be relied upon to
maintain
stability through medication it would be inhumane to keep them
locked up.
Continued medical monitoring was necessary. If conditions were
breached
the person should be returned to restricted custody at the
psychiatric
hospital. While the most vulnerable in society deserve compassion
it does
not surprise there is public concern about lack of proper
scrutiny, the
capacity to re-offend and misuse of the legal process by using
insanity as
a defence. IN the general quest to improve treatment provisions
for
patients the 2000 Act says: "The new legislation provides for
involuntary
treatment in the community as an alternative to being an
in-patient in a
mental health service which reflects contemporary clinical
practice and
the principle of reform that involuntary treatment must be in the
least
restrictive form."
Perhaps the overwhelming feeling is patients' rights have
priority over
victims' rights. Ted Flack, spokesman for the Queensland Homicide
Victims
Support Group says the new Act provides a better environment for
victims'
participation, but there are serious flaws. The rights of
homicide victims
were not guaranteed and this caused an inordinate amount of
distress.
"There's still considerable discretion in the hands of the Mental
Health
Court and the Mental Health Review Tribunal as to whether they
would admit
any evidence from the victims. The new Act is framed in such a
way as to
provide guaranteed rights to the person who's suffering from a
mental
illness and those rights come appropriately from the
international
conventions, but there are similar international conventions for
victims
and they are being completely ignored in the Act." Flack says the
primary
purpose of the Mental Health Tribunal is to save money and to
safeguard
the rights of the mentally disabled person. He believes the
criminally
insane can be catered for properly in jail. "The imprecise
science of
psychiatry is not an appropriate set of guidelines for the
release into
the community of dangerous killers," he says.

NS
GCAT : Political/General News | GCRIM : Crime/Courts | GHEA :
Health |
GHOME : Law Enforcement

RE
AUSNZ : Australia and New Zealand | AUSTR : Australia

AN
Document coumai0020010710dx6n005vl
Jul 23 '06 #2

P: n/a
This may be off the mark as you seem to be looking for a table
structure for importing this text file. Here is an overview of a
program I made to handle newspaper obituaries. As you can read, I did
not need to import a text file, but rather typed a few thousand items
myself. Anyway, here is the link.
http://www.psci.net/gramelsp/temp/Anzeiger.htm

Mike Gramelspacher

Jul 23 '06 #3

P: n/a
(A) sample data
>
HD Was Charles Manson temporarily insane when he led a wild
killing
rampage in the US in 1969?
BY By Deborah Cassrels.
WC 1834 words
PD 23 June 2001
SN Courier Mail
SC COUMAI
PG 30
LA English
CY (c) 2001 Queensland Newspapers Pty Ltd

LP Was Charles Manson temporarily insane when he led a wild
killing
rampage in the US in 1969? Clearly he was mad and bad. But would
<SNIP>
>
NS
GCAT : Political/General News | GCRIM : Crime/Courts | GHEA :
Health |
GHOME : Law Enforcement
RE
AUSNZ : Australia and New Zealand | AUSTR : Australia
AN
Document coumai0020010710dx6n005vl
I worked on a database several years ago that did something like this.
(parsed out records form ComSpec, SciSearch and a few other online
databases. Basically, you need to read a line at a time and look for
the "labels" at the beginning of the line. Parse that off, and then
write the value to a type. then when you're done splitting off pieces,
use a recordset to write the information to a single record.

Really helpful, huh?

Basically, read through the text file using LineInput and then split at
the colon (using Split). then use a recordset to write it to your
table.

Jul 23 '06 #4

P: n/a
One thing you might want to examine is this:

' Class File for reading Text files
' Copyright 2000, Chuck Grimsby
' All Rights Reserved
' Use is free without restriction,
' I just keep the copyright!

You will have to google for this message.
This is his code to demonstrate it. Notice that it gives you the
next and previous line to do with what you want.

Sub ReadAFile(strFileName)
' Create a new class internal to your program:
' Test it in the immediate window using your fine name:
' call ReadAFile("C:\Documents and Settings\.......txt")

Dim myTextFile As New clsTextFile
' Local String to play with in your program:
Dim myString As String
Dim intError As Integer

' set the file name to read:
myTextFile.FileName = strFileName
' Don't return blank lines:
myTextFile.NoBlankLines = True
' Count all lines regardless of
' whether or not they are returned:
myTextFile.CountOnlyNonBlankLines = False
' leave any leading spaces:
myTextFile.StripLeadingSpaces = False
' leave the trailing spaces too:
myTextFile.StripTrailingSpaces = False

' open the file, return any errors in doing so
' (class doesn't handle them!):
intError = myTextFile.cfOpenFile
If intError = 0 Then
' no error in opening the file has occurred
' Watch for the end of the file:
While Not myTextFile.EndOfFile
' Tell the class to go to a new line:
myTextFile.csGetALine
' set your string = to the
' current string of the class:
myString = myTextFile.Text
' Show work in the Debug window.
' (Don't do in production!):
Debug.Print myTextFile.LinesRead, myString
' want to see the next line?
' If myTextFile.NextLine = <whateverThen....
' want to see the Last line?
' If myTextFile.PreviousLine = <whateverThen ....
' <do whatever with the string here.>
Wend
Else
' handle the error here!
MsgBox Err.Description
End If
' close the file. We're done with it:
myTextFile.cfCloseFile
' Always set your objects to nothing
' when you're done with them:
Set myTextFile = Nothing
End Sub

Jul 24 '06 #5

P: n/a

pi********@hotmail.com wrote:
Basically, read through the text file using LineInput and then split at
the colon (using Split). then use a recordset to write it to your
table.
Thanks for your reply. I suspect this is much more complex than
described. I did a search in Access and did not find "LineInput" in
help - I have Office 97. A google search suggests it has something to
do with Visual Basic.

Is the option you describe feasiable for a computer moron?

Bob

Jul 24 '06 #6

P: n/a
Thanks for your reply. I searched for the site and found some code.
This is all like Swahili to me. What program/how do I run this code?

regards

Bob
gr******@psci.net wrote:
One thing you might want to examine is this:

' Class File for reading Text files
' Copyright 2000, Chuck Grimsby
' All Rights Reserved
' Use is free without restriction,
' I just keep the copyright!

You will have to google for this message.
This is his code to demonstrate it. Notice that it gives you the
next and previous line to do with what you want.

Sub ReadAFile(strFileName)
' Create a new class internal to your program:
' Test it in the immediate window using your fine name:
' call ReadAFile("C:\Documents and Settings\.......txt")

Dim myTextFile As New clsTextFile
' Local String to play with in your program:
Dim myString As String
Dim intError As Integer

' set the file name to read:
myTextFile.FileName = strFileName
' Don't return blank lines:
myTextFile.NoBlankLines = True
' Count all lines regardless of
' whether or not they are returned:
myTextFile.CountOnlyNonBlankLines = False
' leave any leading spaces:
myTextFile.StripLeadingSpaces = False
' leave the trailing spaces too:
myTextFile.StripTrailingSpaces = False

' open the file, return any errors in doing so
' (class doesn't handle them!):
intError = myTextFile.cfOpenFile
If intError = 0 Then
' no error in opening the file has occurred
' Watch for the end of the file:
While Not myTextFile.EndOfFile
' Tell the class to go to a new line:
myTextFile.csGetALine
' set your string = to the
' current string of the class:
myString = myTextFile.Text
' Show work in the Debug window.
' (Don't do in production!):
Debug.Print myTextFile.LinesRead, myString
' want to see the next line?
' If myTextFile.NextLine = <whateverThen....
' want to see the Last line?
' If myTextFile.PreviousLine = <whateverThen ....
' <do whatever with the string here.>
Wend
Else
' handle the error here!
MsgBox Err.Description
End If
' close the file. We're done with it:
myTextFile.cfCloseFile
' Always set your objects to nothing
' when you're done with them:
Set myTextFile = Nothing
End Sub
Jul 24 '06 #7

P: n/a
Nick,

Thanks. I have across the memo field concept elsewhere. As I have 100s
of records I have to work out a viable way to enetr this data, rathwer
than manually cutting and pasting,.

regards

Bob
Nick 'The Database Guy' wrote:
The first identifing fields can be stored as text, for the last field I
would recommend that you used a memo field, this can store up to 65,535
characters.

Good luck,

Nick

bgreen wrote:
I am hoping for some assistance with formatting a large text file which
consists of a series of individual records. Each record includes
specific labels/field names (a sample of 1 record (one of the longest
ones) is below - at end of post. What I want to do is reformat the
data, so that each individual record becomes a row (some cells will
have a lot of text). For example, the column variables I want are (a)
HD in one column (b) BY in one column (c) WC data in one column,
(d) PD data in one column, (e) SC data in one column (f) PG data in one
column & g) LP and TD text in one column - this column can contain
quite a lot of text, e.g 1900 words. The other fields are unwanted

If there were 150 individual records, when formatted this would be a 7
column by 150 row dataset.

Can I save such data into Access so each of the above fields becomes a
field in Access, and then, if I wanted to select certain fields for
exporting as a text file to be analysed by another program?

If this is possible, any suggestions or assistance is much appreciated,
Bob

(A) sample data

HD Was Charles Manson temporarily insane when he led a wild
killing
rampage in the US in 1969?
BY By Deborah Cassrels.
WC 1834 words
PD 23 June 2001
SN Courier Mail
SC COUMAI
PG 30
LA English
CY (c) 2001 Queensland Newspapers Pty Ltd

LP Was Charles Manson temporarily insane when he led a wild
killing
rampage in the US in 1969? Clearly he was mad and bad. But would
Queensland have placed him before its Mental Health Tribunal,
found him of
unsound mind at the time of his crimes, institutionalised him and

"treated" his illness? WHY is Queensland the only jurisdiction in
the
Commonwealth with a Mental Health Tribunal which establishes if
an accused
is fit to face trial or of unsound mind at the time of an alleged
offence?
Why is mental incompetence not determined in an adversarial court
by a
jury? Under the Mental Health Act 1974, the tribunal, a statutory
body
operating since 1985, comprises three-yearly appointments of a
Supreme
Court judge and two assisting psychiatrists, whose advice does
not have to
be accepted. The judge alone constitutes the tribunal, an
inquisitorial
process conducted in the Supreme Court in Brisbane.

TD Victims or family are not notified of hearings or allowed to
submit
victim impact statements. They are prohibited from talking to the
media
until 28 days after the decision. And when patients return to the

community there is no requirement for neighbours or victims to be

notified. Is this legislation enlightened or are we just suckers,
falling
for time and money-saving strategies? The tribunal has earned a
reputation
as progressive, humane and economical among some judges who have
presided
over it. The inaugural chair, former Supreme Court judge Angelo
Vasta QC,
thinks the tribunal system is "enlightened" and "it saves an
enormous
amount of expenditure". He points to the humane side of treating
the ill
in a secure hospital rather than punishing them for offences but
is
uncomfortable with borderline cases. "Whether people are mad or
bad ought
to be established by a very thorough investigation.
The associated Patient Review Tribunals (of which there are five)
consist
of three to six members, including the chair who is a legal
officer, a
medical practitioner and a mental health professional. A
psychiatrist is
not required. The other three have no specific qualifications and
can
include former patients. The tribunals operate in closed hearings
and
patients of unsound mind or unfit for trial are reviewed every 12
months.
Leave is granted either by the Mental Health Tribunal or the
Patient
Review Tribunal, which determine when a restricted patient is
discharged
into the community. Says the Director of Mental Health, Dr Peggy
Brown:
"In the case of serious offences you can be assured the period of

monitoring is quite lengthy." Under the Mental Health Act 2000 to
be
implemented late this year, the tribunal will be replaced by a
Mental
Health Court and the Patient Review Tribunal by the Mental Health
Review
Tribunal. Queensland Health Minister Wendy Edmond says the name
change
reflects transparency, with proceedings under oath and
cross-examination
of witnesses. The legislation represents "real change to the
rights of
victims of crime". But there is still an embargo on publishing
decisions
in the media.
Dr Brown says when patients are granted leave, victims or
families can
apply to be notified but decisions will be made on individual
cases. "The
(new) tribunal has to establish that there are reasonable grounds
for the
notification order to be made ... and it's also an appealable
decision,"
returning to the Mental Health Court.
Brown says there are efficiencies in the new legislation but
"it's not
about saving money". The main advantages were that victims could
make
submissions to both bodies. Concerns still might not be addressed
but
reasons were expected to be provided. The court's composition and
sole
power of the judge will be retained. Victims or relatives can be
notified
of hearings and decisions about the patient. If not, reasons must
be
provided. The Patient Review Tribunals will be replaced by one
tribunal
with hearings still closed. It will comprise up to five members
including
a president (a lawyer of at least seven years' standing),
psychiatrist or
medical practitioner and community members and it will be chaired
by a
legal officer. Leave will be approved by the corresponding
previous
bodies. Chief Justice Paul de Jersey who presided over the 1995
case of
Ross Farrah, a paranoid schizophrenic, who after murdering his
girlfriend,
Christine Nash, was allowed out of the John Oxley Centre to play
sport and
see movies, says the proposed legislative changes to the Mental
Health Act
appear to be "refinements". Two weeks ago, Nash's teenage son
Wade
committed suicide after suffering years of torment following his
mother's
murder. In May 1996, a letter was sent to the tribunal by now
former
director of secure care services at John Oxley Dr Peter Fama. It
said:
"Should Ross be committed to the Tribunal for trial on a charge
of
manslaughter or murder, I have to report that he is now fit to be
placed
in corrective custody ... There is no clinical need for further
detention
of Ross in hospital." De Jersey has been involved in the process
of
amendments in the new Act and believes the "adjustments" are
satisfactory:
"It's probably a question of how they're implemented. I thought
the
changes were more concerned with image than effecting substantial
change
to the system, calling it a court rather than a tribunal. There
is some
attempt to enhance the openness of the procedures such as the
advice given
by the existing psychiatrists being revealed in open court to the
judge
but they're aspects of streamlining rather than substantive
change." He
says many people are irked by a perceived disproportion between
the
treatment of mentally ill offenders and their victims. "As a
community we
need much more positively to address the situation of victims."
De Jersey
points to the James Bulger murder in the UK eight years ago when
two
10-year-old boys abducted and battered James, two, to death. The
killers
are expected to be freed soon. Says de Jersey: "Whatever one
thinks of
future plans for the young offenders it is extraordinary, if
reportedly
correct, that so little help has been given to the bereft mother
of the
murdered toddler. "Similarly, here, it is generally indefensible
where
victims or the families of victims are not informed of details of
the
likely release of their offenders, and even before that where
they are not
given a proper explanation as to the process and counselling to
help them
comprehend that process and as well the consequences of the
crime. We are
as a community moving towards a greater focus on the position of
victims
but a lot more needs to be done. "The anguish of victims and the
families
of victims that insane offenders appear to escape punishment is
understandable. The issue is whether the community is prepared to
accept
that insane offenders primarily need treatment." The Mental
Health
Tribunal worked on two assumptions, that offenders of unsound
mind should,
in the interests of the community, be treated rather than
punished, and
that a determination whether an offender was of unsound mind
could
responsibly be made by a Supreme Court judge with expert
psychiatric
assistance. "I have wondered whether with the ultimately serious
crimes
such as murder the community may not reasonably demand that in
the
interests of reassurance that the determination be made by a
jury." He
believes the community's longer term interests would best be
served by
medically treating insane offenders in a hospital rather than a
prison,
where if rehabilitated, they could contribute to the community.
"I accept,
however, that in many cases there will be serious residual
concern, for
example, can the offender be trusted, if left unsupervised, to
continue to
take the relevant medication?"
De Jersey admits problems have arisen when offenders, granted
leave,
stopped taking medication but says if they can be relied upon to
maintain
stability through medication it would be inhumane to keep them
locked up.
Continued medical monitoring was necessary. If conditions were
breached
the person should be returned to restricted custody at the
psychiatric
hospital. While the most vulnerable in society deserve compassion
it does
not surprise there is public concern about lack of proper
scrutiny, the
capacity to re-offend and misuse of the legal process by using
insanity as
a defence. IN the general quest to improve treatment provisions
for
patients the 2000 Act says: "The new legislation provides for
involuntary
treatment in the community as an alternative to being an
in-patient in a
mental health service which reflects contemporary clinical
practice and
the principle of reform that involuntary treatment must be in the
least
restrictive form."
Perhaps the overwhelming feeling is patients' rights have
priority over
victims' rights. Ted Flack, spokesman for the Queensland Homicide
Victims
Support Group says the new Act provides a better environment for
victims'
participation, but there are serious flaws. The rights of
homicide victims
were not guaranteed and this caused an inordinate amount of
distress.
"There's still considerable discretion in the hands of the Mental
Health
Court and the Mental Health Review Tribunal as to whether they
would admit
any evidence from the victims. The new Act is framed in such a
way as to
provide guaranteed rights to the person who's suffering from a
mental
illness and those rights come appropriately from the
international
conventions, but there are similar international conventions for
victims
and they are being completely ignored in the Act." Flack says the
primary
purpose of the Mental Health Tribunal is to save money and to
safeguard
the rights of the mentally disabled person. He believes the
criminally
insane can be catered for properly in jail. "The imprecise
science of
psychiatry is not an appropriate set of guidelines for the
release into
the community of dangerous killers," he says.

NS
GCAT : Political/General News | GCRIM : Crime/Courts | GHEA :
Health |
GHOME : Law Enforcement

RE
AUSNZ : Australia and New Zealand | AUSTR : Australia

AN
Document coumai0020010710dx6n005vl
Jul 24 '06 #8

P: n/a
Mike,

thanks. An interesting project. AS you note I am looking for a way to
avoid doing such a task manually,

regards

Bob
gr******@psci.net wrote:
This may be off the mark as you seem to be looking for a table
structure for importing this text file. Here is an overview of a
program I made to handle newspaper obituaries. As you can read, I did
not need to import a text file, but rather typed a few thousand items
myself. Anyway, here is the link.
http://www.psci.net/gramelsp/temp/Anzeiger.htm

Mike Gramelspacher
Jul 24 '06 #9

P: n/a
If you are truly a 'computer moron", then I suggest you walk away from
this project and hire someone to do this for you. You can consider the
class module as a sort of black box that will magically give you a line
of text from your file. But then, what will you do with it? What is
your intended method for determining what part of that text line should
be inserted into a table column. Do you have the structure of the
database table worked out already? Enough.

Jul 24 '06 #10

P: n/a
>From the Access VB Help File and rather simple.

Sub TestLineInput()
Dim TextLine
' Open file
Open "C:\Documents and Settings\..... whatever.txt" For Input As #1
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
Debug.Print TextLine ' Print to the Immediate window.
Loop
Close #1 ' Close file.
End Sub

Press Ctrl-G to get to a code window and copy this into a module and
save it. Be sure to edit it and type a real text file path and name in
place of what is there now. Then run it in the immediate window by
typing call TestLineInput and pressing return.

Jul 24 '06 #11

P: n/a
I must be a crackhead, because I have most of this working... the only
hard part are the two multi-line text fields.

Option Compare Binary

Public Sub ReadAndParseTextfile(ByVal strInputFile As String)

Dim intInputFile As Integer
Dim strLineIn As String
Dim strLabel As String
Dim strText As String
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblArticle", dbOpenTable,
dbAppendOnly)

intInputFile = FreeFile
Open strInputFile For Input As intInputFile

rs.AddNew

Do Until EOF(intInputFile)
Line Input #intInputFile, strLineIn
strLineIn = Trim$(strLineIn) '--trim off excess characters
strLabel = Trim$(Left$(strLineIn, 3))
strText = ""
Select Case strLabel
Case "BY", "WC", "PD", "SN", "SC", "PG", "LA", "CY", "HD"
'--single line text
strText = Right$(strLineIn, Len(strLineIn) - 3)
Debug.Print strLabel & ":" & strText

rs.Fields(strLabel) = strText
Case "LP", "TD"
'--multi-line text
'-- keep appendinguntil strText =""
strText = Right$(strLineIn, Len(strLineIn) - 3)
Debug.Print strLabel & ":" & strText
Case Else 'must be a text line, so append it to some text
chunk.
strText = strText & strLineIn
'Debug.Print "---" & strText
End Select

Loop

rs.Update

Exit_Routine:
On Error Resume Next
Close
'Kill strInputFile
'rst.Close
'Set rst = Nothing
'Set db = Nothing
Exit Sub

Err_Routine:
Select Case Err
Case 53
'File not found.
Resume Exit_Routine
Case Else
Beep
MsgBox "Error Number: " & Err.Number & "@" _
& Err.Description & "@" _
& "Project: " & Err.Source & ". Procedure: " & conProcName, _
vbOKOnly + vbInformation, _
"Unexpected Error"
End Select
Resume Exit_Routine

End Sub

If I write everything but the last two big text fields to my table,
everything's fine. but I'm having a hard time figuring out how to
determine where the end of a section is on the big ones. I suppose I
could break down and figure out how Chuck's code works and then peek
ahead... if the peek ahead value is one of the labels, then this line
is finished and write it somewhere.
Any suggestions? (Yeah, go to sleep!)

Thanks,
Pieter

Jul 25 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.