Re: Importing Text File

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


On Sun, 3 Jul 2005 00:14:01 -0700, "Antonio"
<Antonio@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>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
>
>
>"John Nurick" wrote:
>
>> 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"
>> <Antonio@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>>
>> >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 [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.