Problems with MUMPS conversion to CSV

I'm consulting at a healthcare clinic that is in the process of changing
from MUMPS to MS Access. I am charged with building a new access DB for
them, and then importing all the old MUMPS data from CSV files.

Each table in MUMPS is downloaded to it's own CSV file, except for one
table which is so large that the columns are broken up across 3 files.
It works like this: TABL1.CSV has columns 1-60, TABL2.CSV 61-140,
TABL3.CSV 141-180. Each row represents a record, and the first field of
each row is the PK, so that the data from the three different files can
be re-joined.

Here is the problem: about 1000-2000 records (each file has between 50
and 70k) do not begin on a newline, and instead just look like a
continuation of the previous row. When doing a CSV import into Access,
these records get lost.

Any idea why this is happening? The in-house MUMPS guy is stumped. The
problem only occurs in the CSV files that make up this one large table,
which makes me suspect some kind of problem in handling the amount of
data that's getting dumped. We've re-run the MUMPS queries and the
problem has not gone away and the same records are affected.

--
Posted via http://dbforums.com

Kinda sounds like the length of some of the data lines might be exceeding
the maximum length (or the length chosen or defaulted to when the output
device is opened). This can cause spurious CR/LF's (or missing ones). Are
you writing a discreet CR/LF before each record? What version of MUMPS?
How are you opening the device? Have you examined any of the problem
records (with a simple text editor capable of opening large files)?
-Seth Tiven

"raphaelc" <member34237@dbforums.com> wrote in message
news:3151535.1059161033@dbforums.com...
>
> I'm consulting at a healthcare clinic that is in the process of changing
> from MUMPS to MS Access. I am charged with building a new access DB for
> them, and then importing all the old MUMPS data from CSV files.
>
> Each table in MUMPS is downloaded to it's own CSV file, except for one
> table which is so large that the columns are broken up across 3 files.
> It works like this: TABL1.CSV has columns 1-60, TABL2.CSV 61-140,
> TABL3.CSV 141-180. Each row represents a record, and the first field of
> each row is the PK, so that the data from the three different files can
> be re-joined.
>
> Here is the problem: about 1000-2000 records (each file has between 50
> and 70k) do not begin on a newline, and instead just look like a
> continuation of the previous row. When doing a CSV import into Access,
> these records get lost.
>
> Any idea why this is happening? The in-house MUMPS guy is stumped. The
> problem only occurs in the CSV files that make up this one large table,
> which makes me suspect some kind of problem in handling the amount of
> data that's getting dumped. We've re-run the MUMPS queries and the
> problem has not gone away and the same records are affected.
>
> --
> Posted via http://dbforums.com

Hi Seth,

I'll start with the last question first. I have examined the problem
records in a simple text editor. I can see no control characters or
anything out of the ordinary in the problem record or the record that
preceeds it. There is simply no newline before the start of certain
records. It looks like this:

"good_record_id","blah","blah",
"bad_record_1","blah","blah","bad_record_2","blah" ,"blah",
"good_record_id","blah","blah",
etc...

There doesn't seem to be any pattern to the frequency or location of the
problem records.

At the end of this response, I've pasted the code we use to export. The
version of Mumps we are using is MSM, VA Fileman Version 22.0.

Thanks for your help. And here's the code:

TBPAT1 template

FIRST EXPORT FIELD: INTERNAL(NAME)//
THEN EXPORT FIELD: NAME//
THEN EXPORT FIELD: INVESTIGATOR//
THEN EXPORT FIELD: CC#//
THEN EXPORT FIELD: MR #//
THEN EXPORT FIELD: WHERE PATIENT RECEIVED CARE//
THEN EXPORT FIELD: DATE TB CLINIC RECORD OPENED//
THEN EXPORT FIELD: DATE TB CLINIC RECORD CLOSED//
THEN EXPORT FIELD: PAST DX TB//
THEN EXPORT FIELD: PAST DX TB COMMENTS//
THEN EXPORT FIELD: PAST TX TB//
THEN EXPORT FIELD: PAST TX TB COMMENTS//
THEN EXPORT FIELD: PMD//
THEN EXPORT FIELD: HISTORY OF POS PPD?//
THEN EXPORT FIELD: FINAL TB EVALUATION STATUS//
THEN EXPORT FIELD: STATUS AT CLOSURE/INH PATIENT//
THEN EXPORT FIELD: COMMENTS//
THEN EXPORT FIELD: CONVERTER//
THEN EXPORT FIELD: INDEX CASE//
THEN EXPORT FIELD: COMPLETED 6-MO POST-TX F/U//
THEN EXPORT FIELD: COMPLETED 12-MO POST-TX F/U//
THEN EXPORT FIELD: PMD NEXT ACTION//
THEN EXPORT FIELD: DATE HC REFERRED PT//
THEN EXPORT FIELD: DAILY SUPERVISED PT (DOT)//
THEN EXPORT FIELD: SITE OF DOT//
THEN EXPORT FIELD: WEEKS IN DOT//
THEN EXPORT FIELD: DATE REPORTED//
THEN EXPORT FIELD: DATE INTERVIEWED//
THEN EXPORT FIELD: DATE COUNTED//
THEN EXPORT FIELD: DATE ON REG//
THEN EXPORT FIELD: DATE OFF REG//
THEN EXPORT FIELD: DATE EPI COMPLETED//
THEN EXPORT FIELD: STATUS AT DIAGNOSIS OF TB//
THEN EXPORT FIELD: PRIOR DX//
THEN EXPORT FIELD: IF PRIOR DX YES//
THEN EXPORT FIELD: DATE OF PRIOR DX//
THEN EXPORT FIELD: MORE THAN ONE PRIOR DX?//
THEN EXPORT FIELD: INITIAL X-RAY//
THEN EXPORT FIELD: IF INITIAL ABN,THEN//
THEN EXPORT FIELD: DATE OF INITIAL X-RAY//
THEN EXPORT FIELD: REPEAT X-RAYS//
THEN EXPORT FIELD: DATE THERAPY STARTED//
THEN EXPORT FIELD: DATE THERAPY STOPPED//
THEN EXPORT FIELD: REASON THERAPY STOPPED//
THEN EXPORT FIELD: RVCT SPUTUM SMEAR//
THEN EXPORT FIELD: RVCT SPUTUM CULTURE//
THEN EXPORT FIELD: MAJOR SITE OF DISEASE//
THEN EXPORT FIELD: OLD ADDL SITE (SUPERSEDED)//
THEN EXPORT FIELD: IF MAJOR SITE/DIS IS OTHER*//
THEN EXPORT FIELD: OLD OTHER SITE (SUPERSEDED)//
THEN EXPORT FIELD: MICRO EXAM OF TIS/BODY FLUIDS//
THEN EXPORT FIELD: CULT OF TIS/BODY FLUIDS//
THEN EXPORT FIELD: INITIAL SKIN TEST//
THEN EXPORT FIELD: INITIAL MM READING//
THEN EXPORT FIELD: IS PATIENT ANERGIC?//
THEN EXPORT FIELD: REPEAT SKIN TEST//
THEN EXPORT FIELD: CASE COUNTING CODE//
THEN EXPORT FIELD: DATE RVCT REPORT SUBMITTED//
THEN EXPORT FIELD: STATE CASE NUMBER//
THEN EXPORT FIELD: WHO SUBMITTED RVCT//


Originally posted by Seth Tiven
> Kinda sounds like the length of some of the data lines might be
> exceeding
> the maximum length (or the length chosen or defaulted to when
> the output
> device is opened). This can cause spurious CR/LF's (or missing
> ones). Are
> you writing a discreet CR/LF before each record? What version
> of MUMPS?
> How are you opening the device? Have you examined any of the problem
> records (with a simple text editor capable of opening large files)?
> -Seth Tiven
>
> "raphaelc" wrote in message
> news:3151535.1059161033@dbforums.com"]news:3151535.1059161033@d-
> bforums.com[/url]...
> > I'm consulting at a healthcare clinic that is in the process of
> changing
> > from MUMPS to MS Access. I am charged with building a new
> access DB for
> > them, and then importing all the old MUMPS data from CSV
> files.
> > Each table in MUMPS is downloaded to it's own CSV file, except
> for one
> > table which is so large that the columns are broken up across 3
> files.
> > It works like this: TABL1.CSV has columns 1-60, TABL2.CSV
> 61-140,
> > TABL3.CSV 141-180. Each row represents a record, and the first
> field of
> > each row is the PK, so that the data from the three different
> files can
> > be re-joined.
> > Here is the problem: about 1000-2000 records (each file has
> between 50
> > and 70k) do not begin on a newline, and instead just look like
> a
> > continuation of the previous row. When doing a CSV import into
> Access,
> > these records get lost.
> > Any idea why this is happening? The in-house MUMPS guy is
> stumped. The
> > problem only occurs in the CSV files that make up this one large
> table,
> > which makes me suspect some kind of problem in handling the
> amount of
> > data that's getting dumped. We've re-run the MUMPS queries and
> the
> > problem has not gone away and the same records are
> affected.
> > --
> Posted via
http://dbforums.com/http://dbforums.com


--
Posted via http://dbforums.com