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

Req: Help with Imported data - multiple entries in a single data field.

P: n/a
ccr
Please view in a text editor so that the columnar text lines up. I
used Terminal 9pt font to compose this post and copied/pasted to my
newsreader program.

I am writing in the hope that one of you Visual Basic experts may
provide a solution, or at least some insight, to the problem described
below. I am not a programmer, but am fairly knowledgeable about
computers. If necessary, I could flowchart a process to do what needs
to be done. Unfortunately, I would not know how to go about coding it
in a VB script. Maybe I'll get lucky and find someone through this
post that has had a similar experience importing third party data. . .

The scenario:

A company we contract to provide centralized medical billing for our
"clients" allows review of individual claims via their internet site.
My goal is to import their data about our business to an MS Access
database, which I could then relate to internal data about our
patients.

At my request, they have created a custom report that exports all
related patient records to a tab-delimited file, which I have
subsequently imported to Access. My problem stems from the way data
is maintained in their system (I am told they use Lotus Notes) wherein
multiple dates, medical codes and related charges may be contained in
a single data field. Data in their exported file has this general
format:

Key Claim# Patient Begin Date End Date
Procedure Code Billed Charge
1 1 Doe, John 1/1/04 1/1/04 555.1
$198.00
2 2 Moss, Pete 1/1/04 1/2/04 1/1/04 1/2/04 101.1
203.6 $500.00 $375.00

For the sake of brevity, I have limited my example to 2 records. In
practice, all records contain at least one entry per field, but may
contain multiple entries. The actual file contains several fields in
addition to those listed.

In order to make the data useful, I need to find a way to expand each
record with multiple entries to new records. Claim# 2 in the example
above would then become:

Key Claim# Patient Begin Date End Date
Procedure Code Billed Charge
1 1 Doe, John 1/1/04 1/1/04 555.1
$198.00
2 2a Moss, Pete 1/1/04 1/1/04 101.1
$500.00
3 2b Moss, Pete 1/2/04 1/2/04 203.6
$375.00

Please post a follow up message if you may be able to help. The email
address listed in this message is not valid.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
> The scenario:

A company we contract to provide centralized medical billing for our
"clients" allows review of individual claims via their internet site.
My goal is to import their data about our business to an MS Access
database, which I could then relate to internal data about our
patients.

At my request, they have created a custom report that exports all
related patient records to a tab-delimited file, which I have
subsequently imported to Access. My problem stems from the way data
is maintained in their system (I am told they use Lotus Notes) wherein
multiple dates, medical codes and related charges may be contained in
a single data field. Data in their exported file has this general
format:

Key Claim# Patient Begin Date End Date
Procedure Code Billed Charge
1 1 Doe, John 1/1/04 1/1/04 555.1
$198.00
2 2 Moss, Pete 1/1/04 1/2/04 1/1/04 1/2/04 101.1
203.6 $500.00 $375.00

For the sake of brevity, I have limited my example to 2 records. In
practice, all records contain at least one entry per field, but may
contain multiple entries. The actual file contains several fields in
addition to those listed.

In order to make the data useful, I need to find a way to expand each
record with multiple entries to new records. Claim# 2 in the example
above would then become:

Key Claim# Patient Begin Date End Date
Procedure Code Billed Charge
1 1 Doe, John 1/1/04 1/1/04 555.1
$198.00
2 2a Moss, Pete 1/1/04 1/1/04 101.1
$500.00
3 2b Moss, Pete 1/2/04 1/2/04 203.6
$375.00

Please post a follow up message if you may be able to help. The email
address listed in this message is not valid.


Oh, this is gonna be ugly. Are you quite sure you can't get the data
in any other format? This repeating field stuff is going to be a
nightmare. The only way I can think of doing it is reading a line at
a time, and using the Split function to break it up. Even so, that's
going to be a serious pain. If I were you, I would ask them what
formats they can export to. If you can get normal tab-delimited text
files, then you're there. You'd create an import specification and
then just refer to it when you wanted to import some data...
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.