Importing Text File

[Guest]

I have a text file that I need to import into my Access Database. The problem
is, this is the first report I have come across that has data for one patient
(I work in a hospital) spread over 5 rows. Example:

Name Number Location Visit Date Visit Time
Jim.S 999999
CDA
07/05/05
1400
Sally.G 8888888
CDA
07/05/05
1300

I need to get it into a table like this:
Name Number Location VisitDate Time
Jim.S 999999 CDA 07/05/05 1400
SallyG 888888 CDA 07/05/05 1300

I just know the best way to deal with a report like this. I was thinking I
could write expressions that would list the corresponding location, visit
date, and time with the correct patient....but have had no luck.
I really appreciate any help.
Good night and thank you.

 

[John Nurick]

Hi Antonio,

Because of the way newsgroup software handles text I can't be sure that
your sample data reaches me the way it left you. For instance, you say
the data for one patient is spread over four rows, but the sample shows
it in only four.

But it sounds as if what you have is something like this:

* a record starts with a name at the very beginning of a line.
* the first line of a record contains name and number, separated by two
or more spaces (or maybe one or more tab characters).
* subsequent lines of a record each contain one field, preceded by
multiple spaces or tabs.
* every record contains the same number of lines in the same order.

There are several ways to tackle this, including

1) search and replace, using a text editor or word processor that can do
wildcard searches. The general idea would be

a) delete all the trailing spaces (i.e. replace each instance of
[spaces + linebreak] with just [linebreak])

b) replace each instance of [linebreak + spaces] with [tab]

c) replace each remaining instance of [more than one space] with [tab]

The result will be a tab-delimited text file that Access can import.

2) write a little program in your favourite language to do the same sort
of processing as (1) above. Here's how it can be done in Perl:

#start of code
while (<>) { #read input line by line
s/\s+$// ; #trim trailing whitespace

if (m/^\w/) { #no leading whitespace: new record
print "$record\n" if $record; #print previous record, if any
s/ {2,}/\t/g; #replace spaces with tab
$record = $_; #start accumulating new record
} else {
s/^\s+//; #trim leading whitepace
$record .= "\t$_" ; #concatenate tab and field value
}
}
print "$record\n"; #print last record
#end of code

3) write VBA code to read the text file line by line, assemble records
and append them directly to your table. IMHO this is only worth doing if
you need to automate the import process.

 

[Guest]

John. Thank you so much for your response. You are correct, I mis counted.
Below is a better example of what I have on my hands. First Row Patient, 2nd,
3rd, 4th row the remaining info. Your first option mentioned using a word
processor, will microsoft word work? I attempted to use its Find and Replace
option but I did not know how to represent [space+linebreak] in the find
field.
I have had no experience with Perl and little with VBA. Do you have any
examples of VBA code that "write VBA code to read the text file line by line,
assemble records

and append them directly to your table."? Or is that something normally covered in a three week course at New Horizons?

Thanks again for your help.

ID Name VisitDate VisitTime Location
M000443990 MORA,EDUARDO N M/30
07/05/05
1440

CARDIOLOGY LOCUM MD

Hi Antonio,

Because of the way newsgroup software handles text I can't be sure that
your sample data reaches me the way it left you. For instance, you say
the data for one patient is spread over four rows, but the sample shows
it in only four.

But it sounds as if what you have is something like this:

* a record starts with a name at the very beginning of a line.
* the first line of a record contains name and number, separated by two
or more spaces (or maybe one or more tab characters).
* subsequent lines of a record each contain one field, preceded by
multiple spaces or tabs.
* every record contains the same number of lines in the same order.

There are several ways to tackle this, including

1) search and replace, using a text editor or word processor that can do
wildcard searches. The general idea would be

a) delete all the trailing spaces (i.e. replace each instance of
[spaces + linebreak] with just [linebreak])

b) replace each instance of [linebreak + spaces] with [tab]

c) replace each remaining instance of [more than one space] with [tab]

The result will be a tab-delimited text file that Access can import.

2) write a little program in your favourite language to do the same sort
of processing as (1) above. Here's how it can be done in Perl:

#start of code
while (<>) { #read input line by line
s/\s+$// ; #trim trailing whitespace

if (m/^\w/) { #no leading whitespace: new record
print "$record\n" if $record; #print previous record, if any
s/ {2,}/\t/g; #replace spaces with tab
$record = $_; #start accumulating new record
} else {
s/^\s+//; #trim leading whitepace
$record .= "\t$_" ; #concatenate tab and field value
}
}
print "$record\n"; #print last record
#end of code

3) write VBA code to read the text file line by line, assemble records
and append them directly to your table. IMHO this is only worth doing if
you need to automate the import process.

I have a text file that I need to import into my Access Database. The problem
is, this is the first report I have come across that has data for one patient
(I work in a hospital) spread over 5 rows. Example:

Name Number Location Visit Date Visit Time
Jim.S 999999
CDA
07/05/05
1400
Sally.G 8888888
CDA
07/05/05
1300

I need to get it into a table like this:
Name Number Location VisitDate Time
Jim.S 999999 CDA 07/05/05 1400
SallyG 888888 CDA 07/05/05 1300

I just know the best way to deal with a report like this. I was thinking I
could write expressions that would list the corresponding location, visit
date, and time with the correct patient....but have had no luck.
I really appreciate any help.
Good night and thank you.

 

[Guest]

I posted a question regarding the VBA code example John was talking about in
this question on the Access Programming VBA group on 7/3/05.

John. Thank you so much for your response. You are correct, I mis counted.
Below is a better example of what I have on my hands. First Row Patient, 2nd,
3rd, 4th row the remaining info. Your first option mentioned using a word
processor, will microsoft word work? I attempted to use its Find and Replace
option but I did not know how to represent [space+linebreak] in the find
field.
I have had no experience with Perl and little with VBA. Do you have any
examples of VBA code that "write VBA code to read the text file line by line,
assemble records

and append them directly to your table."? Or is that something normally covered in a three week course at New Horizons?

Thanks again for your help.

ID Name VisitDate VisitTime Location
M000443990 MORA,EDUARDO N M/30
07/05/05
1440

CARDIOLOGY LOCUM MD

Hi Antonio,

Because of the way newsgroup software handles text I can't be sure that
your sample data reaches me the way it left you. For instance, you say
the data for one patient is spread over four rows, but the sample shows
it in only four.

But it sounds as if what you have is something like this:

* a record starts with a name at the very beginning of a line.
* the first line of a record contains name and number, separated by two
or more spaces (or maybe one or more tab characters).
* subsequent lines of a record each contain one field, preceded by
multiple spaces or tabs.
* every record contains the same number of lines in the same order.

There are several ways to tackle this, including

1) search and replace, using a text editor or word processor that can do
wildcard searches. The general idea would be

a) delete all the trailing spaces (i.e. replace each instance of
[spaces + linebreak] with just [linebreak])

b) replace each instance of [linebreak + spaces] with [tab]

c) replace each remaining instance of [more than one space] with [tab]

The result will be a tab-delimited text file that Access can import.

2) write a little program in your favourite language to do the same sort
of processing as (1) above. Here's how it can be done in Perl:

#start of code
while (<>) { #read input line by line
s/\s+$// ; #trim trailing whitespace

if (m/^\w/) { #no leading whitespace: new record
print "$record\n" if $record; #print previous record, if any
s/ {2,}/\t/g; #replace spaces with tab
$record = $_; #start accumulating new record
} else {
s/^\s+//; #trim leading whitepace
$record .= "\t$_" ; #concatenate tab and field value
}
}
print "$record\n"; #print last record
#end of code

3) write VBA code to read the text file line by line, assemble records
and append them directly to your table. IMHO this is only worth doing if
you need to automate the import process.

I have a text file that I need to import into my Access Database. The problem
is, this is the first report I have come across that has data for one patient
(I work in a hospital) spread over 5 rows. Example:

Name Number Location Visit Date Visit Time
Jim.S 999999
CDA
07/05/05
1400
Sally.G 8888888
CDA
07/05/05
1300

I need to get it into a table like this:
Name Number Location VisitDate Time
Jim.S 999999 CDA 07/05/05 1400
SallyG 888888 CDA 07/05/05 1300

I just know the best way to deal with a report like this. I was thinking I
could write expressions that would list the corresponding location, visit
date, and time with the correct patient....but have had no luck.
I really appreciate any help.
Good night and thank you.

 

[John Nurick]

Hi Antonio,

Your new example raises new questions. Does
MORA,EDUARDO N M/30
need to go into one field
PatientName: MORA,EDUARDO N M/30
or several, e.g.
FamilyName: MORA
FirstName: EDUARDO
MiddleName: N
Sex: M
Age: 30

Similarly, does the last row parse as
Location: CARDIOLOGY LOCUM MD
or something like
Location: CARDIOLOGY
Status: LOCUM MD
?

The searching and replacing can be done with Microsoft Word's wildcard
search feature. It's weak and buggy compared with standard pattern
matching engines (such as the ones in Perl, Python, VBScript, etc.),
but the following should help you get started. Study the help file
carefully to understand them:

Delete trailing spaces: replace

{1,}^013

with
^p

Change [linebreak + spaces] to [tab]: replace
^013 *<
with
^t

Change [more than one space between words] to [tab]: replace

{2,}< with
^t



John. Thank you so much for your response. You are correct, I mis counted.
Below is a better example of what I have on my hands. First Row Patient, 2nd,
3rd, 4th row the remaining info. Your first option mentioned using a word
processor, will microsoft word work? I attempted to use its Find and Replace
option but I did not know how to represent [space+linebreak] in the find
field.
I have had no experience with Perl and little with VBA. Do you have any
examples of VBA code that "write VBA code to read the text file line by line,
assemble records

and append them directly to your table."? Or is that something normally covered in a three week course at New Horizons?

Thanks again for your help.

ID Name VisitDate VisitTime Location
M000443990 MORA,EDUARDO N M/30
07/05/05
1440

CARDIOLOGY LOCUM MD

Hi Antonio,

Because of the way newsgroup software handles text I can't be sure that
your sample data reaches me the way it left you. For instance, you say
the data for one patient is spread over four rows, but the sample shows
it in only four.

But it sounds as if what you have is something like this:

* a record starts with a name at the very beginning of a line.
* the first line of a record contains name and number, separated by two
or more spaces (or maybe one or more tab characters).
* subsequent lines of a record each contain one field, preceded by
multiple spaces or tabs.
* every record contains the same number of lines in the same order.

There are several ways to tackle this, including

1) search and replace, using a text editor or word processor that can do
wildcard searches. The general idea would be

a) delete all the trailing spaces (i.e. replace each instance of
[spaces + linebreak] with just [linebreak])

b) replace each instance of [linebreak + spaces] with [tab]

c) replace each remaining instance of [more than one space] with [tab]

The result will be a tab-delimited text file that Access can import.

2) write a little program in your favourite language to do the same sort
of processing as (1) above. Here's how it can be done in Perl:

#start of code
while (<>) { #read input line by line
s/\s+$// ; #trim trailing whitespace

if (m/^\w/) { #no leading whitespace: new record
print "$record\n" if $record; #print previous record, if any
s/ {2,}/\t/g; #replace spaces with tab
$record = $_; #start accumulating new record
} else {
s/^\s+//; #trim leading whitepace
$record .= "\t$_" ; #concatenate tab and field value
}
}
print "$record\n"; #print last record
#end of code

3) write VBA code to read the text file line by line, assemble records
and append them directly to your table. IMHO this is only worth doing if
you need to automate the import process.

I have a text file that I need to import into my Access Database. The problem
is, this is the first report I have come across that has data for one patient
(I work in a hospital) spread over 5 rows. Example:

Name Number Location Visit Date Visit Time
Jim.S 999999
CDA
07/05/05
1400
Sally.G 8888888
CDA
07/05/05
1300

I need to get it into a table like this:
Name Number Location VisitDate Time
Jim.S 999999 CDA 07/05/05 1400
SallyG 888888 CDA 07/05/05 1300

I just know the best way to deal with a report like this. I was thinking I
could write expressions that would list the corresponding location, visit
date, and time with the correct patient....but have had no luck.
I really appreciate any help.
Good night and thank you.

 

[Guest]

Thank you very much John. The word processor hints AND the VBA code have
helped tremendously.
Antonio.

Hi Antonio,

Your new example raises new questions. Does
MORA,EDUARDO N M/30
need to go into one field
PatientName: MORA,EDUARDO N M/30
or several, e.g.
FamilyName: MORA
FirstName: EDUARDO
MiddleName: N
Sex: M
Age: 30

Similarly, does the last row parse as
Location: CARDIOLOGY LOCUM MD
or something like
Location: CARDIOLOGY
Status: LOCUM MD
?

The searching and replacing can be done with Microsoft Word's wildcard
search feature. It's weak and buggy compared with standard pattern
matching engines (such as the ones in Perl, Python, VBScript, etc.),
but the following should help you get started. Study the help file
carefully to understand them:

Delete trailing spaces: replace
> {1,}^013
with
^p

Change [linebreak + spaces] to [tab]: replace
^013 *<
with
^t

Change [more than one space between words] to [tab]: replace
> {2,}<
with
^t

John. Thank you so much for your response. You are correct, I mis counted.
Below is a better example of what I have on my hands. First Row Patient, 2nd,
3rd, 4th row the remaining info. Your first option mentioned using a word
processor, will microsoft word work? I attempted to use its Find and Replace
option but I did not know how to represent [space+linebreak] in the find
field.
I have had no experience with Perl and little with VBA. Do you have any
examples of VBA code that "write VBA code to read the text file line by line,
assemble records

and append them directly to your table."? Or is that something normally covered in a three week course at New Horizons?

Thanks again for your help.

ID Name VisitDate VisitTime Location
M000443990 MORA,EDUARDO N M/30
07/05/05
1440

CARDIOLOGY LOCUM MD

Hi Antonio,

Because of the way newsgroup software handles text I can't be sure that
your sample data reaches me the way it left you. For instance, you say
the data for one patient is spread over four rows, but the sample shows
it in only four.

But it sounds as if what you have is something like this:

* a record starts with a name at the very beginning of a line.
* the first line of a record contains name and number, separated by two
or more spaces (or maybe one or more tab characters).
* subsequent lines of a record each contain one field, preceded by
multiple spaces or tabs.
* every record contains the same number of lines in the same order.

There are several ways to tackle this, including

1) search and replace, using a text editor or word processor that can do
wildcard searches. The general idea would be

a) delete all the trailing spaces (i.e. replace each instance of
[spaces + linebreak] with just [linebreak])

b) replace each instance of [linebreak + spaces] with [tab]

c) replace each remaining instance of [more than one space] with [tab]

The result will be a tab-delimited text file that Access can import.

2) write a little program in your favourite language to do the same sort
of processing as (1) above. Here's how it can be done in Perl:

#start of code
while (<>) { #read input line by line
s/\s+$// ; #trim trailing whitespace

if (m/^\w/) { #no leading whitespace: new record
print "$record\n" if $record; #print previous record, if any
s/ {2,}/\t/g; #replace spaces with tab
$record = $_; #start accumulating new record
} else {
s/^\s+//; #trim leading whitepace
$record .= "\t$_" ; #concatenate tab and field value
}
}
print "$record\n"; #print last record
#end of code

3) write VBA code to read the text file line by line, assemble records
and append them directly to your table. IMHO this is only worth doing if
you need to automate the import process.




On Fri, 1 Jul 2005 23:51:01 -0700, "Antonio"

I have a text file that I need to import into my Access Database. The problem
is, this is the first report I have come across that has data for one patient
(I work in a hospital) spread over 5 rows. Example:

Name Number Location Visit Date Visit Time
Jim.S 999999
CDA
07/05/05
1400
Sally.G 8888888
CDA
07/05/05
1300

I need to get it into a table like this:
Name Number Location VisitDate Time
Jim.S 999999 CDA 07/05/05 1400
SallyG 888888 CDA 07/05/05 1300

I just know the best way to deal with a report like this. I was thinking I
could write expressions that would list the corresponding location, visit
date, and time with the correct patient....but have had no luck.
I really appreciate any help.
Good night and thank you.